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

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

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

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