How to use the if statement for Macro coding? in Excel Worksheet Functions    |   |  Help   Search For:     In:   Office   Excel     Excel General Questions     Excel New users     Excel Application Errors     Excel Charts     Excel Programming     Excel Setup     Excel Worksheet Functions              Eric 6/19/2007 12:43 AM PST   Question   IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN { / Processing following code Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3 Workbooks("1.xls").Close savechanges:=True } else / Processing nothing Does anyone have any suggestions on how to code the if statement for Excel Macro? Thank in advance for any suggestions Eric     Was this post helpful to you?         Reply |   Top           Mike H 6/19/2007 1:51 AM PST   Answer   Eric, Try this combination of Function and Sub:- Change you paths and workbook names to suit:- Sub marine() x = TheValue("c:\", "Book2.xls", "Sheet1", "A1") If x = 1 Then Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3 Workbooks("book2.xls").Close savechanges:=True Else MsgBox ("The value was " & x) End If End Sub Function TheValue(Path, WorkbookName, Sheet, Addr) As String Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets.Add Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!" & Addr TheValue = Range("A1").Value ActiveSheet.Delete Application.ScreenUpdating = True End Function Mike "Eric" wrote: > IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN > { / Processing following code > Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3 > Workbooks("1.xls").Close savechanges:=True > } else > / Processing nothing > > Does anyone have any suggestions on how to code the if statement for Excel > Macro? > Thank in advance for any suggestions > Eric     Was this post helpful to you?         Reply |   Top           Eric 6/19/2007 8:15 AM PST   Thank you for your suggestions For the function, can I use Addr instead of "A1" since this cell location could be changed based on different files? I try to replace "A1" with Addr, but this does not work. Could you please give me any suggestions? Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!" & Addr TheValue = Range("A1").Value Thank you for your suggestions Eric "Mike H" wrote: > Eric, > > Try this combination of Function and Sub:- Change you paths and workbook > names to suit:- > > Sub marine() > x = TheValue("c:\", "Book2.xls", "Sheet1", "A1") > If x = 1 Then > Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3 > Workbooks("book2.xls").Close savechanges:=True > Else > MsgBox ("The value was " & x) > End If > End Sub > > Function TheValue(Path, WorkbookName, Sheet, Addr) As String > Application.DisplayAlerts = False > Application.ScreenUpdating = False > Worksheets.Add > Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & > "'!" & Addr > TheValue = Range("A1").Value > ActiveSheet.Delete > Application.ScreenUpdating = True > End Function > > Mike > > "Eric" wrote: > > > IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN > > { / Processing following code > > Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3 > > Workbooks("1.xls").Close savechanges:=True > > } else > > / Processing nothing > > > > Does anyone have any suggestions on how to code the if statement for Excel > > Macro? > > Thank in advance for any suggestions > > Eric     Was this post helpful to you?         Reply |   Top           Mike H 6/19/2007 2:08 AM PST   Simpler:- Sub human() Application.DisplayAlerts = False Path = "c:\" WorkbookName = "Book2.xls" Sheet = "Sheet1" Addr = "A1" Worksheets.Add Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet & "'!" & Addr TheValue = Range("A1").Value ActiveSheet.Delete If TheValue = 1 Then Workbooks.Open Filename:="c:\book2.xls", UpdateLinks:=3 Workbooks("book2.xls").Close savechanges:=True Else MsgBox ("The value was " & TheValue) End If End Sub Mike "Eric" wrote: > IF( 'E:\file\[1.xls]Sheet1'!$A$1 = 1 THEN > { / Processing following code > Workbooks.Open Filename:="E:\file\1.xls", UpdateLinks:=3 > Workbooks("1.xls").Close savechanges:=True > } else > / Processing nothing > > Does anyone have any suggestions on how to code the if statement for Excel > Macro? > Thank in advance for any suggestions > Eric     Was this post helpful to you?         Reply |   Top         Return to Microsoft Communities   Notify me of replies