Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Worksheet Name variable in VBA formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Worksheet Name variable in VBA formula
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLS HELP AGAIN Reference Worksheet Name variable in VBA formula
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 ------------------------------------------------------------------------------------------------------------------------ Mike wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
making a portion of reference to a worksheet variable in a formula | Excel Discussion (Misc queries) | |||
Variable column reference in formula | Excel Discussion (Misc queries) | |||
Vlookup with variable worksheet reference | Excel Worksheet Functions | |||
how to reference a worksheet with a variable vs a name | Excel Programming | |||
Making a file and worksheet reference into a variable.... | Excel Worksheet Functions |