VBA Macro - Found.Row keep the same value

[gio123bg]

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

 

[tweedle]

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?

 

[gio123bg]

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

 

[tweedle]

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.

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