Using if else if to determine file location

Needsomehelp10

New Member
Joined
Sep 5, 2017
Messages
18
Hey guys,

I am trying to use a If ElseIf to determine where to save a file. For instance if A2 equals A, it will save in one location. If A2 equals B, the file will save in another location. Below is the VBA code I have so far. It will recognize the first if statement but will not carry out the ElseIf condition. Any help would be much appreciated thanks.



Sub

Dim Filename As String
Dim FilePath As String
If Range("A2") = "A" Then
Filename = ActiveSheet.Name & "December"
FilePath = "Insert File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
ElseIf Range("A2") = "B" Then
Filename = ActiveSheet.Name & "December"
FilePath = "Insert Alternative File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
EndIf
End Sub
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
Other than the fact that you do not have a name for your procedure, there is nothing wrong with the structure of the code.

My guess is that the values you are checking for may not really match. Note that something as simple as a single space on the end will cause them not to match, i.e. "B " does not equal "B".
 

Needsomehelp10

New Member
Joined
Sep 5, 2017
Messages
18
Joe4,

I figured out what I was doing wrong, it was a simple mistake. I do have an additional question though. As of now, I have have the files being saved under the name Filename = ActiveSheet.Name & " TB - (10) October 2017". I want to define some variables so that I only have to change the month one time instead of having to go onto each if and else if statements and changing them. I have tried this but it is not working properly. I am new to this so I am probably just making a fairly simple fixable mistake. Thanks

Subfilelocation ()

Dim Filename As String
Dim FilePath As String
Dim Year As Integer
Dim Month As String
Month = "(10) October"
Year = 2017
If Range("A2") = "A" Then
Filename = ActiveSheet.Name & " TB - " & "Month" & "Year"
FilePath = "Insert File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
ElseIf Range("A2") = "B" Then
Filename = ActiveSheet.Name & " TB - " & "Month" & "Year"
FilePath = "Insert Alternative File Path Here"
ActiveSheet.SaveAs Filename:=FilePath & "" & Filename, FileFormat:=51
EndIf
End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,975
Office Version
  1. 365
Platform
  1. Windows
Here is the important thing to remember:
- anything enclosed in double-quotes is treated as literal text
- variables should never be enclosed in double-quotes

This little test will demonstrate this perfectly:
Code:
Sub Test()
    Dim mnth As String
    mnth = "October"
    MsgBox "Value returned with mnth in double-quotes: " & "mnth"
    MsgBox "Value returned with mnth NOT in double-quotes: " & mnth
End Sub
One other tip: Never choose reserved words (the name of functions, properties, objects, etc) as the name of your variables. It can cause ambiguity, unexpected results, and errors.
Since "Year" and "Month" are also the name of functions, choose different names (like I did above).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top