If Then Else
 

I am trying to figure out how to set my macro to check a certain variable and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as the file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value of B7
that I would get a message "File Exists" but if there was no file with the
name as the value in B7 then it would tell me that "File Does Not Exist".

The code above no matter what value I have in B7 it tells me "File Does Not
Exist" even if i use a value in B7 that already has a file saved as the value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value of B7
that I would get a message "File Exists" but if there was no file with the
name as the value in B7 then it would tell me that "File Does Not Exist".

The code above no matter what value I have in B7 it tells me "File Does
Not
Exist" even if i use a value in B7 that already has a file saved as the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

That may be the problem Tom however I am not sure how to include the ".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value of B7
that I would get a message "File Exists" but if there was no file with the
name as the value in B7 then it would tell me that "File Does Not Exist".

The code above no matter what value I have in B7 it tells me "File Does
Not
Exist" even if i use a value in B7 that already has a file saved as the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain
variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value
of B7
that I would get a message "File Exists" but if there was no file with
the
name as the value in B7 then it would tell me that "File Does Not
Exist".

The code above no matter what value I have in B7 it tells me "File
Does
Not
Exist" even if i use a value in B7 that already has a file saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

Thank you very much Tom!

Ryan Hess

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain
variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value
of B7
that I would get a message "File Exists" but if there was no file with
the
name as the value in B7 then it would tell me that "File Does Not
Exist".

The code above no matter what value I have in B7 it tells me "File
Does
Not
Exist" even if i use a value in B7 that already has a file saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

And you have another response at your other thead.

Curt wrote:

Understand part of it. Am new to this so here goes. Have filesaveas called
user enters a filename then save a copy as. in between I should check to see
if file exists. Then call for a diff name if it does or reject however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain
variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value
of B7
that I would get a message "File Exists" but if there was no file with
the
name as the value in B7 then it would tell me that "File Does Not
Exist".

The code above no matter what value I have in B7 it tells me "File
Does
Not
Exist" even if i use a value in B7 that already has a file saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess








--

Dave Peterson

If Then Else
 

Need to save the existing workbook and relocate values for next application
useing savecopy allows workbook to stay inplace and be updated.
Thanks much

"Ron de Bruin" wrote:

Hi Curt

Why not use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Curt" wrote in message ...
Understand part of it. Am new to this so here goes. Have filesaveas called
user enters a filename then save a copy as. in between I should check to see
if file exists. Then call for a diff name if it does or reject however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain
variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value
of B7
that I would get a message "File Exists" but if there was no file with
the
name as the value in B7 then it would tell me that "File Does Not
Exist".

The code above no matter what value I have in B7 it tells me "File
Does
Not
Exist" even if i use a value in B7 that already has a file saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

Don't understand. Are you saying there is still a problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curt" wrote in message
...
Need to save the existing workbook and relocate values for next
application
useing savecopy allows workbook to stay inplace and be updated.
Thanks much

"Ron de Bruin" wrote:

Hi Curt

Why not use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files
(*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Curt" wrote in message
...
Understand part of it. Am new to this so here goes. Have filesaveas
called
user enters a filename then save a copy as. in between I should check
to see
if file exists. Then call for a diff name if it does or reject
however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include
the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking
for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls"
on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in
message
...
I am trying to figure out how to set my macro to check a
certain
variable
and
if it meets a specific variable to skip the rest of the macro
and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the
same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the
value
of B7
that I would get a message "File Exists" but if there was no
file with
the
name as the value in B7 then it would tell me that "File Does
Not
Exist".

The code above no matter what value I have in B7 it tells me
"File
Does
Not
Exist" even if i use a value in B7 that already has a file
saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

Hi Bob

Don't understand.

Same for me

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Bob Phillips" wrote in message ...
Don't understand. Are you saying there is still a problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Curt" wrote in message
...
Need to save the existing workbook and relocate values for next
application
useing savecopy allows workbook to stay inplace and be updated.
Thanks much

"Ron de Bruin" wrote:

Hi Curt

Why not use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files
(*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Curt" wrote in message
...
Understand part of it. Am new to this so here goes. Have filesaveas
called
user enters a filename then save a copy as. in between I should check
to see
if file exists. Then call for a diff name if it does or reject
however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include
the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking
for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls"
on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in
message
...
I am trying to figure out how to set my macro to check a
certain
variable
and
if it meets a specific variable to skip the rest of the macro
and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the
same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the
value
of B7
that I would get a message "File Exists" but if there was no
file with
the
name as the value in B7 then it would tell me that "File Does
Not
Exist".

The code above no matter what value I have in B7 it tells me
"File
Does
Not
Exist" even if i use a value in B7 that already has a file
saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess

If Then Else
 

RON:
Want to thank you. A little tweeking with what I had and it works just like
I wanted it to.
Thanks Again

"Ron de Bruin" wrote:

Hi Curt

Why not use GetSaveAsFilename

Sub Test()
Dim fname As Variant
Dim Wb As Workbook
Set Wb = ActiveWorkbook

Again:
fname = Application.GetSaveAsFilename("", _
fileFilter:="Excel Files (*.xls), *.xls")
'On Error Resume Next
If fname = False Then Exit Sub
If Dir(fname) < "" Then GoTo Again
Wb.SaveCopyAs fname
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Curt" wrote in message ...
Understand part of it. Am new to this so here goes. Have filesaveas called
user enters a filename then save a copy as. in between I should check to see
if file exists. Then call for a diff name if it does or reject however. Not
sure how to do this. Any assistance greatly appreciated.
Thanks Curt

"Tom Ogilvy" wrote:

If Dir(sStr & sh.Range("B7").Value & ".xls") < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

--
Regards,
Tom Ogilvy

"ryanmhess" wrote in message
...
That may be the problem Tom however I am not sure how to include the
".xls"
with

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

to get it to work.

btw, Thanks for the Exit Sub part, was exactly what I was looking for.
Ryan Hess

"Tom Ogilvy" wrote:

could it be a problem like:

? dir("C:\data6\99budget.xls")
99budget.xls
? dir("C:\data6\99budget")

The second one returns nothing because it is missing the ".xls" on the
end.

If x = 10 then Exit sub

--
Regards,
Tom Ogilvy



"ryanmhess" wrote in message
...
I am trying to figure out how to set my macro to check a certain
variable
and
if it meets a specific variable to skip the rest of the macro and end.

If 'variable = X
Then 'skip to the end of the macro and end
Else 'continue on with the rest of the macro

I also am having trouble with some code I was given earlier.

Dim sh As Worksheet
Dim sStr As String

Set sh = ActiveSheet

sStr = "\\HCI\HCI Share\C of A's - Building 4\00-RMHTest-6
ParametersXXXX\"

If Dir(sStr & sh.Range("B7").Value) < "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If

I was told this code would check if the value in B7 was the same as
the
file
name saved in the location "\\HCI\HCI Share\C of A's - Building
4\00-RMHTest-6
ParametersXXXX\" and if there was a file named the same as the value
of B7
that I would get a message "File Exists" but if there was no file with
the
name as the value in B7 then it would tell me that "File Does Not
Exist".

The code above no matter what value I have in B7 it tells me "File
Does
Not
Exist" even if i use a value in B7 that already has a file saved as
the
value
in the location given.

Any help would be appreciated.
Thank you
Ryan Hess