PDA

View Full Version : [SOLVED] VBA SUM question



Billy C
02-10-2014, 08:57 AM
HiI have a some code that inserts a column based on a find i.e. look for a text string and insert a column. This works well, ColEnd is the column character My next step is to insert a sum that sums between ColStrt the first find column and ColEnd the last column in the find. The code below works when I specify the sum columns as “C” & “I” however this will change dependant on how many columns there are.Does anyone know how to change this code to enable me to use variables for the columns. Worksheets("VehicleListing").Range(ColEnd & "2:" & ColEnd & DataLstRw).Formula = "=SUM(VehicleListing!C2:I2)"

p45cal
02-10-2014, 09:11 AM
perhaps:
FirstCol = "C"
LastCol = "I"
Worksheets("VehicleListing").Range(ColEnd & "2:" & ColEnd & DataLstRw).Formula = "=SUM(VehicleListing!" & FirstCol & "2:" & LastCol & "2)"
?

Bob Phillips
02-10-2014, 10:10 AM
lastcol = Cells(2, Columns.Count).End(xlToLeft).Column
Worksheets("VehicleListing").Range(ColEnd & "2:" & ColEnd & DataLstRw).FormulaR1C1 = "=SUM(VehicleListing!RC3:R" & lastcol - 2 & ")"

Aussiebear
02-10-2014, 04:26 PM
"=SUM(VehicleListing!RC3:R" & lastcol - 2 & ")"


What is the last bit doing Bob? &")"

Billy C
02-11-2014, 01:41 AM
To all who have kindly contributed to this question Thank you very much!Everything is working and you have enhanced my knowledge.

Bob Phillips
02-11-2014, 03:28 AM
What is the last bit doing Bob? &")"

Just closing out the SUM function Ted.

Aussiebear
02-11-2014, 04:47 AM
Ok, so why does it need to be enclosed within inverted commas, and with the ampersand?

Bob Phillips
02-11-2014, 12:40 PM
Because I started with part of the SUM function string

"=SUM(VehicleListing!RC3:R"

which I then concatenated with a variable

& lastcol - 2

and so it need a losing bracket in the string, so that gets wrapped in quotes, and to concatenate it we need &

& ")"

Aussiebear
02-11-2014, 03:23 PM
So is that true of any formula with a variable included within it?

Bob Phillips
02-11-2014, 04:33 PM
If you are doing it from VBA, yes. You string together the fixed parts, functions and maybe some of the ranges, the dynamic parts that are in variables and anything needed to close it out. All of the text parts have to be quote-enclosed,a and all the bits are joined by ampersands.

Aussiebear
02-11-2014, 05:26 PM
So, in essence its only the underlined section that requires the inverted commas
"=SUM(VehicleListing!RC3:R" & lastcol - 2 & ")"

Bob Phillips
02-12-2014, 07:07 AM
Well, you could put it that way, but I wouldn't. I would say that any text has to be quote enclosed and those have to be joined with variables with &.

snb
02-12-2014, 09:02 AM
Some illustration:


Sub M_snb()
MsgBox "illustration for Aussiebear"
MsgBox "illustration for " & "Aussiebear"
MsgBox "illustration for " & 2 & " Aussiebear"
MsgBox "illustration for " & Date & " Aussiebear"
MsgBox "illustration for " & (1 = 1) & " Aussiebear"
MsgBox "illustration for " & (1 = 0) & " Aussiebear"
x12 = "mister"
MsgBox "illustration for " & x12 & " Aussiebear"
x20 = 50
MsgBox "illustration for " & 2 * x20 & " Aussiebear"
End Sub

Aussiebear
02-12-2014, 03:28 PM
Thank you snb