Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vba Formula To Include Activesheet Name
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vba Formula To Include Activesheet Name
Cells(5, 4).Formula = "=SUMPRODUCT((ActiveSheet.Name!$R$" & lr & ":$R$" _
& br & "=$B$14)*(ActiveSheet.Name!$I$" & lr & ":$I$" & br & "0))" "LuisE" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vba Formula To Include Activesheet Name
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. -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility Free and Pro versions "LuisE" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vba Formula To Include Activesheet Name
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))" LuisE wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
include sheet name in formula | Excel Discussion (Misc queries) | |||
Use a password in VBA ActiveSheet.protect & ActiveSheet.unprotect? | Excel Programming | |||
Copying new activesheet after other activesheet is hidden? | Excel Programming | |||
Sum if formula to include value in col Q when no value in col P for that row | Excel Discussion (Misc queries) | |||
Modify A Formula To Include AND | Excel Worksheet Functions |