If Then Else

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
If Dir(sStr & sh.Range("B7").Value & ".xls") <> "" Then
MsgBox "File Exists"
Else
MsgBox "File Does Not Exist"
End If
 
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
 
How about this

Sub filesave()
Dim sFilename
Dim sNewFilename As String
Do
sFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If sFilename <> False Then
sNewFilename = Dir(sFilename)
If sNewFilename <> "" Then
MsgBox sFilename & " already Exists"
Else
ActiveWorkbook.SaveAs sFilename
End If
End If
Loop Until sFilename = False Or sNewFilename = ""
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Need to save the existing workbook and relocate values for next application
useing savecopy allows workbook to stay inplace and be updated.
Thanks much
 
Don't understand. Are you saying there is still a problem?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
RON:
Want to thank you. A little tweeking with what I had and it works just like
I wanted it to.
Thanks Again
 
Back
Top