VBA Macro - Found.Row keep the same value

gio123bg

Active Member
Joined
Feb 14, 2004
Messages
255
Hi All,
In a sheet I find a value in column B. I use this VBA code

Set Found = Sheet3.Cells.Find(What:=VALUE1, After:=Sheet3.Range("B1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

I use the value in a different column (for example J) in the row found as

targe1tRow = Found.Row

I move this new value in variable VALUE1 and I try to find this value in column B in the same way.

Set Found = Sheet3.Cells.Find(What:=VALUE2, After:=Sheet3.Range("B1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

If I check the new value targe2tRow = Found.Row I find the same value of targe1tRow

I tried to reset the value in this way

Set Found = Nothing

but without success.

Any help will be well appreciated.

Thanks in advance for your kind support.

Regards,

Giovanni
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Let's see if I understand...
You have a list of items in Column B...
You want to start a search for 1 item, get a referenced item from Col J, then search for the item From column J and find it's reference...until there are no more items to find, is that correct?
[Sounds like parsing a project plan]
If that's correct: how are you 'starting' this search process?
What do you do with the items that are found?
 
Upvote 0
Hi Tweedle,
first of all thanks for your reply and support.
This is an example.

Row number____Column "B"_______________________Column "J"

1_____________11111____________________________99999
2_____________22222____________________________99999
3_____________33333____________________________99999
4_____________44444____________________________22222
5_____________55555____________________________99999
6_____________66666____________________________99999

VBA Code

VALUE1= 44444

Set Found = Sheet3.Cells.Find(What:=VALUE1, After:=Sheet3.Range("B1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

targe1tRow = Found.Row (targe1tRow = 4)

VALUE2=Sheet1.Cells(targe1tRow, 10).Value (where column n. 10 = column J)

Then .... I try to find the row number of the secon value (VALUE2). I hope to find 2 as row number but ...

Set Found = Sheet3.Cells.Find(What:=VALUE2, After:=Sheet3.Range("B1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

targe2tRow = Found.Row (targe2tRow = 4 - the previous value) In general the VALUE2 in column B comes before VALUE1.

Hope this helps.

Thanks in advance for your kind support.

Regards,

Giovanni
 
Upvote 0
Give this a spin; mostly adjusted the parameters of the .find to make it work.
>also uses ws to specify the worksheet; one of your original statements pointed to sheet1 [?]
>also changed variable names Value2 is an Excel-defined name; may want to stay away from using that.

Code:
Sub Tester()
Dim ws As Worksheet
Dim Found As Range
Set ws = Sheet3
ws.Activate
vValue1 = 44444
Set Found = ws.Cells.Find(What:=vValue1, _
        After:=ws.Range("B1"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext)
targe1tRow = Found.Row '(targe1tRow = 4)
 
'Optionally, can consolidate the two statements above to one statement
targe1tRow = ws.Cells.Find(What:=vValue1, _
        After:=ws.Range("B1"), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext).Row
 
'was Sheet1.Cells [?]
vValue2 = ws.Cells(targe1tRow, 10).Value '(where column n. 10 = column J)
Set Found = ws.Cells.Find(What:=vValue2, _
        After:=Sheet3.Range("B1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext)
targe2tRow = Found.Row '(targe2tRow = 4 - the previous value)
'-----------------------(Now reports Row 2)
 
'Optionally, can consolidate the two statements above to one statement
targe2tRow = ws.Cells.Find(What:=vValue2, _
        After:=Sheet3.Range("B1"), _
        LookIn:=xlValues, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext).Row
Debug.Print targe1tRow, targe2tRow
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top