Reference Worksheet Name variable in VBA formula

[Mike]

Having trouble using variable in SUMPRODUCT formula...any help/comments
appreciated. I don't know how to use quotation marks (double or
single) correctly.


Dim FPsh = ActiveSheet.Name 'assign whsht name as variable FPsh
Worksheets("2005").Select

Range("E6").Formula = "=SUMPRODUCT((" & FPsh & "!$E$1:$E$1000=$C6)*" &
"(" & FPsh & "!$B$1:$B$1000>(--(""2004/12/31"")))*" & "((" & FPsh &
"!$B$1:$B$1000<(--(G$4))))*" & "(((" & FPsh & "!$J$1:$J$1000)" & ")"

Many thanks...Mike

 

[kassie]

You should use Dim FPsh as worksheet to declare, and SET FPsh = whatever it is.
As far as the quotation marks are concerned - If you want to use quotation
marks in a formula, then in VBA you must enclose it in quotation marks. The
formula
=IF(B1="","",B1), in VBA looks like
Range("E1").Formula = "=IF(B1="""","""",B1)".
The formula ='[TestOne.xls]Sheet1'!$A$1 when using a variable, can be written
Range("E1").formula = "='[" & wbkOne &"]Sheet1'!$A$1"
In this example, a single quotation mark is used to enclose a file
reference. Otherwise, a single quotation mark is used to mark a comment

 

[Mike]

I tried again, but still having trouble. My apology...let me try again.

I declared FPsh as Worksheet in my declarations, but no luck. I
originally tried declaring as a String.

I need to assign a variable for the active worksheet Name property,
since I run the code in different workbooks and the worksheet name
changes each time I run the code. I want to assign a variable to the
worksheet Name property and use that variable in my SUMPRODUCT formula.

Below is the troublesome area of code from my module. My problem seems
to be incorrect syntax for variable(s) and how to write the SUMPRODUCT
formula code so that the variable is used for worksheet name.

Your help is sincerely appreciated... Mike
-------------------------------------------------------------------------------
Sub IS_Setup2005()

Dim fName As String
Dim msg As String
Dim FPsh As Worksheet
Dim FPsheetName2 As Worksheet

Application.DisplayAlerts = True

Sheet1.Select
Set FPsheetName2 = ActiveWorksheet

Columns("N:N").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("N1").Select
'Range(Worksheets("Sheet2").Select
Range("AZ1:AZ100").Value =
Worksheets("Sheet2").Range("D1:E100").Value
Range("N2").Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$AZ$2:$AZ88"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("O2").Formula =
"=IF(N1<>"""",VLOOKUP(N1,'2005'!B$6:C$99,2,FALSE),"""")"
Range("N2:O2").Select
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
End With
Selection.AutoFill Destination:=Range("N2:O1000"),
Type:=xlFillDefault
Range("N:N").ColumnWidth = 34.29
Range("O:O").ColumnWidth = 4.43

Range("A1").Select
Set FPsh = Worksheets.ActiveSheet.Name
'ActiveSheet.Name = FPsh

Worksheets("2005").Select
Range("E6").Formula = "=SUMPRODUCT((" & FPsh & "!$E$1:$E$1000=$C6)*" &
"(" & FPsh & "!$B$1:$B$1000>(--(""2004/12/31"")))*" & "((" & FPsh &
"!$B$1:$B$1000<(--(G$4))))*" & "(((" & FPsh & "!$J$1:$J$1000)" & ")"

Range("G6:G96").Formula = Range("E6:E96").Formula
Range("I6:I96").Formula = Range("E6:E96").Formula
Range("K6:K96").Formula = Range("E6:E96").Formula
Range("M6:M96").Formula = Range("E6:E96").Formula
Range("O6:O96").Formula = Range("E6:E96").Formula
Range("Q6:Q96").Formula = Range("E6:E96").Formula
Range("S6:S96").Formula = Range("E6:E96").Formula
Range("U6:U96").Formula = Range("E6:E96").Formula
Range("W6:W96").Formula = Range("E6:E96").Formula
Range("Y6:Y96").Formula = Range("E6:E96").Formula
Range("AA6:AA96").Formula = Range("E6:E96").Formula

Application.DisplayAlerts = True
ActiveWindow.LargeScroll up:=3
Range("B2").Select
MsgBox "Verify Office Name is completed"

fName = Application.GetSaveAsFilename(filefilter:="Excel Files (*.xls),
*.xls")

If fName <> "False" Then
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlNormal
msg = "File is saved at location " & _
(ActiveWorkbook.FileFormat = xlNormal) & _
vbNewLine & "full path: " & ActiveWorkbook.FullName
MsgBox msg
Application.DisplayAlerts = True
Else
MsgBox ("You did not save the file")
End If

End Sub
------------------------------------------------------------------------------------------------------------------------