Thanks for all the previous help I have received while working on this
project.

Currently I have a macro programmed that takes existing data, inserts
columns, and does equations on the existing data. It copies down each
column and produces a total on the last row. I need to write code in
that takes the cell directly to the left and multiplies it by the
total. For instance:
Cell B1 = A1 * A34
Cell B2 = A2 * A34

Now comes the tricky part. While cell B1 will always pull from Cell A1
and B2 from A2, etc. The row the total is in (in this case, 34) varies
by spreadsheet. In one it might be 34, in another it might be 290.
What is the easiest way to perform this?


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549058

You can always use something like:

Range("A1").End(xlDown)

....to find the cell at the end of the region, so provided that your sums are
always located at the end of column A, and that the data in column A is
contiguous this should work just fine.

Cheers,
/MP

"mkerstei" wrote:


Thanks for all the previous help I have received while working on this
project.

Currently I have a macro programmed that takes existing data, inserts
columns, and does equations on the existing data. It copies down each
column and produces a total on the last row. I need to write code in
that takes the cell directly to the left and multiplies it by the
total. For instance:
Cell B1 = A1 * A34
Cell B2 = A2 * A34

Now comes the tricky part. While cell B1 will always pull from Cell A1
and B2 from A2, etc. The row the total is in (in this case, 34) varies
by spreadsheet. In one it might be 34, in another it might be 290.
What is the easiest way to perform this?


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549058

Right, I understand the use of
Range("A1").End(xlDown)
But I do not know how to put that into an equation that will be copie
down the the bottom of the column.
right now it would be

Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]-Total

Where total would be the Range("A1").End(xlDown)
I know this is totally simple, but I just don't know VBA very well.
How do I put a macro formula into an excel formula

--
mkerste
-----------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=54905

Ah, okay, well what about this then (I've been very explicit about things in
the VBA code, just to make sure I'm clear enough, but if I'm not then don't
hesitate to ask me again):

==============

Private Sub LetsFillInTheBlanks()
' Get the last cell of the A column
' (requires data to be contiguous!)
Dim r As Range
Set r = Range("A1").End(xlDown)

' Get an absolute cell reference in
' RC notation (check AddressLocal()
' in the VBA Help for alternatives)
Dim s As String
s = r.AddressLocal(True, True, xlR1C1)

' Set the formulae for all of col B
Dim i As Long
For i = 1 To r.Row
Cells(i, 2).FormulaR1C1 = "=RC[-1] - " & s
Next i
End Sub

==============

"mkerstei" wrote:


Right, I understand the use of
Range("A1").End(xlDown)
But I do not know how to put that into an equation that will be copied
down the the bottom of the column.
right now it would be

Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]-Total

Where total would be the Range("A1").End(xlDown)
I know this is totally simple, but I just don't know VBA very well.
How do I put a macro formula into an excel formula?


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549058

By the way, I forgot to ask you: I'm not sure why your snippet said "RC[-3] -
Total" though -- wasn't it supposed to say something like "RC[-1] * Total" ?
Anyway, you'll figure out how to use and/or modify the example code I sent
you, no doubt...

Cheers,
/MP

"mkerstei" wrote:


Right, I understand the use of
Range("A1").End(xlDown)
But I do not know how to put that into an equation that will be copied
down the the bottom of the column.
right now it would be

Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[-3]-Total

Where total would be the Range("A1").End(xlDown)
I know this is totally simple, but I just don't know VBA very well.
How do I put a macro formula into an excel formula?


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549058

Perfect. Thank you so much. I worked on that thing for like 5 straigh
days and couldn't get it and you solved it. Thanks again

--
mkerste
-----------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...fo&userid=2568
View this thread: http://www.excelforum.com/showthread.php?threadid=54905

Sure, no probs. I'm glad that I could help you out a bit...

Cheers,
/MP

"mkerstei" wrote:


Perfect. Thank you so much. I worked on that thing for like 5 straight
days and couldn't get it and you solved it. Thanks again.


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=549058