I need to append this macro to find the next empty column to place the data in. The orignal VBA works fine, but I need to go into the editor, and repalce the offset number every time I add a new row (weekly).
Here's the orignal coding:
Sub Lookup()
Dim oDat As Range, Rng As Range, Ocl As Range, Cl As Range
Dim Found As Boolean
Set oDat = Sheets("Current Data").Range(Sheets("Current Data").Range("A2"), Sheets("Current Data").Range("A" & Rows.Count).End(xlUp))
Set Rng = Range(Range("C4"), Range("C" & Rows.Count).End(xlUp))
For Each Cl In Rng
Found = False
For Each Ocl In oDat
If Cl.Value = Ocl.Value Then
Cl.Offset(, 9).Value = Ocl.Offset(, 1).Value
Found = True
End If
Next Ocl
If Found = False Then
Cl.Offset(, 9).Value = 0
End If
Next Cl
End Sub
I tried this, but it's not working as i would have hoped.
Sub Lookup()
Dim oDat As Range, Rng As Range, Ocl As Range, Cl As Range
Dim Found As Boolean
Set oDat = Sheets("Current Data").Range(Sheets("Current Data").Range("A2"), Sheets("Current Data").Range("A" & Rows.Count).End(xlUp))
Set Rng = Range(Range("C4"), Range("C" & Rows.Count).End(xlUp))
For Each Cl In Rng
Found = False
For Each Ocl In oDat
If Cl.Value = Ocl.Value Then
Sheet1.Range("IV4").End(xlToLeft)(1, 2) = Sheet1.Range("IV4").End(xlToLeft)(1, 2).Value
Found = True
End If
Next Ocl
If Found = False Then
Sheet1.Range("IV4").End(xlToLeft)(2, 1).Value = 0
End If
Next Cl
End Sub
Any grand ideas you gurus?
`engineers08
Bookmarks