The following code looks at a data table ("Emp Data Behav Comp"), matches column ("V3:V7") and row ("U3:U7") and then pastes values from Activesheet range ("W3:W7"). The problem that I can't figure out is that it finds the columns but only pastes the first value from ("W3") into each cell. I need it to post each of the corresponding values("W3:W7") into the appropriate cells.
See attached example.
Test1.xlsx
Any help would be appreciated.
Sub CopytoEmpDataBehavComp()
Dim ws1 As Worksheet: Set ws1 = ActiveSheet
Dim ws2 As Worksheet: Set ws2 = Sheets("Emp Data Behav Comp")
Dim rCell As Range, rDateFind As Range, RValueFind
Dim rval As Range
Dim strFound As String, strNotFound As String
Dim LC1 As Long, LC2 As Long
Set rvall = ws1.Range("V3:V7")
Set rCell = ws1.Range("U3")
strFound = vbNullString
strNotFound = vbNullString
LC2 = ws2.Cells(1, Columns.Count).End(xlToLeft).Column
For Each rvall In ws1.Range("V3:V7")
Set RValueFind = ws2.Range("A2:A33").Find(What:=rCell, LookIn:=xlValues, LookAt:=xlWhole)
If Not RValueFind Is Nothing Then
strFound = strFound & ", " & rCell
Set rDateFind = ws2.Range(ws2.Cells(2, 1), ws2.Cells(1, LC2)).Find(What:=(rvall), LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rDateFind Is Nothing Then
ws2.Cells(RValueFind.Row, rDateFind.Column).Value = ws1.Cells(rCell.Row, "W").Value
End If
Else
strNotFound = strNotFound & ", " & rCell
End If
Next rvall
MsgBox ("RACF match: " & strFound & vbCr & "RACF not found: ")
End Sub
Bookmarks