Excel Macro question

[hobman]

I've created a macro and assigned it a hotKey for the Product formula (=product(1+C5:C10)-1).

The macro works fine but the short coming is that, the range that it selects is the range I selected when I recorded the macro. So in the above example, it would only select rows C5-C10. The way the macro below works is that, I would click in a cell right below my numbers (I have numbers in rows C5-C10) so I would click in row 11 and ctrl+q which does the product formula.

How can I get it to select all rows with a number in them above C11?

Macro:
ActiveCell.FormulaR1C1 = "=PRODUCT(1+R[-6]C:R[-1]C)-1"

Selection.FormulaArray = "=PRODUCT(1+R[-6]C:R[-1]C)-1"
Selection.Style = "Percent"
Selection.NumberFormat = "0.00%"

thanks,
H.

 

[SkipVought]

Hi,

Sub MyProduct()
    With ActiveCell
        .FormulaArray = "=Product(1 + (" & Range(.Offset(-6), .Offset(-1)).Address & ")) - 1"
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[hobman]

thanks for the reply skip. Your formula also covers just the specified amount of cells. I was hoping to find a way for it to not just do "-6" always but to grab all cells above active cell which have values in them (non-blank).

thanks for your help.
H.

 

[electricpete]

If you want the computation based on everything above the active cell, then you could use a small modification of Skip's code

Sub MyProduct()
    With ActiveCell
        .FormulaArray = "=Product(1 + (" & Range(.Offset(-ActiveCell.Row + 1), .Offset(-1)).Address & ")) - 1"
    End With
End Sub

But this wouldn't react well to non-numeric data in the column above. Might there be non-numeric data above?

 

[hobman]

yeah, there might be. I was hoping that I have numbers in the following ways:

Row 1 Column Title
Row 2 5.0%
Row 3 1.0%
Row 4 (completely blank)
Row 5 .01%
Row 6 .02%
Row 7 1.0%
Row 8 (Product Formula)

What I wanted was, when I run the macro after clicking in row 8, for it to grab the rows above it until it hits blank. So in this example, for it not to include anything above row 5.

thanks again.
H.

 

[electricpete]

This will check for non-numeric data above the selected cell and ignore it:

Sub MyProduct2()
Dim iRowIndex As Integer
Dim doProduct As Double
Dim vaThisCellValue As Variant

    doProduct = 1
    For iRowIndex = 1 To ActiveCell.Row - 1
        vaThisCellValue = Cells(iRowIndex, ActiveCell.Column).Value
        If IsNumeric(vaThisCellValue) Then
            doProduct = doProduct * (vaThisCellValue + 1)
            
        End If
    Next iRowIndex
ActiveCell.Value = doProduct - 1
End Sub

 

[electricpete]

whoops. Did not discard everything above the first blank above the active cell. Guess we have to search upwards from activecell until we find non-numeric or blank?

 

[hobman]

man, you are pretty good at this. yes, the only problems with your latest code is that

1. what you mentioned above (doesn't ignore blank cells)
2. it applies "pasteSpecial" to the answer (hard codes it)

I think I will continue looking into this so I can manupilate what you gave me.

thanks for your help.
H.

 

[SkipVought]

Sub MyProduct()
    With ActiveCell
        .FormulaArray = "=Product(1 + (" & Range(.Offset(-1).end(xlup), .Offset(-1)).Address & ")) - 1"
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[electricpete]

Unless xlup gets you to the header cell.

 

[hobman]

Skip,

That was exactly what I was looking for. Thanks much to both of you.

H.

 

[SkipVought]

Try this to compensate for headings in the column...

Sub MyProduct()
    Dim r As Range
    With ActiveCell
        Set r = .Offset(-1).End(xlUp)
        Do While Not IsNumeric(r.Value)
            Set r = r.Offset(1)
        Loop
        .FormulaArray = "=Product(1 + (" & Range(r, .Offset(-1)).Address & ")) - 1"
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]