Variable excel formula

[Dave]

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.

 

[Martin Fishlock]

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?

 

[gerdmain]

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.

 

[Dave]

Thanks everyone. I will let you know if this works.

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

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.