Cell Reference

[Brian]

I'm looking for tips on how to reference the first or second cell in a row.

Specifically a calculation in J3:J1000 references a number value in J1 and K1 as well as a date value in J2 and K2 (>=J2 and < K2). Likewise a calculation in K3:K1000 references a number value in K1 and L1 as well as a date value in K2 and L2. Also there is a reference to a date in E3:E1000 and a calculation done with a value in I3:I1000.

I have this setup as formulas which work fine but am trying to rewrite in VBA. I am not looking for the whole formula to be written in VBA, just some help in how to reference a specific cell in a column.

The formula I'm using now is:

=IF(AND(DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5))>=R$2,DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5))<S$2),DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5)),"")

The bold areas are where I am trying ot reference the first or second rows.

Thanks,

Brian

 

[STEVE BELL]

Brian,

I am not absolutely sure of what you are asking. But if you are trying to
write formulas through code and use variables
to indicate row and column you might want to look at R1C1 (row/column)
notation rather than A1 notation.

Cells(1,1).FormulaR1C1= "=R1C2 + R1C3"
is the same as Range("A1").Formula = "$B$1 + $C$1"

You can put the formula into a range with:
Range(Cells(1,1),Cells(10,1)) = "R1C2 + R1C3"

to use relative reference - use [ ] for row and column numbers
Range(Cells(1,1),Cells(10,1)) = "=RC[1] + RC[2]"
= "=B1 + C1"

you can create variables for row & columns such as:
Dim rw as Integer, col as interger
' add formulas to define rw & col '
' you can use If then statements to define your variables'

Range(Cells(1,1),Cells(10,1)) = "R" & rw & "C" & col

Watch carefully for where to use " " & where not to use them.

see if this gets you started in the direction you seek.

--
rand451
"Brian" <Brian at minnplace dot com> wrote in message
I'm looking for tips on how to reference the first or second cell in a row.

Specifically a calculation in J3:J1000 references a number value in J1 and
K1 as well as a date value in J2 and K2 (>=J2 and < K2). Likewise a
calculation in K3:K1000 references a number value in K1 and L1 as well as a
date value in K2 and L2. Also there is a reference to a date in E3:E1000
and a calculation done with a value in I3:I1000.

I have this setup as formulas which work fine but am trying to rewrite in
VBA. I am not looking for the whole formula to be written in VBA, just some
help in how to reference a specific cell in a column.

The formula I'm using now is:

=IF(AND(DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5))>=R$2,DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5))<S$2),DATE(YEAR($Q5),MONTH($Q5)+($J5+(ROUNDDOWN(ABS(R$1/$J5),0)*$J5)),DAY($Q5)),"")The bold areas are where I am trying ot reference the first or second rows.Thanks,Brian