VBA - Using the value of a string variable as the second argument

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Hello everyone.

I have a file with several sheets, each one named after an alphanumeric account code (18D08H, 20J03, and so on) and another one named Sld2000 populated with my data.

I want to put some values in each sheet, and at a certain point I need to use the value assigned to a variable as the condition in a conditional sum.

Here’s my attempt:

Dim AccNum As String
AccNum = ActiveSheet.Name
(…)
With ActiveCell
.Offset(1, 1).FormulaR1C1 = "=SUMIF(Sld2000!R1C2:R100C2,AccNum,Sld2000!R1C8:R100C8)"





In the spreadsheet I want to replace “AccNum” with the value of the variable; final formula should look like this (in the 18D08H tab):

=SUMIF(Sld2000!$B$1:$B$100;18D08H;Sld2000!$H$1:$H$100)

but so far I get the string “AccNum” as the second argument of my function:

=SUMIF(Sld2000!$B$1:$B$100;AccNum;Sld2000!$H$1:$H$100)

Another option would be to use the ActiveSheet name as the second argument, since AccNum always takes this value.

Thank you very much
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,931
Office Version
2013
Platform
Windows
Try writing it like this in your code;

Code:
"=SUMIF(Sld2000!R1C2:R100C2," & AccNum & ",Sld2000!R1C8:R100C8)"
Code:
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Thank you.

I tried it, but when I hit enter it says "Compile error. Expected: end of statement"
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,931
Office Version
2013
Platform
Windows
Thank you.

I tried it, but when I hit enter it says "Compile error. Expected: end of statement"
Could it be the commas and the semicolons? They don't mix very well.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Try writing it like this in your code;

Code:
"=SUMIF(Sld2000!R1C2:R100C2," & AccNum & ",Sld2000!R1C8:R100C8)"
Code:
It worked for me

Code:
Sub aTest()
    Dim AccNum As String
    
    AccNum = ActiveSheet.Name
 
    With ActiveCell
    .Offset(1, 1).FormulaR1C1 = "=SUMIF(Sld2000!R1C2:R100C2," & AccNum & ",Sld2000!R1C8:R100C8)"
    End With
    
End Sub
M.
 

paliman

Active Member
Joined
Jul 7, 2002
Messages
254
Thanks to everyone.

For some reason I couldn´t make it to work, but I made a turnaround that even looks better:

with activecell
.Value=Activesheet.Name
.Offset(1, 1).FormulaR1C1 = "=SUMIF(Sld2000!R1C2:R100C2,R[-1]C[-1],Sld2000!R1C8:R100C8)"


I will be looking for the reason my first attempt doesn´t work, but for the moment my macro is working fine.

Thanks again to everyone for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,253
Messages
5,467,562
Members
406,543
Latest member
semoredhawk

This Week's Hot Topics

Top