[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?

 

[Joe4]

Try this:

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

 

[RockandGrohl]

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]

Try this:

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

Yesss!!

It's the square brackets! The square brackets I was missing!

 

[Joe4]

Yesss!!

It's the square brackets! The square brackets I was missing!

You are welcome. The key is to look at what "Rep1.Name" returns (you can see this with a simple MsgBox command, i.e. "MsgBox Rep1.Name", and then replace that part in your original hard-coded formula with the variable. Since you can see the square brackets in your original hard-coded formula, but not in the "Rep1.Name" command, you know that you have to leave those in with the hard-coded part of the formula.

 

[RockandGrohl]

You are welcome. The key is to look at what "Rep1.Name" returns (you can see this with a simple MsgBox command, i.e. "MsgBox Rep1.Name", and then replace that part in your original hard-coded formula with the variable. Since you can see the square brackets in your original hard-coded formula, but not in the "Rep1.Name" command, you know that you have to leave those in with the hard-coded part of the formula.

Hey

For some reason I am absolutely stumped at this.

Just says Application Defined or Object Defined error. From what I can see this should be working perfectly. The formula above it produces what I need, with the csv file referenced directly. Any help? Thanks.

 

[RockandGrohl]

Bump

 

[Joe4]

Is the "csv" file already open in the same instance of Excel?
What exactly is the value of "bnb.Name" at that point in time?
If you aren't sure, you can simply use:

MsgBox bnb.Name

in your code.

 

[RockandGrohl]

Is the "csv" file already open in the same instance of Excel?
What exactly is the value of "bnb.Name" at that point in time?
If you aren't sure, you can simply use:

MsgBox bnb.Name

in your code.

Hi Joe

Yep, it's open. bnb.name when hovered over is exactly, precisely the name of the csv file that's open.

 

[Joe4]

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