Formula needs to reference anchored relative cell

[mkerstei]

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?

 

[Guest]

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]

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

 

[Guest]

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

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

 

[Guest]

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]

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

 

[Guest]

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

Cheers,
/MP