Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
118
Hi, When I run this I get a #NAME in I2. I would like to know what I'm doing wrong... Thanks!
BTW I there an easer way to do this?
Code:
Sub SumOfColumn()
'
'
'
Dim WithDraw As String  'named cell is WithDraw_2018
WithDraw = "WithDraw_2018"
   Sheets("sheet2").Select
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=+SUM(R[1]C:WithDraw)"
    Selection.NumberFormat = "$#,##0_);[Red]($#,##0)"
'
End Sub
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,714
.
What is it you are attempting to do ? I understand you are wanting to place a formula in A1, but I don't understand the remainder of your code.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
maybe this

Code:
WithDraw = Range("withDraw").Address(0, 0)
With Sheets("sheet2").Range("I2")
   .Formula = "=SUM(C1:" & WithDraw & ")"
   .NumberFormat = "$#,##0_);[Red]($#,##0)"
End With
 

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
118
This is a snip-it of a larger code, I wanted to put a single variable at the top of he code, so that when the year changes instead of changing 20 or 30 iterations in the code I could just change the variable.
Thanks
 

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
118

ADVERTISEMENT

This is a snip-it of a larger code, I wanted to put a single variable at the top of he code, so that when the year changes instead of changing 20 or 30 iterations in the code I could just change the variable.
Thanks
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
Does the code provided not work ???
If this is a piece of a lerger code you should provide all of the code. It's very difficult to give you a solution if we only get part of the story !
 

Gregfox

Board Regular
Joined
Apr 12, 2011
Messages
118

ADVERTISEMENT

Thanks the code works, but I really wanted to know why the code I provided didn't work.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,403
Office Version
  1. 2013
Platform
  1. Windows
The code didn't work because you needed to specify the cell address, not the name of the cell required !
you also asked
BTW I there an easer way to do this?
which is why I provided the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,108
Messages
5,545,986
Members
410,718
Latest member
ALM1GHTY
Top