Hello,

I have been trying to figure out if there is a way to allow a dynamic
range within a formula in VBA.

I have this static range, but the end row could change as data is
added/deleted:

Range("K11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-6]:R[18]C[-6],"""")"

I'd like to be able to change the R[18] to whichever row the user would
put into an input box at the beginning of the macro.

Thanks!


--
Trixie

~TRIXIE
------------------------------------------------------------------------
Trixie's Profile: 438
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175466

Microsoft Office Help

Hi, I'm no VBA buff, but since the formula is essentially a text string,
you can just concatenate it with any other value, like


Code:
--------------------

stRow = InputBox("Which row are we interested in?")
Range("K11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-6]:R[" & stRow & "]C[-6],"""")"


--------------------


I may be completely off, but I'm sure I'll get corrected :)


--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=175466

Microsoft Office Help

No need for the select

stRow = InputBox("Which row are we interested in?")
Range("K11").FormulaR1C1 = "=COUNTIF(R[-9]C[-6]:R[" & stRow &
"]C[-6],"""")"

HTH

Bob

"teylyn" wrote in message
...

Hi, I'm no VBA buff, but since the formula is essentially a text string,
you can just concatenate it with any other value, like


Code:
--------------------

stRow = InputBox("Which row are we interested in?")
Range("K11").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-9]C[-6]:R[" & stRow &
"]C[-6],"""")"


--------------------


I may be completely off, but I'm sure I'll get corrected :)


--
teylyn

Teylyn -- 'teylyn.posterous.com' (http://teylyn.posterous.com)
------------------------------------------------------------------------
teylyn's Profile: 983
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=175466

Microsoft Office Help