Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > IF Statement
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default IF Statement
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,080
Default IF Statement
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".

--

Vasant



"alexm999 " wrote in message
...
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\102
directory


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default IF Statement
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" <vasantn *AT* aol *DOT* com wrote in message
...
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".

--

Vasant



"alexm999 " wrote in message
...
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\102
directory


---
Message posted from http://www.ExcelForum.com/





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default IF Statement
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default IF Statement
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)

"alexm999 " wrote in message
...
here's my Code.... Please look it over... Let me know how i can clean it
up a bit....

I will duplicate this macro to look for other worksheets and then go to
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 Sub


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default IF Statement
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default IF Statement
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

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
IF statement inside a SUMIF statement.... or alternative method Sungibungi Excel Worksheet Functions 3 December 4th 09 06:22 PM
Reconcile Bank statement & Credit card statement & accounting data Bklynhyc Excel Worksheet Functions 0 October 7th 09 09:07 PM
Embedding an OR statement in an IF statement efficiently Chatnoir11 Excel Discussion (Misc queries) 4 February 2nd 09 08:12 PM
appending and IF statement to an existing IF statement spence Excel Worksheet Functions 1 February 28th 06 11:00 PM
Help please, IF statement/SUMIF statement Brad_A Excel Worksheet Functions 23 January 11th 05 02:24 PM


All times are GMT +1. The time now is 05:20 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement