[VBA] Sumifs where the sheet name is variable

[RockandGrohl]

Hi guys, I'm building a tool which opens two different workbooks which I DIM as Workbook called "Rep1" and "Rep2" in VBA.

What I need to do is reference them in a SUMIFS formula, like this:

Range("C3:C" & Lastrow).FormulaR1C1 = "=SUMIFS('[Rep1.xlsx]1'!R7C7:R6833C7,'[Rep1.xlsx]1'!R7C3:R6833C3,RC[-2])"

Ignoring the fixed sum references for the minute, which I do know how to change, I can't remember how to reference the name. I know it's something like ' & Rep1.name & ' but I can't remember exactly what I replace and obviously the errors I get in VBA are just saying "This formula isn't right" lol.


If anyone can help jog my memory that would be super duper. Thanks.

EDIT:
Actually I've evidently also forgotten how to reference a bloody dynamic range too, it should be something like:
R7C7:R" & LastrowRep1 - 1 & "C7

Right?

 

[RockandGrohl]

If the line above the one in yellow works, try making it exactly the same format as that one.

Hi, sorry I'm confused? They are the same format - the red line is the same formula but with the sheet name written explicitly.

 

[Joe4]

Not quite.
The first one has square brackets, the second one does not.

If you find a version that works, you will want to copy it exactly.

 

[RockandGrohl]

Not quite.
The first one has square brackets, the second one does not.

If you find a version that works, you will want to copy it exactly.

Isn't that because I am trying to open a workbook I'm defining as "bnb" which has a variable name?

What I'm trying to do is load 3 different types of sheets. The sheets will always have different names. One will be named "defiblahblah 2 bnb.csv" another instance of these sheets could have the name "sgtblahblah bnb.csv"

So I'm trying to give them a variable name of "bnb" to reference them.

 

[Joe4]

Isn't that because I am trying to open a workbook I'm defining as "bnb" which has a variable name?

I though you said the CSV file was already open when that line of code runs?
VLOOKUP only works on open files. So the file needs to be opened BEFORE that line of code runs.

It would probably be helpful if you posted the code for the ENTIRE procedure, so we can see all that it is doing, and how all your variables are being defined.
And when posting VBA code, please do not post screen images - we cannot do anything with that (other than look at it).
Please copy/paste and use the VBA code tags, as decribed here: How to Post Your VBA Code

 

[RockandGrohl]

I though you said the CSV file was already open when that line of code runs?
VLOOKUP only works on open files. So the file needs to be opened BEFORE that line of code runs.

It would probably be helpful if you posted the code for the ENTIRE procedure, so we can see all that it is doing, and how all your variables are being defined.
And when posting VBA code, please do not post screen images - we cannot do anything with that (other than look at it).
Please copy/paste and use the VBA code tags, as decribed here: How to Post Your VBA Code

Sorry, I think we're misunderstanding each other here mate


Basically, I am downloading 3 different data sheets -
[username] bnb.csv
[username] internal.csv
[username] token.csv

The username is always going to different, so the filenames will be different,

These three sheets are opened by the macro at the start, and then I use formulas to reference data in each of the three sheets.

The way I define them as variable is to open the bnb sheet, set it as BNB, and then I can refer to it in formulas like '[" & bnb.name & "]'. The others will be referenced as inter for internal and tok for token.

This isn't working for some reason, even though the sheet is open, so for troubleshooting I've replaced the '[" & bnb.name & "]' with the name of the sheet as it would look with a Macro recorder. I then confirm that this formula places correctly, and therefore there's a problem with referencing this worksheet for use with a variable name.

I've done this in the past, so I know it works, but I can't work out what the error is.

I hope this helps you understand, and thanks for your time.

 

[Joe4]

I still would like to see the rest of the code in that VBA procedure, expecially the part that is opening the files and setting the variables. In order to try to recreate it to debug it on my side, I need to see the other parts of the code so we will be comparing "apples-to-apples".

 

[RockandGrohl]

I still would like to see the rest of the code in that VBA procedure, expecially the part that is opening the files and setting the variables. In order to try to recreate it to debug it on my side, I need to see the other parts of the code so we will be comparing "apples-to-apples".

Perfect, gotcha. I'll do this whenever I'm at my PC next. Thanks!