Find the correct column and row, then place the data in that cell.
Sub UsingFind()
Dim ws As Worksheet, sh As Worksheet
Dim Rws As Long, Rng As Range, c As Range, lookRng As Range
Dim rw As Range, col As Range
Set sh = Worksheets("Sheet1")
Set ws = Worksheets("Sheet2")
With ws
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(.Cells(2, 1), .Cells(Rws, 100)).SpecialCells(xlCellTypeConstants)
End With
For Each c In Rng.Cells
Set col = sh.Rows(1).Find(what:=ws.Cells(1, c.Column), lookat:=xlWhole)
Set rw = sh.Columns(1).Find(what:=ws.Cells(c.Row, 1), lookat:=xlWhole)
If Not col Is Nothing And Not rw Is Nothing Then
sh.Cells(rw.Row, col.Column).Value = c
End If
Next c
End Sub
Bookmarks