Dynamic range w/in formula in macro

[Trixie]

Hello,

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

I have this static range, but the end row could change as data i
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 woul
put into an input box at the beginning of the macro.

Thanks

 

[teylyn]

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],"""")"

 

[Bob Phillips]

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