Macro Help, should be an easy solution

  • Thread starter Thread starter rugby199993
  • Start date Start date
R

rugby199993

Hi to All,

My question is this: I am trying to create a Macro that will change a
Fixed cell: SAY D2: In D2, I have a formula that says "=C9". What I am
trying to accomplish is to have a Macro button that I click on and it
automatically changing Cell D2, which has "=C9" to "=D9". The trick is
that I want to be able to click that button again and it to change the
formula to "=E9" and so on "=F9" etc...

I can't seem to figure this out and would appreciate some guidance.
Let me know if this is not enough info to complete this task. I know
how to record the macro and buttons but cannot adjust the code so that
the macro will do what I want.
Thanks for your time,

Paul
 
Paul,

When a problem is difficult, you either "Captain Kirk" it (change the
problem), or you cheat a little. I've opted for the latter, to let excel
figure out the changed cell reference you need. The following routine, of
slightly mousy means, saves what's in E2, copies D2 to E2, yielding a
formula with a relative cell reference one to the right, as you want, then
puts that back into D2, then restores the original contents of E2. No prize
for elegance here.

Sub BumpD2()
Dim SaveE2
SaveE2 = Range("E2").Formula ' save contents of E2
Range("D2").Copy Range("E2") ' Copy for relative cell reference
Range("D2") = Range("E2").Formula ' put in D2
Range("E2") = SaveE2 ' restore E2
End Sub
 
Back
Top