Customize ActiveCell.FormulaR1C1

Hi all!

See attached for sample file: Insert Row Number to Formula.xlsm

Issue:

Formula List sheet - shows Row # of agent in Sheet1
How do I write a formula where formula will consider the Row# of the agent specified in "Formula List" sheet?

Example: This is the formula for AGENT: ANDY

Please Login or Register  to view this content.

I want the same formula for AGENT: MARIE but Marie is in Row 4
How do I change

Please Login or Register  to view this content.

to reflect AGENT: MARIE's Row location per "Formula List" sheet?

Thank you in advance!

Your original macro for ANDY is inserted into cell D3, and the formula is already referencing that particular row, but with column-offset referencing style.

To change it for MARIE, just simply change
Range("D3").Select to Range("D4").Select
Range("O3").Select to Range("O4").Select
Selection.AutoFill Destination:=Range("D3:N3"), Type:=xlFillDefault to Selection.AutoFill Destination:=Range("D4:N4"), Type:=xlFillDefault.
Though it may not be the best way to do it.

You can also just insert the formulas directly for the entire range of cells:

Please Login or Register  to view this content.

Originally Posted by millz

Your original macro for ANDY is inserted into cell D3, and the formula is already referencing that particular row, but with column-offset referencing style.

To change it for MARIE, just simply change
Range("D3").Select to Range("D4").Select
Range("O3").Select to Range("O4").Select
Selection.AutoFill Destination:=Range("D3:N3"), Type:=xlFillDefault to Selection.AutoFill Destination:=Range("D4:N4"), Type:=xlFillDefault.
Though it may not be the best way to do it.

You can also just insert the formulas directly for the entire range of cells:

Please Login or Register  to view this content.

I want the VBA to do the range change automatically if I could

Originally Posted by smartbuyer

I want the VBA to do the range change automatically if I could

I don't understand what you mean, you need to explain in more detail what you want to do.

"Range change" from what to what?