VBA Code with VLOOKUP Loop

[ten1jmj]

Thank you in advance for any assistance you can provide. I have a workbook containing worksheets named "Dashboard" and "January". I have the following code to lookup descriptions on the "January" sheet and return the subsequent value. My cumulative list on the Dashboard has all possible charges, however not each of the charges will be incurred each month, so when the code runs, it pulls the correct data for any charge listed on the January sheet, however for any line item not on the January sheet, the value returned comes from the row above. For example, January only has four charges for the month, however the Dashboard returns a value for all items in column 'D'. I would like for all column 'D' to have a blank on any line item without a charge for that current month.

Thank you,



Sub FindCount()

Dim Description As String
Dim Count As String
Dim i As Integer

Worksheets("DASHBOARD").Range("A3").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To LastRow
Description = ActiveCell
On Error Resume Next
Count = Application.WorksheetFunction.VLookup _
(Description, Worksheets("January").Range("A20:B109"), 2, False)
On Error Resume Next

If IsError(Count) Then
ActiveCell.Offset(0, 3).Value = ""
Else
ActiveCell.Offset(0, 3).Value = Count

End If

ActiveCell.Offset(1, 0).Select

Next i

End Sub

 

[RobP]

Hi,

just add count = "" before your next i loop.

Sub FindCount()

Dim Description As String
Dim Count As String
Dim i As Integer

Worksheets("DASHBOARD").Range("A3").Select
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To LastRow
Description = ActiveCell
On Error Resume Next
Count = Application.WorksheetFunction.VLookup _
(Description, Worksheets("January").Range("A20:B109"), 2, False)
On Error Resume Next

If IsError(Count) Then
ActiveCell.Offset(0, 3).Value = ""
Else
ActiveCell.Offset(0, 3).Value = Count

End If

ActiveCell.Offset(1, 0).Select

Count = ""

Next i

End Sub

 

[ten1jmj]

Thank you so much RobP. It works perfectly now.

​

 

[RobP]

Great, glad we could help - thanks for the feedback

Rob