VBA Runtime Error 1004 Unable to Get Match property of the WorksheetFunction class

[D_Theroux]

Hello,

I am looking to populate a calendar grid with select piece of information. My Calendar Grid sheet is named "Calendar". The sheet with the data is named "Input".

The macro successfully completes one cycle but I get an error in the 2nd cycle once I arrive at the Cindex line of code. All values before that change correctly in the locals window.

Any guidance is greatly appreciated. I am relatively new to VBA so the code may not be optimal. Thank you

Sub PopulateCalendar()

Dim CalendarMonth As String
Dim Brand As String
Dim Price As String
Dim PromotedFamily As String
Dim PromoTactics As String
Dim Calendarcell As String
Dim Cindex As Integer
Dim RIndex As Integer
Dim i As Integer

i = 0
Worksheets("Input").Range("B3").Select

Do Until ActiveCell.Value = ""
Worksheets("Input").Activate
Brand = ActiveCell.Value
CalendarMonth = ActiveCell.Offset(0, 5).Value
Price = ActiveCell.Offset(0, 3).Value
PromotedFamily = ActiveCell.Offset(0, 1).Value
PromoTactics = ActiveCell.Offset(0, 2).Value
Calendarcell = PromotedFamily & " " & Price & " " & PromoTactics
Worksheets("Calendar").Activate
Range("B2").Select
Cindex = WorksheetFunction.Match(CalendarMonth, Range("B2:M2"))
RIndex = WorksheetFunction.Match(Brand, Range("A3:A20"))

If ActiveCell.Value = "" Then
ActiveCell.Value = Calendarcell
Else
ActiveCell.Value = ActiveCell & " " & Calendarcell
End If
Worksheets("Input").Activate
Range("B3").Select
i = i + 1
ActiveCell.Offset(i, 0).Select
Loop

End Sub

 

[Domenic]

Does it help to search for an exact match?

Cindex = WorksheetFunction.Match(CalendarMonth, Range("B2:M2")[COLOR=#ff0000], 0[/COLOR])

Maybe the same thing for RIndex?

RIndex = WorksheetFunction.Match(Brand, Range("A3:A20")[COLOR=#ff0000], 0[/COLOR])

Also, depending on your situation, you may want to test for a match before assigning the values to Cindex and RIndex.