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
– R3uK, Commented Mar 8, 2017 at 14:24

SJR · Accepted Answer · 2017-03-08 14:18:28Z

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 8, 2017 at 14:18

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MertTheGreat Over a year ago

It works fine, I cannot thank you enough. It bugged me all day.

SJR Over a year ago

My pleasure. It's a very common problem.

R3uK · Accepted Answer · 2017-03-08 14:22:55Z

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

Collectives™ on Stack Overflow

Excel VBA Using Variables in Cells and Range Function

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related