R1C1 question pretty please!! 2

[ribhead]

I'm trying to put a formula into a cell using VBA but I keep getting an application defined error. Could someone please offer me a little advice? Here is my code.


Sub Addem_Up()
Dim lrow As Double
lrow = Rows.Cells(1, 1).End(xlDown).Row
Cells(lrow + 1, 1).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-lrow]C:R[-1]C)"
End Sub

For just $19.95 you too can have a beautiful smile.

 

[michaelbr55]

What is this sub supposed to do?

 

[ribhead]

It is supposed to find the last row used then enter the sum formula into the last row +1 to add up all the data. I thought if I knew what the lastrow was all I would have to do is insert the negative of that number in to the R1C1 format for the first row that will be totaled. Are you confused yet?

For just $19.95 you too can have a beautiful smile.

 

[michaelbr55]

You bettya?

 

[ribhead]

Lol! All I'm trying to do here is put a sum formula in to a cell using the R1C1 method. But it doesn't like me using a variable (-lrow) for the R[]. For example if I had 9 rows with numbers I'd want to put the formula in to the 10th row and my formula might look something like this
"=sum(R[-9]C:R[-1]C)"
"=sum(R[-lrow]C:R[-1]C)"

I was thinking that these are the same if I had 9 rows that I wanted to total. But apparently not.

For just $19.95 you too can have a beautiful smile.

 

[michaelbr55]

All right, all right that's enough my brain's about to explode, I see where you got the "=sum(R[-lrow]C:R[-1]C)"
recorded macros are such a mess, I couldn't get the sum function to work either so here is a simpler way, hope it fits the bill.

Sub Addem_Up()
Dim lastrow, x, y
lastrow = 1
Do While lastrow <> &quot;&quot;
x = x + 1
lastrow = Cells(x, 1)
Loop

For y = 1 To x
Cells(x + 1, 1).Value = Cells(x + 1, 1).Value + Cells(y, 1).Value
Next
End Sub

 

[Zathras]

ribhead: You were very close. Try this:

Sub Addem_Up()
Dim lrow As Double
lrow = Rows.Cells(1, 1).End(xlDown).Row
Cells(lrow + 1, 1).Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-

[blue]

" & lrow & "

[/color]

]C:R[-1]C)"
End Sub

 

[ribhead]

Hey cool Zathras now I don't feel like the moron that I really am....I mean eh? Heh thanks a bunch.

For just $19.95 you too can have a beautiful smile.

 

[Bowers74]

Hey ribhead,

You might want to think about changing your lrow = Rows.Cells(1, 1).End(xlDown).Row to the following:

lrow = Rows.Cells(65536, 1).End(xlUp).Row

Even if all of your cells are contiguous. It is better practice, because if you ever have a column in the future with non-contiguous cells, the End(xldown) won't give you the true last row.

Just a thought!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

Great thoght Mike, I didn't even think of this. It is much easier than what I've been doing. By the way any idea why I have to use the quotes around the variable? I know that wasn't your post but I'm not sure why the variable has to be enclosed in quotes.

For just $19.95 you too can have a beautiful smile.

 

[Bowers74]

I'm lost . . . Which post was that?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

My apologies this was Zathras' post. I'm not understanding why the variable is inside of quotes. Again sorry for the confusion.

Sub Addem_Up()
Dim lrow As Double
lrow = Rows.Cells(1, 1).End(xlDown).Row
Cells(lrow + 1, 1).Activate
ActiveCell.FormulaR1C1 = &quot;=SUM(R[-&quot; & lrow & &quot;]C:R[-1]C)&quot;
End Sub


For just $19.95 you too can have a beautiful smile.

 

[Zathras]

ribhead: If you look a little more closely, you will see that the variable is not inside of quotes.

 =SUM(R[-

and

 ]C:R[-1]C)

are inside of quotes.

The string you are putting into the FormulaR1C1 property is made up from three parts concatenated together, thus:

string & variable & string

When you use the ampersand VBA automatically makes the conversion from number to string when handling the variable to build up the entire string.

You can use the same method to concatenate values in cell formulas in a worksheet (instead of using CONCATENATE).

 

[Bowers74]

The variable is actually between two sets of quotes.

The Formula and FormulaR1C1 Properties only accept strings, but you can insert a variable into a string. The problem is getting it in there, because if you write it inside the string the name of the variable will be written into the string, so you need to concatinate two strings (using the & symbol) with the varible in between them:

Range("A1").Formula = "First_String" &

Variable

 & "Second_String"

I hope this is understandable because it is late and I need to get to bed. Get back to you tomorrow!

I hope this helps!


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

Hey thanks guys I won't be posting for a while because my head just exploded from all that knowledge. Just kidding great educational experience for me. In about 10 years I too will be somebody. Stars for everyone...Let's celebrate.



For just $19.95 you too can have a beautiful smile.