hi
I desighned this simple macro but doesn't work well . it should bring the data based on cell G5
I know to can do that by formula , but my real data about 2200 rows and then it takes from me more time to pull down . also the macro is better than the formula especially when every time increase data . I don't have to pull down the formula . I put the expected result when search based on cell G5 .
Sub getData()
Dim cell As Range
Dim sh As Worksheet
Dim lRow As Integer
Set sh = ActiveSheet
For Each cell In sh.Range("B4:B" & sh.Cells(Rows.Count, "B").End(xlUp).Row)
lRow = sh.Cells(Rows.Count, "F").End(xlUp).Row + 1
If cell.Value = sh.Cells(5, 7).Value Then
sh.Cells(lRow, "F").Value = cell.Offset(0, -1).Value
sh.Cells(lRow, "G").Value = cell.Offset(0, 1).Value
sh.Cells(lRow, "H").Value = cell.Offset(0, 2).Value
End If
Next cell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [G5]) Is Nothing Then
Call getData
End If
End Sub
any help. it would truly appreciate