Excel VBA Using Variables in Cells and Range Function

Question

I am tring to find the word "Example" in whole used cells. But the code gives me Runtime Error 1004 “Application-defined or Object-defined error”. I know there is something wrong with the line Range(Cells(1, 1), Cells(lastrow, lastcolumn)) What could it be?

Thanks in advance. Here is the Code:

Dim lastrow as Long
Dim lastcolumn as Long
Dim sclnr as Range
dim aws as WorkSheet

Set aws = Thisworkbook.Sheets("Sheet1")

'Using UsedRange
lastrow = aws.UsedRange.Rows(aws.UsedRange.Rows.Count).Row
lastcolumn = aws.UsedRange.Columns(aws.UsedRange.Columns.Count).Column
'UsedRange works fine no problem with finding last row and column

Set sclnr = aws.Range(Cells(1, 1), Cells(lastrow, lastcolumn)).Find("Example") 
'the word Example exits in one of the cells

Welcome to SO, please take a minute to take the [tour] (click it) to see how SO community works : accepting answers and upvotes. — R3uK, Mar 08 '17 at 14:24
See [Is the . in .Range necessary when defined by .Cells?](http://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) — , Mar 08 '17 at 14:35

score 3 · Accepted Answer · answered Mar 08 '17 at 14:18

3

Try this. You need to qualify all the range/cells references in case aws is not the active sheet when the macro is run.

Dim lastrow as Long
Dim lastcolumn as Long
Dim sclnr as Range
dim aws as WorkSheet

Set aws = Thisworkbook.Sheets("Sheet1")

'Using UsedRange
lastrow = aws.UsedRange.Rows(aws.UsedRange.Rows.Count).Row
lastcolumn = aws.UsedRange.Columns(aws.UsedRange.Columns.Count).Column
'UsedRange works fine no problem with finding last row and column

Set sclnr = aws.Range(aws.Cells(1, 1), aws.Cells(lastrow, lastcolumn)).Find("Example")

answered Mar 08 '17 at 14:18

SJR

22,986
6
18
26

It works fine, I cannot thank you enough. It bugged me all day. – MertTheGreat Mar 08 '17 at 14:21
1

My pleasure. It's a very common problem. – SJR Mar 08 '17 at 14:23

score 0 · Answer 2 · answered Mar 08 '17 at 14:22

Sub test_MertTheGreat()
Dim FirstAddress As String, LookForString As String
LookForString = "Example"

Dim LastRow As Long
Dim LastColumn As Long
Dim SclnR As Range
Dim awS As Worksheet

Set awS = ThisWorkbook.Sheets("Sheet1")

'Using UsedRange
With awS.UsedRange
LastRow = .Rows(.Rows.Count).Row
LastColumn = .Columns(.Columns.Count).Column
End With 'awS.UsedRange

Set SclnR = awS.Range(Cells(1, 1), Cells(LastRow, LastColumn)).Find("Example")
'the word Example exits in one of the cells

With awS.Range(awS.Cells(1, 1), awS.Cells(LastRow, LastColumn))
    .Cells(1, 1).Activate
    'First, define properly the Find method
    Set SclnR = .Find(What:=LookForString, _
                After:=ActiveCell, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)

    'If there is a result, keep looking with FindNext method
    If Not SclnR Is Nothing Then
        FirstAddress = SclnR.Address
        Do
            '''---+++--- Your actions here ---+++---
            Set SclnR = .FindNext(SclnR)
        'Look until you find again the first result
        Loop While Not SclnR Is Nothing And SclnR.Address <> FirstAddress
    End If
End With

End Sub

Excel VBA Using Variables in Cells and Range Function

2 Answers2