I am writing a macro function in Excel to organize a set of monthly sales data. The original format uses 6 rows per entry, and the organized table uses only 1. The number of sales changes every month, so I need to figure out a way to identify how many sales entries there are so that I know how far to extend my formulas.
At the end of the raw data always appears the word "SIGNED:" in the second column, so I used the indirect and match functions to determine how far to filldown the formulas. The code that I used looks like this:
Application.Goto Reference:= _ "INDIRECT(""T"" & (MATCH(""SIGNED:"",RawData!C2:C2,0)-3)/6+1)" Range("T2", Selection).Select Selection.FillDown
And it worked great initially, except that apparently sometimes the raw data doesn't use 6 rows because sometimes the match function with the above inputs does not return a whole number. So I tried using a ceiling function around the match function to insure that I get a whole number, and figured it would be okay if it is off by one or two as that is easily dealt with manually. What I wrote looks like this:
Application.Goto Reference:= _ "INDIRECT(""T"" & Ceiling((MATCH(""SIGNED:"",RawData!C[-20],0)-3)/6+1),1))" Range("A2", Selection).Select Selection.FillDown
Now I get an error and I can't figure out why. Does it have to do with the format of the number returned by the ceiling function? I would greatly appreciate any help in coming to a solution, or perhaps even a different approach to finding the number of sales entries.