Cell reference to output cell macro

[petegrant89]

I have data in Columns A-BI in groups of rows ranging in number (each group will have a different amount of rows every time the report is run). The groups of data are separated by a row of blank cells. I have been trying to design a macro where I can place my cursor underneath the group of data in column BI, and when I run the macro a designated output cell is populated with the cell reference (coordinates) of the top non-blank cell in column BI before the blank row.

Note: the problem I have been having is that I need this macro to function with all groups of data going down the page, which vary in terms of how many rows they consist of. I need this macro to populate the output cell with the cell reference of the top non-blank cell before the blank row in the group of data that my ActiveCell is currently under, so I can run the same macro for each group of data on the page.

Any suggestions?

 

[Andrew Poulsom]

This compiles OK:

.FormulaR1C1 = "=IF(R[-1]C[1]"">""0,COUNTIF(R" & x & "C[-2]:R[1]C[-2],""<=""&R[-2]C[1]),IF(R[-1]C[1]""=""0,COUNTIF(R" & x & "C[-2]:R[1]C[-2],""<=""&R[-2]C[1])+1))"

I had to add some more double quotes.

 

[petegrant89]

When I run that one it returned a Run-time error 1004: Application-defined or object-defined error. I looked it up and it involves "LegendEntries" - I do not quite understand what that error entails.

 

[Andrew Poulsom]

Try:

.FormulaR1C1 = "=IF(R[-1]C[1]>0,COUNTIF(R" & x & "C[-2]:R[1]C[-2],""<=""&R[-2]C[1]),IF(R[-1]C[1],""=0"",COUNTIF(R" & x & "C[-2]:R[1]C[-2],""<=""&R[-2]C[1])+1))"

 

[petegrant89]

Yes... perfect. Thank you again for all your help - you've saved me hours of monotony per week. Additionally, do you have any suggestions for books for me to pick up to learn VBA better for excel? I've been learning a lot by just playing with code and changing it to fit my needs but as you can see my coding is sloppy and verbose; I'd like to learn to write more efficient codes.

 

[Andrew Poulsom]

I like John Walkenbach's books:

The Spreadsheet Page - Excel Books by John Walkenbach