Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable excel formula
For I = 1 To count ActiveCell.FormulaR1C1 = "=RC[-2] - R13C12" ActiveCell.Offset(1, 0).Select Next I This loop is used to fill in the delay for all the female parent rows. "Count" is the number of female rows. The formula calculates the value of the cell two to the left of the active cell (the female parent GDU, this value changes in each record) minus the value in cell R13C12 (male parent GDU, all males are the same so this GDU value stays the same. I don't have the same number of males and females so this part of the formula has to be an absolute reference). I run this calcuation for blocks of records. When I finish one block I loop through the male parents in that block, find the count of female parents in the next block and want to go back to the above section, but with the correct reference for the male parent GDU, which will no longer be R13C12. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable excel formula
Hi Dave:
Don't you just flex the ActiveCell.FormulaR1C1 = "=RC[-2] - R13C12" as in ActiveCell.FormulaR1C1 = "=RC[-2] - R13C" & 12 + someadjustment where someadjustment is the offset to the cell R13C12? -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Dave" wrote: For I = 1 To count ActiveCell.FormulaR1C1 = "=RC[-2] - R13C12" ActiveCell.Offset(1, 0).Select Next I This loop is used to fill in the delay for all the female parent rows. "Count" is the number of female rows. The formula calculates the value of the cell two to the left of the active cell (the female parent GDU, this value changes in each record) minus the value in cell R13C12 (male parent GDU, all males are the same so this GDU value stays the same. I don't have the same number of males and females so this part of the formula has to be an absolute reference). I run this calcuation for blocks of records. When I finish one block I loop through the male parents in that block, find the count of female parents in the next block and want to go back to the above section, but with the correct reference for the male parent GDU, which will no longer be R13C12. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable excel formula
Hi Dave,
Obviously you have to change the R13C12 in the command you put into the "female" cells. Because VBA sees this command as just a string, you can change it programmatically - see below. But for this you need the address of this cell, i.e. row and column numbers. You didn't say how you "find" the next male parent. If it is in Activecell, use MaleAddr = ActiveCell.Address (ReferenceStyle:=xlR1C1) in the code. Here is some code that might get you started. I included comments to explain what's happening. Public MaleAddr As String ' address of current male Public Cntr As Integer ' counter for relev females Public rFem1 As Single ' row number for 1st female Public cFem1 As Single ' col nbr 1st fem Sub MainRoutine() ' in your code ' find cell containing the first / next Male ' say it's row 13, column 12, then r = 13 c = 12 MaleAddr = "R" & r & "C" & c ' ' load Female count into Cntr ' say it's 10 here Cntr = 10 ' ' Assume the first female is in ' row = rFem1 (say 14 here) ' and column = cFem1 (= 10 here) rFem1 = 14 cFem1 = 10 Call InsertFormula ' ' look for next male (as above) ' possibly in a do - loop ' End Sub Sub InsertFormula() ' first we make the command cmd = "=RC[-2] - " & MaleAddr For i = 0 To Cntr - 1 Cells(rFem1 + i, cFem1).FormulaR1C1 = cmd Next End Sub Hope this helps. -- Gerd "Dave" wrote: For I = 1 To count ActiveCell.FormulaR1C1 = "=RC[-2] - R13C12" ActiveCell.Offset(1, 0).Select Next I This loop is used to fill in the delay for all the female parent rows. "Count" is the number of female rows. The formula calculates the value of the cell two to the left of the active cell (the female parent GDU, this value changes in each record) minus the value in cell R13C12 (male parent GDU, all males are the same so this GDU value stays the same. I don't have the same number of males and females so this part of the formula has to be an absolute reference). I run this calcuation for blocks of records. When I finish one block I loop through the male parents in that block, find the count of female parents in the next block and want to go back to the above section, but with the correct reference for the male parent GDU, which will no longer be R13C12. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variable excel formula
Thanks everyone. I will let you know if this works.
"gerdmain" wrote: Hi Dave, Obviously you have to change the R13C12 in the command you put into the "female" cells. Because VBA sees this command as just a string, you can change it programmatically - see below. But for this you need the address of this cell, i.e. row and column numbers. You didn't say how you "find" the next male parent. If it is in Activecell, use MaleAddr = ActiveCell.Address (ReferenceStyle:=xlR1C1) in the code. Here is some code that might get you started. I included comments to explain what's happening. Public MaleAddr As String ' address of current male Public Cntr As Integer ' counter for relev females Public rFem1 As Single ' row number for 1st female Public cFem1 As Single ' col nbr 1st fem Sub MainRoutine() ' in your code ' find cell containing the first / next Male ' say it's row 13, column 12, then r = 13 c = 12 MaleAddr = "R" & r & "C" & c ' ' load Female count into Cntr ' say it's 10 here Cntr = 10 ' ' Assume the first female is in ' row = rFem1 (say 14 here) ' and column = cFem1 (= 10 here) rFem1 = 14 cFem1 = 10 Call InsertFormula ' ' look for next male (as above) ' possibly in a do - loop ' End Sub Sub InsertFormula() ' first we make the command cmd = "=RC[-2] - " & MaleAddr For i = 0 To Cntr - 1 Cells(rFem1 + i, cFem1).FormulaR1C1 = cmd Next End Sub Hope this helps. -- Gerd "Dave" wrote: For I = 1 To count ActiveCell.FormulaR1C1 = "=RC[-2] - R13C12" ActiveCell.Offset(1, 0).Select Next I This loop is used to fill in the delay for all the female parent rows. "Count" is the number of female rows. The formula calculates the value of the cell two to the left of the active cell (the female parent GDU, this value changes in each record) minus the value in cell R13C12 (male parent GDU, all males are the same so this GDU value stays the same. I don't have the same number of males and females so this part of the formula has to be an absolute reference). I run this calcuation for blocks of records. When I finish one block I loop through the male parents in that block, find the count of female parents in the next block and want to go back to the above section, but with the correct reference for the male parent GDU, which will no longer be R13C12. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Named Formula Weakly Interacts with a Variable Range on the Worksheet - Re-Visit | Excel Discussion (Misc queries) | |||
Variable in Excel Sum formula | Excel Discussion (Misc queries) | |||
entering formula using variable in Excel Macro | Excel Programming | |||
Including a variable number of columns in Excel Sum formula | Excel Programming | |||
Excel VBA-use variable in active cell formula problem | Excel Programming |