Find

K

kaon

Hi all,

I got the error message : "Unable to find property of Range class" fro
Excel VBA and I have ben looking for the bug(s) a long time, so anyon
would kindly give me a hand?

Problem tackled:
1) matchRow = Worksheets("Raw Data").UsedRange.Find(What:=poNo
LookIn:=xlFormula, LookAt:=xlWhole).Row

p.s I want to search the corresponding row with the wanted number i
another sheet.

Public Sub Mark2004Po()
Dim i As Integer
Dim poNo As String
Dim matchRow As Range

For i = 2 T
Worksheets("Settings").UsedRange.Columns(1).Rows.Count
poNo = Worksheets("Settings").Cells(i, 1).Value
If Not IsEmpty(poNo) Then
Do
matchRow = Worksheets("Ra
Data").UsedRange.Find(What:=poNo, LookIn:=xlFormula
LookAt:=xlWhole).Row
If Not (matchRow Is Nothing) Then
matchRow.Interior.ColorIndex = 2
End If
Loop Until matchRow Is Nothing
End If
Next i
End Sub

Thanks in advance
 
V

Vasant Nanavati

Perhaps because you are declaring the variable matchRow as a Range object
rather than an Integer or a Long, and then assigining a row number to it.

If you want it to be a range, try replacing .Row with .EntireRow in your
assignment statement. You will also need to use the Set keyword to assign a
range object to the variable:

Set matchRow = Worksheets("Raw Data").UsedRange _
..Find(What:=poNo, LookIn:=xlFormula, _
LookAt:=xlWhole).EntireRow
 
T

Tom Ogilvy

Public Sub Mark2004Po()
Dim i As Integer
Dim poNo As String
Dim matchRow As Range
Dim fAddr As String

For i = 2 To _
Worksheets("Settings").UsedRange _
.Columns(1).Rows.Count
poNo = Worksheets("Settings").Cells(i, 1).Value
If Not IsEmpty(poNo) Then
With Worksheets("Raw Data"). _
UsedRange
Set rng = .Find(What:=poNo, _
LookIn:=xlFormula, _
LookAt:=xlWhole)
If Not rng Is Nothing Then
fAddr = rng.Address
Do
rngEntireRow.Interior.ColorIndex = 2
Set rng = .FindNext(rng)
Loop While rng.Address <> fAddr
End If
End With
End If
Next i
End Sub
 
K

kaon

Public Sub Mark2004Po_2()
Dim i As Integer
Dim poNo As String
Dim fAddr As String
Dim rng As Range

For i = 2 T
Worksheets("Settings").UsedRange.Columns(1).Rows.Count
poNo = Worksheets("Settings").Cells(i, 1).Value
If Not IsEmpty(poNo) Then
With Worksheets("Raw Data").UsedRange
***Set rng = .Find(What:=poNo, LookIn:=xlFormula
LookAt:=xlWhole) ***
If Not rng Is Nothing Then
fAddr = rng.Address
Do
rng.EntireRow.Interior.ColorIndex = 2
Set rng = .FindNext(rng)
Loop While rng.Address <> fAddr
End If
End With
End If
Next i
End Sub


Thanks both of you but I have tackled another problem on the lin
marked with *** and message shows on the screen: "Unable to get th
Find property of the Range Class.
 
Top