Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula needs to reference anchored relative cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing relative to a formula reference | Excel Worksheet Functions | |||
VLOOKUP anchored cell reference changing when macro runs | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
Relative Cell Reference | Excel Worksheet Functions | |||
Using Relative Reference in a Formula | Excel Programming |