Find
 

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.C ount
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

--
Message posted from http://www.ExcelForum.com

Find
 

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

--

Vasant

"kaon " wrote in message
...
Hi all,

I got the error message : "Unable to find property of Range class" from
Excel VBA and I have ben looking for the bug(s) a long time, so anyone
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 in
another sheet.

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

For i = 2 To
Worksheets("Settings").UsedRange.Columns(1).Rows.C ount
poNo = Worksheets("Settings").Cells(i, 1).Value
If Not IsEmpty(poNo) Then
Do
matchRow = Worksheets("Raw
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.


---
Message posted from http://www.ExcelForum.com/

Find
 

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

--
Regards,
Tom Ogilvy


"kaon " wrote in message
...
Hi all,

I got the error message : "Unable to find property of Range class" from
Excel VBA and I have ben looking for the bug(s) a long time, so anyone
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 in
another sheet.

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

For i = 2 To
Worksheets("Settings").UsedRange.Columns(1).Rows.C ount
poNo = Worksheets("Settings").Cells(i, 1).Value
If Not IsEmpty(poNo) Then
Do
matchRow = Worksheets("Raw
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.


---
Message posted from http://www.ExcelForum.com/

Find
 

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.C ount
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.

--
Message posted from http://www.ExcelForum.com

Find
 

change

Lookin:=xlFormula

to

Lookin:=xlFormulas

After that, it
worked fine for me.

--
Regards,
Tom Ogilvy

"kaon " wrote in message
...
Public Sub Mark2004Po_2()
Dim i As Integer
Dim poNo As String
Dim fAddr As String
Dim rng As Range

For i = 2 To
Worksheets("Settings").UsedRange.Columns(1).Rows.C ount
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 line
marked with *** and message shows on the screen: "Unable to get the
Find property of the Range Class."


---
Message posted from http://www.ExcelForum.com/