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

[paliman]

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

 

[JLGWhiz]

Try writing it like this in your code;

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

 

[paliman]

Thank you.

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

 

[JLGWhiz]

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]

Try writing it like this in your code;

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

It worked for me

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]

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.