Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the if statement for Macro coding?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the if statement for Macro coding?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the if statement for Macro coding?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to use the if statement for Macro coding?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to confirm the open dialogue box by using macro coding? | Excel Worksheet Functions | |||
How to close a excel file using Macro coding? | Excel Discussion (Misc queries) | |||
if then statement color coding (or another function???) | Excel Worksheet Functions | |||
Hidding Macro names and coding | Excel Discussion (Misc queries) | |||
soft-coding lines in a macro | Excel Discussion (Misc queries) |