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

azm

New Member
Joined
Jul 7, 2020
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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:

VBA Code:
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
VBA Code:
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
554
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 7, 2020
Messages
4
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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?

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,241
Members
410,824
Latest member
Bobmn4
Top