VBA: match cell value with cell on another sheet to copy data

[azm]

Hello,

I have in Sheet2 a range of 10 cells (F4:F13) in which i have drop down lists, the values of these drop down lists are the headers for columns on Sheet1 (E2:CC2).
I am trying to create a macro that loops through the range in Sheet2 and finds a match on the range of headers in Sheet1, when it finds the match the values of the column with the matched header in Sheet 1 are copied in column F of Sheet2.

I tried to use the worksheetfunction.match method, but then couldn't find a way to copy the right data:

Sub Copy_1()

Dim ShSource As Worksheet
Dim ShDest As Worksheet
Dim s As Range 
Dim t As Range 
Dim v As Range 
Dim cell As Range

Set ShSource = Worksheets("Sheet1")
Set ShDest = Worksheets("Sheet2")
Set s = ShSource.Range("E2:CC2")
Set t = ShDest.Range("F4:F13")
Set v = ShDest.Range("F16:F101")
    
    For Each cell In t
    If WorksheetFunction.Match(cell, s, 0) Then v.Value = "data from Sheet1"
'   example If cell.Value = "Header 1" Then v.Value = ShSource.Range("E3:E87").Value
    Next

End Sub

I also tried to use the find method but nothing was copied

Dim WB As Range
Set WB = ShSource.Range("E2:AL2").Find(what:=ShDest.Range("F4").Value, LookIn:=xlValues, lookat:=xlWhole)
v.Value = WB.Offset(1, 0).Value

I hope someone can help and thanks!

 

[braindiesel]

This may be a cause of your MATCH function not working...
If WorksheetFunction.Match(cell, s, 0)>0 Then v.Value = "data from Sheet1"

 

[azm]

This may be a cause of your MATCH function not working...
If WorksheetFunction.Match(cell, s, 0)>0 Then v.Value = "data from Sheet1"

Thank you! In the end I didn't use match but conditional IF which selects the right ranges, but instead of selecting all the ranges at once it goes from one range to the next, do you know how I could have them all selected at once?

Dim ShSource, ShDest As Worksheet
Dim s, t, v, cell As Range
Set ShSource = Worksheets("Sheet1")
Set ShDest = Worksheets("Sheet2")
Set s = ShSource.Range("E2:CC2") 'source scenarios
Set t = ShDest.Range("F4:F13") 'target scenarios
Set v = ShDest.Range("F16:F101") 'volume of items
For Each cell In s
If cell = ShDest.Range("F4") Then cell.Rows("2:87").Select
If cell = ShDest.Range("F5") Then cell.Rows("2:87").Select
If cell = ShDest.Range("F6") Then cell.Rows("2:87").Select
If cell = ShDest.Range("F7") Then cell.Rows("2:87").Select
If cell = ShDest.Range("D8") Then cell.Rows("2:87").Select
If cell = ShDest.Range("D9") Then cell.Rows("2:87").Select
If cell = ShDest.Range("F10").Value = "YES" Then cell.Rows("2:87").Select
'elseif cell = ShDest.Range("F10").value = "NO" Then ""
If cell = ShDest.Range("F11") Then cell.Rows("2:87").Select
'elseif cell = ShDest.Range("F11").value = "NO" Then ""
If cell = ShDest.Range("F12") Then cell.Rows("2:87").Select
'elseif cell = ShDest.Range("F12").value = "NO" Then ""
If cell = ShDest.Range("F13") Then cell.Rows("2:87").Select
'elseif cell = ShDest.Range("F13").value = "NO" Then ""
Next cell
End Sub