A
Ayo
I am getting this compile error when I try to run the following codes, any
ideas what I did wrong?
Thanks
Private Sub cmdUpdate_Click()
Dim rngData As Range, c As Range, rngBaseline As Range
Dim WSlastRow As Long, bllastRow As Long
Dim WSbline As Worksheet, WSinsite As Worksheet
Set WSinsite = Worksheets("InSite Data")
WSlastRow = ActiveSheet.Range("B65536").End(xlUp).Row
Set rngData = WSinsite.Range("B2:AJ" & WSlastRow)
Set WSbline = Worksheets("Baseline")
bllastRow = WSbline.Range("C65536").End(xlUp).Row
Set rngBaseline = WSbline.Range("C5:C" & bllastRow)
On Error Resume Next
With WSbline
For Each c In rngBaseline.Cells
checkForUpdates c.Value, 1, 2, 16, 17, rngData
checkForUpdates c.Value, 3, 4, 30, 31, rngData
checkForUpdates c.Value, 5, 6, 18, 19, rngData
checkForUpdates c.Value, 7, 8, 20, 21, rngData
checkForUpdates c.Value, 9, 10, 12, 13, rngData
checkForUpdates c.Value, 11, 12, 26, 27, rngData
checkForUpdates c.Value, 13, 14, 10, 11, rngData
checkForUpdates c.Value, 15, 16, 14, 15, rngData
checkForUpdates c.Value, 17, 18, 8, 9, rngData
checkForUpdates c.Value, 19, 20, 6, 7, rngData
checkForUpdates c.Value, 21, 22, 22, 23, rngData
checkForUpdates c.Value, 23, 24, 33, 34, rngData
checkForUpdates c.Value, 25, 26, 34, 35, rngData
checkForUpdates c.Value, 27, 28, 24, 25, rngData
checkForUpdates c, 29, 30, 28, 29, rngData
checkForUpdates c, 31, 32, 4, 5, rngData
Next c
End With
End Sub
Sub checkForUpdates(cl As Range, clOffset1 As interger, clOffset2 As
interger, _
VlkupcolIndex1 As interger, VlkupcolIndex2 As interger,
Vlkuprng As Range)
If cl.Offset(0, clOffset1).Value <>
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex1,
False) Then
cl.Offset(0, clOffset1).Value =
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex1,
False)
cl.Offset(0, clOffset2).Value =
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex2,
False)
cl.Offset(0, clOffset1).Interior.Color = 255
End If
End Sub
ideas what I did wrong?
Thanks
Private Sub cmdUpdate_Click()
Dim rngData As Range, c As Range, rngBaseline As Range
Dim WSlastRow As Long, bllastRow As Long
Dim WSbline As Worksheet, WSinsite As Worksheet
Set WSinsite = Worksheets("InSite Data")
WSlastRow = ActiveSheet.Range("B65536").End(xlUp).Row
Set rngData = WSinsite.Range("B2:AJ" & WSlastRow)
Set WSbline = Worksheets("Baseline")
bllastRow = WSbline.Range("C65536").End(xlUp).Row
Set rngBaseline = WSbline.Range("C5:C" & bllastRow)
On Error Resume Next
With WSbline
For Each c In rngBaseline.Cells
checkForUpdates c.Value, 1, 2, 16, 17, rngData
checkForUpdates c.Value, 3, 4, 30, 31, rngData
checkForUpdates c.Value, 5, 6, 18, 19, rngData
checkForUpdates c.Value, 7, 8, 20, 21, rngData
checkForUpdates c.Value, 9, 10, 12, 13, rngData
checkForUpdates c.Value, 11, 12, 26, 27, rngData
checkForUpdates c.Value, 13, 14, 10, 11, rngData
checkForUpdates c.Value, 15, 16, 14, 15, rngData
checkForUpdates c.Value, 17, 18, 8, 9, rngData
checkForUpdates c.Value, 19, 20, 6, 7, rngData
checkForUpdates c.Value, 21, 22, 22, 23, rngData
checkForUpdates c.Value, 23, 24, 33, 34, rngData
checkForUpdates c.Value, 25, 26, 34, 35, rngData
checkForUpdates c.Value, 27, 28, 24, 25, rngData
checkForUpdates c, 29, 30, 28, 29, rngData
checkForUpdates c, 31, 32, 4, 5, rngData
Next c
End With
End Sub
Sub checkForUpdates(cl As Range, clOffset1 As interger, clOffset2 As
interger, _
VlkupcolIndex1 As interger, VlkupcolIndex2 As interger,
Vlkuprng As Range)
If cl.Offset(0, clOffset1).Value <>
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex1,
False) Then
cl.Offset(0, clOffset1).Value =
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex1,
False)
cl.Offset(0, clOffset2).Value =
Application.WorksheetFunction.VLookup(c1.Value, rngData, VlkupcolIndex2,
False)
cl.Offset(0, clOffset1).Interior.Color = 255
End If
End Sub