Can't get macro to run correctly

[courtneyd112]

Good morning -
I have written a macro that works correctly ONLY when I click on the cell that the macro starts in. If I try to run the macro if a random cell is selected, it wont run correctly. How can I get the macro to run the same everytime regardless of which cell is selected? Thank you!

 

[MrKowz]

Welcome to the forums!

Can you please post the code you are currently using, as well as what cell you want it to start from?

 

[RSXchin]

the problem is that you used "relative references" (just guessing)

 

[courtneyd112]

ActiveCell.FormulaR1C1 = "SUPD"
Range("E1").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[5]C[-2]:R[77]C[-2],""SUPD"")"
Range("D2").Select
ActiveCell.FormulaR1C1 = "SUPQ"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[4]C[-2]:R[73]C[-2],""SUPQ"")"
Range("D3").Select
ActiveCell.FormulaR1C1 = "SYKQ"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[3]C[-2]:R[82]C[-2],""SYKQ"")"
Range("D4").Select
ActiveCell.FormulaR1C1 = "SYKP"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(R[2]C[-2]:R[72]C[-2],""SYKP"")"
Range("D5").Select




Want it to start in D1

 

[RSXchin]

do you want it to ALWAYS start in D1? or will it change in your usage?

 

[courtneyd112]

yes, always want it to start in D1 - regardless of what cell is selected on the page

i didn't use relative references.

 

[JackDanIce]

Try:

Range("D1") = "SUPD"
Range("E1").FormulaR1C1 = "=COUNTIF(R[5]C[-2]:R[77]C[-2],""SUPD"")"
Range("D2")= "SUPQ"
Range("E2").FormulaR1C1 = "=COUNTIF(R[4]C[-2]:R[73]C[-2],""SUPQ"")"
Range("D3") = "SYKQ"
Range("E3").FormulaR1C1 = "=COUNTIF(R[3]C[-2]:R[82]C[-2],""SYKQ"")"
Range("D4") = "SYKP"
Range("E4") .FormulaR1C1 = "=COUNTIF(R[2]C[-2]:R[72]C[-2],""SYKP"")"
Range("D5").Select

 

[RSXchin]

[d1] = "SUPD"
[E1] = "=COUNTIF(R[5]C[-2]:R[77]C[-2],""SUPD"")"
[D2] = "SUPQ"
[E2] = "=COUNTIF(R[4]C[-2]:R[73]C[-2],""SUPQ"")"
[D3] = "SYKQ"
[E3] = "=COUNTIF(R[3]C[-2]:R[82]C[-2],""SYKQ"")"
[D4] = "SYKP"
[E4] = "=COUNTIF(R[2]C[-2]:R[72]C[-2],""SYKP"")"

you forgot to tell it to start at D1

 

[courtneyd112]

thanks everyone!