use variable to make things easy

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
118
I have the following code courtesy of Michael M. Is there away I could put a variable at the top of the code to change the year(withdraw_2018) to be, withdraw_2019, withdraw_2020etc. as the years change rather than hunting through the code looking for the year?
I tried;
Rich (BB code):
Dim X as string
X = withdraw_2018
and then used X inthe following code in place of withdraw_2018, but it failed.

Rich (BB code):
WithDraw = Range("withDraw_2018").Address(0, 0)
With Sheets("Fisher").Range("I2")
   .Formula ="=SUM(I3:" & WithDraw & ")"
   .NumberFormat ="$#,##0_);[Red]($#,##0)"
End With
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,665
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You'd need to use this:

Code:
WithDraw = Range(X).Address(0, 0)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,923
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Dim X As String

    X = "Withdraw_2018"

    With Sheets("Fisher").Range("I2")
       .Formula ="=SUM(I3:" & X & ")"
       .NumberFormat ="$#,##0_);[Red]($#,##0)"
    End With
 

Watch MrExcel Video

Forum statistics

Threads
1,114,323
Messages
5,547,244
Members
410,779
Latest member
che55ysia
Top