IF Statement

  • Thread starter Thread starter alexm999
  • Start date Start date
A

alexm999

What code can I add to my MAcro that will tell my macro the following:

If im in the JUMP worksheet, then use the file from the c:\UDC\10
director
 
Dim fName As String
If ActiveSheet.Name = "JUMP" Then
Workbooks.Open "c:\UDC\102\" & fName
End If

where fName is the filename. This assumes you want to open the file, since I
don't know what you mean by "use".
 
since the post is pretty sparse on what you'd like to be doing with th
data in UDC\102, i'll just start you with code to change to tha
directory.

'begin code

If activesheet.name = "Jump" then
Folder = "c:\UDC\102"
End if

ChDir (Folder)

'end code


hope that gets you started
 
Seeing other post, probably batter to use Case

Select Case ActiveSheet.Name
Case "JUMP": sDir = "C:\UDC\102\"
Case "STAND": sDir = "C:\UDC\103\"
etc.
End Case

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Vasant Nanavati said:
Dim fName As String
If ActiveSheet.Name = "JUMP" Then
Workbooks.Open "c:\UDC\102\" & fName
End If

where fName is the filename. This assumes you want to open the file, since I
don't know what you mean by "use".
Click to expand...
 
here's my Code.... Please look it over... Let me know how i can clean i
up a bit....

I will duplicate this macro to look for other worksheets and then go t
other directories to access files...


Sub Macro1()
Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
End If
Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False
Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1)
Array(2, 1), Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)
Array(8, 1))
If Range("a:a").Find(what:="DEV") Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If
Windows("1.txt").Activate
Range("E62").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("AL9").Select
ActiveSheet.Paste
Range("AM9").Select
Windows("1.txt").Activate
Range("I62").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("1.txt").Activate
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C9").Select
ActiveSheet.Paste
Range("E9").Select
Windows("1.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J9").Select
Windows("1.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K9").Select
Windows("1.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI9").Select
Windows("1.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("1.TXT").Activate
ActiveWindow.Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
End Su
 
Alex,

Here is one way.

I have also shortened your code, although I admit to getting lost as to
where it was being copied to, so if you use it, check all the destinations
and correct them.

Sub Test()

Select Case ActiveSheet.Name
Case "101-JAN04": myMacro "c:\UDC\101\"
End If
End Sub
Sub myMacro(sh As String)
Application.DisplayAlerts = False
Workbooks.OpenText Filename:=sh & "1.TXT", _
Origin:=xlWindows, _
StartRow:=7, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3,
1), _
Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1))
If Range("A:A").Find(what:="DEV") Is Nothing Then
Range("A16").EntireRow.Insert shift:=xlDown
End If
Windows("1.txt").Range("E62").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("AL9")
Windows("1.txt").Range("I62").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.txt").Range("F13").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.TXT").Range("F14").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.TXT").Range("E17").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.TXT").Range("G18").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.TXT").Range("F18").Copy Destination:= _
Windows("DAILY OPERATIONS_2004.xls").Range("C9")
Windows("1.TXT").Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Here's the code... Any ideas?
I have the following directories:

C:\UDC\101
C:\UDC\102
C:\UDC\103
C:\UDC\104
C:\UDC\105

the sheets are 101-JAN04,102-JAN04 and so on to 105-JAN04



Sub Macro1()
Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
End If
Workbooks.OpenText Filename:="c:\UDC\101\1.TXT", Origin:=xlWindows
StartRow _
:=7, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False
_
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)
Array(3 _
, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1))
If Range("a:a").Find(what:="DEV") Is Nothing Then
Range("a16").EntireRow.Insert shift:=xlDown
End If
Windows("1.txt").Activate
Range("E62").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("AL9").Select
ActiveSheet.Paste
Range("AM9").Select
Windows("1.txt").Activate
Range("I62").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("1.txt").Activate
Range("F13").Select
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
Range("C9").Select
ActiveSheet.Paste
Range("E9").Select
Windows("1.TXT").Activate
Range("F14").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("J9").Select
Windows("1.TXT").Activate
Range("E17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Range("K9").Select
Windows("1.TXT").Activate
Range("G18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=2
Range("AI9").Select
Windows("1.TXT").Activate
Range("F18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DAILY OPERATIONS_2004.xls").Activate
ActiveSheet.Paste
Windows("1.TXT").Activate
ActiveWindow.Close
ActiveWindow.LargeScroll ToRight:=-2
ActiveWindow.ScrollColumn = 1
Application.DisplayAlerts = True
End Su
 
Alex,

As before, but with this calling routine
Sub Test()

Select Case ActiveSheet.Name
Case "101-JAN04": myMacro "c:\UDC\101\"
Case "102-JAN04": myMacro "c:\UDC\102\"
Case "103-JAN04": myMacro "c:\UDC\103\"
Case "104-JAN04": myMacro "c:\UDC\104\"
Case "105-JAN04": myMacro "c:\UDC\105\"
End If
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sub Macro1()
Dim rng As Range, sh As Worksheet
Dim sh2 As Worksheet, sFile As String
Dim Folder As String

' sFile just demo's that you could set other activesheet
' dependent data.

Application.DisplayAlerts = False
If ActiveSheet.Name = "101-JAN04" Then
Folder = "c:\UDC\101\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "102-JAN04"
Folder = "c:\UDC\102\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "103-JAN04"
Folder = "c:\UDC\103\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Name = "104-JAN04"
Folder = "c:\UDC\104\"
sFile = "DAILY OPERATIONS_2004.xls"
Elseif Activesheet.Nmae = "105-JAN04"
Folder = "c:\UDC\105\"
sFile = "DAILY OPERATIONS_2004.xls"
End If

Workbooks.OpenText FileName:=Folder & "1.TXT", _
Origin:=xlWindows, _
StartRow:=7, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), _
Array(6, 1), Array(7, 1), _
Array(8, 1))

Set sh = ActiveSheet

Set rng = sh.Range("a:a").Find(what:="DEV")

If rng Is Nothing Then
sh.Range("a16").EntireRow.Insert shift:=xlDown
Exit Sub
End If

Set sh2 = Windows(sFile).ActiveSheet
sh.Range("E62").Copy Destination:=sh2.Range("AL9")
sh.Range("I62").Copy Destination:=sh2.Range("AM9")
sh.Range("F13").Copy Destination:=sh2.Range("C9")
sh.Range("F14").Copy Destination:=sh2.Range("E9")
sh.Range("E17").Copy Destination:=sh2.Range("J9")
sh.Range("G18").Copy Destination:=sh2.Range("K9")
sh.Range("F18").Copy Destination:=sh2.Range("AI9")
Workbooks("1.Text").Close

Application.DisplayAlerts = True
End Sub
 
Back
Top