need help with an if statement

[Zygoid]

right now i am using this code on a command button;

Sub Save_As_FileName()

FName1 = Range("d3").Value
FName2 = Range("d5").Value
Fname3 = Range("d6").Value
Fname4 = Range("d7").Value

pth = "f:\bids\"

MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname
& " " & ".xls"
ActiveWorkbook.SaveAs Filename:=pth & MyFileName

End Sub

But the pth will change, depending on what is in cell d2.

so, I think i will need an If statement saying

if cell d2 = gjg, then the path will be f:\bids\gjg
or
if cell d2 = bdg, then the path will be f:\bids\bdg

I have tried this;

With pth
If (Range("D2")) Is "gjg" Then
pth = "f:\bids\gjg"
If Not IsEmpty(.Value) Then
If (Range("D2")) Is "bdg" Then
pth = "f:\bids\bdg"
If Not IsEmpty(.Value) Then
If (Range("D2")) Is "ear" Then
pth = "f:\bids\ear"
If Not IsEmpty(.Value) Then
If (Range("D2")) Is "jpr" Then
pth = "f:\bids\jpr"
End If
End With

could someone help

 

[AlfD]

Hi!

Probably short of a \ at the end of the pth.

Al

 

[Zygoid]

Yes, I see what ya mean. but I am still getting a

compile error: type mismatch for "gjg"

even though gjg is the text written in cell d

 

[AlfD]

Hi!

And how about "=" instead of "Is"?

Al

 

[Zygoid]

using the = fixed it.
but instead of saving to f:\bids\gjg\ folder, it is only saving it t
F:

btw, thanks !

 

[Zygoid]

I got it.

the final code ended up being;

Sub Save_As_FileName()

FName1 = Range("d3").Value
FName2 = Range("d5").Value
Fname3 = Range("d6").Value
Fname4 = Range("d7").Value

With pth
If (Range("D2")) = "gjg" Then
pth = "f:\Bids\GJG\"
End If
If (Range("d2")) = "bdg" Then
pth = "f:\Bids\BDG\"
End If
If (Range("d2")) = "ear" Then
pth = "f:\Bids\EAR\"
End If
If (Range("d2")) = "jpr" Then
pth = "f:\Bids\JPR\"
End If

End With

MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname4 & "
& ".xls"
ActiveWorkbook.SaveAs Filename:=pth & MyFileName
End Sub


Thanks again for your help!!


now, onto the next head dizzying task. :