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]

Like this?

MsgBox ActiveCell.Offset(-1).End(xlUp).Address

 

[MickG]

Ok !!!

 

[petegrant89]

Perfect - thank you very much. I was making it wayyyyyy more complicated than it needed to be. Thanks again.

 

[petegrant89]

Follow-up Question:

How can I implement the returned cell address into another macro-driven formula? Example: I would like the line of text to look like this: ActiveCell.FormulaR1C1 = "=COUNTIF("RETURNED CELL ADDRESS":activecell.offset(3, -3),"="&activecell.offset(-1))".... however I feel as though I just more or less bastardized the VBA coding language...

 

[Andrew Poulsom]

Can you post the formula as it would appear on a worksheet and the address of the cell that contains it?

 

[petegrant89]

I have included the link to a screenshot of one of the data groups that I am referring to. Because they go all the way down the sheet and each has a different amount of rows, but my data analytical data always has the same relation to the bottom cells, I used the code that you provided earlier to identify the top piece of data in the group (ActiveCell.Value = ActiveCell.Offset(-1).End(xlUp).Address).
I want to use the cell that the code above returns in subsequent lines of code in the same macro, but also reference other cells (based on "Activecell.offset" references) in the same line; I do not know how to do this (or if it is possible to.)
The line of code that I would like to include these references in is: "=COUNTIF("RETURNED CELL ADDRESS":activecell.offset(3, -3),"="&activecell.offset(-1))".

https://docs.google.com/file/d/0B_sHW5e7EgaQQU9DVHNvZTQ1b00/edit?usp=sharing

 

[Andrew Poulsom]

Is it?

Sub Test()
    Dim r As Long
    With ActiveCell
        r = .Offset(-1).End(xlUp).Row
        .FormulaR1C1 = "=COUNTIF(R" & r & "C:R[-1]C,""=""&R[-4]C[3])"
    End With
End Sub

 

[petegrant89]

Yes - thank you again!

 

[petegrant89]

I have one line of code that is returning a Compile error - "Expected: end of statement". The line of code is:
.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))".

To put it into context, I have attached my (horribly sloppy) code down below... the same screenshot of what the sheet looks like that I posted yesterday is: https://docs.google.com/file/d/0B_sH...it?usp=sharing. I have no idea why I'm receiving the compile error.


Sub Analysis()

Dim r As Long

ActiveCell.Offset(-5, 1).Select
ActiveCell.Value = "Total Sales"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=OFFSET(R[9]C[-6],-4,0,1,1)"
ActiveCell.Offset(1, -1).Select
ActiveCell.Value = "% Threshold"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range("BK4")
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1) * ActiveCell.Offset(-1, -1)
ActiveCell.Offset(1, -2).Select
ActiveCell.Value = "Exact?"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]>0,""YES"",""NO"")"
ActiveCell.Offset(0, 1).Select
With ActiveCell
r = .Offset(2, -3).End(xlUp).Row
.FormulaR1C1 = "=COUNTIF(R" & r & "C[-3]:R[2]C[-3],""=""&R[-1]C)"
End With
ActiveCell.Offset(1, -2).Select
ActiveCell.Value = "# Of Styles"
ActiveCell.Offset(0, 1).Select
With ActiveCell
x = .Offset(1, -2).End(xlUp).Row
.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))"
End With


End Sub