Putting a formula in the first empty cell in a row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help me with code that can put a formula in the first empty cell
in a row? Basically, I want to replace the B3 in the following formula so
that it will put the formula in the first empty cell in row 3. I'm trying to
make the following formula dynamic.

Range("B3").FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"


any help would be appreciated.
 
Can you find the first open cell by starting at the far right and doing the
equivalent of End|leftArrow.

If yes

dim NextCell as range
with activesheet
set nextcell = .cells(3,.columns.count).end(xltoleft).offset(0,1)
end with

nextcell.formular1c1 = ....


Can anyone help me with code that can put a formula in the first empty cell
in a row? Basically, I want to replace the B3 in the following formula so
that it will put the formula in the first empty cell in row 3. I'm trying to
make the following formula dynamic.

Range("B3").FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"

any help would be appreciated.
Click to expand...
 
LR=cells(1,"b").end(down).row+1
or you may like this better
LR=cells(rows.count,"b").end(up).row+1

Range("B" & lr).FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"
 
Don, he wanted the first empty in row 3, so instead of:

LR=cells(1,"b").end(down).row+1

Try this:

LC = Cells(3,1).End(xlToRight).Column + 1
Cells(3, LC) .FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"



Don Guillett said:
LR=cells(1,"b").end(down).row+1
or you may like this better
LR=cells(rows.count,"b").end(up).row+1

Range("B" & lr).FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"

--
Don Guillett
SalesAid Software
(e-mail address removed)
timmulla said:
Can anyone help me with code that can put a formula in the first empty
cell
in a row? Basically, I want to replace the B3 in the following formula so
that it will put the formula in the first empty cell in row 3. I'm trying
to
make the following formula dynamic.

Range("B3").FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"


any help would be appreciated.
Click to expand...
Click to expand...
 
Thanks

--
Don Guillett
SalesAid Software
(e-mail address removed)
JLGWhiz said:
Don, he wanted the first empty in row 3, so instead of:

LR=cells(1,"b").end(down).row+1

Try this:

LC = Cells(3,1).End(xlToRight).Column + 1
Cells(3, LC) .FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"



Don Guillett said:
LR=cells(1,"b").end(down).row+1
or you may like this better
LR=cells(rows.count,"b").end(up).row+1

Range("B" & lr).FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"

--
Don Guillett
SalesAid Software
(e-mail address removed)
timmulla said:
Can anyone help me with code that can put a formula in the first empty
cell
in a row? Basically, I want to replace the B3 in the following formula
so
that it will put the formula in the first empty cell in row 3. I'm
trying
to
make the following formula dynamic.

Range("B3").FormulaR1C1 = "=SUM(R[1]C[-2]:R[65535]C[-2])"


any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top