Loop through a column, find a value, return offset value to cell

[harts]

I have been trying to find a solution to my problem. I find bits a pieces that kind of work but not a whole solution. I need to loop the names listed D2:AZ2, find a match in BH1:BH40, then return the value in BF1:BF40 to D3:AZ3. Thank you in advanced. It is much appreciated.

 

[harts]

this will find a match with just the first word, it looks for a space so if there isn't a space it doesn't find a match, but you could combine the two.

Sub test()
Darray = Range("D2:az3")  ' load range D2 to Az3 into a variant array
bfarray = Range("BF1:Bh40") ' load ranger  BF1 to Bh40 into a varaint array
 For i = 1 To UBound(Darray, 2) ' loop through each column D to Az
  Textb = Split(Darray(1, i), " ")
    If IsArray(Textb) Then
    For j = 1 To UBound(bfarray, 1) ' 2nd loop through each row 1 to 40
     Textg = Split(bfarray(j, 3), " ")
  
     If IsArray(Textg) Then
      If Textb(0) = Textg(0) Then ' compare the two first words
       Darray(2, i) = bfarray(j, 1)        ' when match found copy to row 3
       Exit For
      End If
     End If
    Next j
  ' you could add "else" here and then post the first code in here
   End If
 Next i
Range("D2:az3") = Darray  ' write the array back to worksheet
 
End Sub

I get a run time error 9 subscript out of range on this line- If Textb(0) = Textg(0) Then ' compare the two first words

 

[harts]

This works perfect thank you for your time and teaching

Thanks for updating your details.

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time in post 5.

Give this one a try

Sub test3()
  With Range("D4:AZ4")
    .Formula = "=LET(bp,$BP1:$BP40,bs,$BS1:$BS40,XLOOKUP(D1,bp,bs,XLOOKUP(TEXTBEFORE(D1,"" "",,,1)&""*"",bp,bs,"""",2)))"
    .Value = .Value
  End With
End Sub

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[harts]

Thanks for updating your details.

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time in post 5.

Give this one a try

Sub test3()
  With Range("D4:AZ4")
    .Formula = "=LET(bp,$BP1:$BP40,bs,$BS1:$BS40,XLOOKUP(D1,bp,bs,XLOOKUP(TEXTBEFORE(D1,"" "",,,1)&""*"",bp,bs,"""",2)))"
    .Value = .Value
  End With
End Sub

I greatly apricate your time, I have 1 more work around with this vba. Is it possible to do a if then maybe that if BN contains the word "Rig Cost" it will move on to the next reference with that same name?

 

[harts]

I greatly apricate your time, I have 1 more work around with this vba. Is it possible to do a if then maybe that if BN contains the word "Rig Cost" it will move on to the next reference with that same name?

To clarify basically if col BP1:BP40= d1:AZ1 & BN:BN = "rig cost" then get that column BS value, if it doesn't get the next value. I do apologize for not being organized, wasn't until I ran that I realized it wasn't 100% right.

 

[Peter_SSs]

To clarify

I think that you may have done just the opposite?
Don't the following two things contradict each other?

if BN contains the word "Rig Cost" it will move on

if ... & BN:BN = "rig cost" then get that column BS value

What about some sample data and the expected results with XL2BB and explain again in relation to the sample data?

Make up some dummy data that shows the variety of things that can happen with names matching fully/partially/not at all and "rig cost" v not "rig cost" in say columns D:P, hide columns Q:AZ and post what is visible with XL2BB