Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Thanks!

share|improve this question

1 Answer 1

I would approach this somewhat differently. The code fragment below uses the .Find property of ranges to determine the last cell of data in the worksheet. .Find returns a cell reference, and we can get its row by using the .Row property.

Note also that it isn't necessary to select ranges to act on them.

Dim lastcell As Range
Set lastcell = Cells.Find(What:="*", After:=[A1], _
    SearchDirection:=xlPrevious)
Range("A2:A" & lastcell.Row).FillDown
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.