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

paliman

Active Member
Joined
Jul 7, 2002
Messages
255
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Try writing it like this in your code;

Code:
"=SUMIF(Sld2000!R1C2:R100C2," & AccNum & ",Sld2000!R1C8:R100C8)"
Code:
 
Upvote 0
Thank you.

I tried it, but when I hit enter it says "Compile error. Expected: end of statement"
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top