There are 2 sheets in my excel file (Sheet 1 and 2).
In Sheet 1, there are 3 columns - Code, ID and text. There are more than 100 records here.
In Sheet 2, there is heaps of data.
I am trying to write VBA that:
1) finds 'code' (Sheet1) from the second column of Sheet2;
2) finds 'ID' (Sheet1) from the third row of Sheet2 and get the column number;
3) Paste 'text' (Sheet1) in the respective row and column in Sheet2.
I have written down the below code but it is not pasting 'text' in the respective column - rather pastes in all the columns matching the 'IDs'
Please assist. Thanks
Private Sub CommandButton1_Click()
Dim wb As Workbook
Dim sht As Worksheet
Dim rng1 As Range
Dim rngCell_1 As Range
Dim rngCell_2 As Range
Dim rngCell_3 As Range
Set wb = ActiveWorkbook
Set sht2 = ActiveWorkbook.Sheets("Sheet2")
Set sht = wb.Sheets("Sheet1")
With sht2
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
lastrowcell = sht.Cells(Rows.Count, "A").End(xlUp).Row
For Row = 4 To lastrow
For Each rngCell_2 In sht.Range("B2:B" & lastrowcell)
Set rng1 = sht2.UsedRange.Find(rngCell_2, , xlValues, xlWhole)
For Each rngCell_1 In sht.Range("A2:A" & lastrowcell)
For Each rngCell_3 In sht.Range("C2:C" & lastrowcell)
If (.Cells(Row, 2) = rngCell_1) Then
.Cells(Row, rng1.Column) = rngCell_3
.Cells(Row, rng1.Column).Font.Color = 255
End If
Next rngCell_3
Next rngCell_1
Next rngCell_2
Next Row
End With
End Sub