Vba Formula To Include Activesheet Name

[LuisE]

Thanks in advanceHow can I get the following formula to work by making it
reference the name of the active sheet? Where lr and br are variables.


Cells(5, 4).Formula = "=SUMPRODUCT((ActiveSheet!$R$" & lr & ":$R$" & br &
"=$B$14)*(ActiveSheet!$I$" & lr & ":$I$" & br & ">0))"

Thanks in advance

 

[JLGWhiz]

Cells(5, 4).Formula = "=SUMPRODUCT((ActiveSheet.Name!$R$" & lr & ":$R$" _
& br & "=$B$14)*(ActiveSheet.Name!$I$" & lr & ":$I$" & br & ">0))"

 

[Tim Zych]

The quick and easy way for your own macro is:

Cells(5, 4).Formula = "=SUMPRODUCT((" & ActiveSheet.Name & "!$R$" & lr &
":$R$" & br & "=$B$14)*(" & ActiveSheet.Name & "!$I$" & lr & ":$I$" & br &
">0))"


The more robust way if someone else is using your code or if you don't want
your macro to break intermittently. As a rule, wrap single quotes around the
sheet name, and double the single quotes within the sheet name.

Cells(5, 4).Formula = "=SUMPRODUCT(('" & Replace(ActiveSheet.Name, "'",
"''") & "'!$R$" & lr & ":$R$" & br & "=$B$14)*('" &
Replace(ActiveSheet.Name, "'", "''") & "'!$I$" & lr & ":$I$" & br & ">0))"

The top one will work as long as the sheet name has no spaces or
apostrophes. The bottom one will work for any sheet name.

Here is an example to illustrate the difference between the two.

Sub Test()

Dim lr As Long, br As Long
lr = 4
br = 4

Dim shtName As String

' This works because the sheet name has no spaces or single quotes
ActiveSheet.Name = "TimsSheet"
shtName = ActiveSheet.Name
Cells(5, 4).Formula = "=SUMPRODUCT((" & shtName & "!$R$" & lr & ":$R$" & br
& "=$B$14)*(" & shtName & "!$I$" & lr & ":$I$" & br & ">0))"

' This works because the sheet reference is properly modified to handle
spaces and single quotes
ActiveSheet.Name = "Tim's Sheet"
shtName = ActiveSheet.Name
shtName = "'" & Replace(shtName, "'", "''") & "'"
Cells(6, 4).Formula = "=SUMPRODUCT((" & shtName & "!$R$" & lr & ":$R$" & br
& "=$B$14)*(" & shtName & "!$I$" & lr & ":$I$" & br & ">0))"

' This works
ActiveSheet.Name = "TimsSheet"
shtName = ActiveSheet.Name
shtName = "'" & Replace(shtName, "'", "''") & "'"
Cells(7, 4).Formula = "=SUMPRODUCT((" & shtName & "!$R$" & lr & ":$R$" & br
& "=$B$14)*(" & shtName & "!$I$" & lr & ":$I$" & br & ">0))"

' This fails because the sheet reference has not been modified to handle the
spaces or single quotes
ActiveSheet.Name = "Tim's Sheet"
shtName = ActiveSheet.Name
Cells(8, 4).Formula = "=SUMPRODUCT((" & shtName & "!$R$" & lr & ":$R$" & br
& "=$B$14)*(" & shtName & "!$I$" & lr & ":$I$" & br & ">0))"


End Sub

Although, in your usage as posted, you don't really need to use ActiveSheet
name, since active sheet is what is actually referred to without the
explicit reference. Copying the formula to another sheet will retain the
reference, so it has some benefit.

 

[Dave Peterson]

Since Cells(5,4) isn't qualified, I'm betting that it's on the activesheet, too:

Cells(5, 4).Formula = "=SUMPRODUCT(($R$" & lr & ":$R$" & br & "=$B$14)" _
& "*($I$" & lr & ":$I$" & br & ">0))"