Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Add additional logic to my code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default Add additional logic to my code
Is I'm trying to add the logic to my code below

If the first three characters in .Offset(i, 1).Value = Left(FirstLine,
8) ="B85" then

..Offset(i, 3).Value = "Place1"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place2"


or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place3"


or If the first two characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="FC" then

..Offset(i, 3).Value = "Place4"


or If the first character in .Offset(i, 1).Value = Left(FirstLine, 8)
="X" then

..Offset(i, 3).Value = "Place23




My Entire Code...


Sub GetData4Export()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long


Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 42



Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("D:\Test\")
Set fc = f.Files

i = 0

With Res

For Each fl In fc

If UCase(Right(fl.Path, 4)) = ".IDF" Then

fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)

Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1
.Offset(i, 0).Value = "M"
.Offset(i, 1).Value = Left(FirstLine, 8)
.Offset(i, 2).Value = Left(FirstLine, 8)
.Offset(i, 3).Value = "UBS MONTH END NON-PREMIER"
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"
.Offset(i, 5).Value = Mid(ln, 9, 6)
.Offset(i, 5).NumberFormat = "000000"
.Offset(i, 6).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
.Offset(i, 6).NumberFormat = "0"

i = i + 1
End If
Next fl
.Offset(0, 8).EntireColumn.AutoFit
End With





Range("A1").Select

End Sub










Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,986
Default Add additional logic to my code
Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.

"Little Penny" wrote:

Is I'm trying to add the logic to my code below

If the first three characters in .Offset(i, 1).Value = Left(FirstLine,
8) ="B85" then

..Offset(i, 3).Value = "Place1"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place2"


or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place3"


or If the first two characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="FC" then

..Offset(i, 3).Value = "Place4"


or If the first character in .Offset(i, 1).Value = Left(FirstLine, 8)
="X" then

..Offset(i, 3).Value = "Place23




My Entire Code...


Sub GetData4Export()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long


Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 42



Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("D:\Test\")
Set fc = f.Files

i = 0

With Res

For Each fl In fc

If UCase(Right(fl.Path, 4)) = ".IDF" Then

fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)

Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1
.Offset(i, 0).Value = "M"
.Offset(i, 1).Value = Left(FirstLine, 8)
.Offset(i, 2).Value = Left(FirstLine, 8)
.Offset(i, 3).Value = "UBS MONTH END NON-PREMIER"
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"
.Offset(i, 5).Value = Mid(ln, 9, 6)
.Offset(i, 5).NumberFormat = "000000"
.Offset(i, 6).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
.Offset(i, 6).NumberFormat = "0"

i = i + 1
End If
Next fl
.Offset(0, 8).EntireColumn.AutoFit
End With





Range("A1").Select

End Sub











Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default Add additional logic to my code

I hope this is alittle clearer.

After or during the time my code is running I want to put a value in
column D based on the first 2 or sometimes 3 characters in column.
If the first 2 or 3 characters in column B begins with:
"CVR" than put East Coast in Colum D
"XC" then put Overseas in column D
"B85" then put Green Office in column D
"RC" then put Blue Office in column D


Example


A B C D FT78u
CVR10001 CVR10001 EAST COAST 000000 000000 46525
FT78u XC120002 XC120002 OVERSEAS 000000 000000 52415
FT78u B85CVRT B85CVRT GREEN OFFICE 000000 000000 22844
FT78u RCJJ747 RCJJ747 BLUE OFFICE 000000 000000 22844






On Mon, 3 Dec 2007 09:28:03 -0800, JLGWhiz
wrote:

Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.

"Little Penny" wrote:

Is I'm trying to add the logic to my code below

If the first three characters in .Offset(i, 1).Value = Left(FirstLine,
8) ="B85" then

..Offset(i, 3).Value = "Place1"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place2"


or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place3"


or If the first two characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="FC" then

..Offset(i, 3).Value = "Place4"


or If the first character in .Offset(i, 1).Value = Left(FirstLine, 8)
="X" then

..Offset(i, 3).Value = "Place23




My Entire Code...


Sub GetData4Export()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long


Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 42



Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("D:\Test\")
Set fc = f.Files

i = 0

With Res

For Each fl In fc

If UCase(Right(fl.Path, 4)) = ".IDF" Then

fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)

Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1
.Offset(i, 0).Value = "M"
.Offset(i, 1).Value = Left(FirstLine, 8)
.Offset(i, 2).Value = Left(FirstLine, 8)
.Offset(i, 3).Value = "UBS MONTH END NON-PREMIER"
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"
.Offset(i, 5).Value = Mid(ln, 9, 6)
.Offset(i, 5).NumberFormat = "000000"
.Offset(i, 6).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
.Offset(i, 6).NumberFormat = "0"

i = i + 1
End If
Next fl
.Offset(0, 8).EntireColumn.AutoFit
End With





Range("A1").Select

End Sub











Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 367
Default Add additional logic to my code
Wouldn't you overwrite the value you set he
..Offset(i, 4).Value = Mid(FirstLine, 9, 6)
..Offset(i, 4).NumberFormat = "000000"

as i see it, this will be set in column d, right?

otherwise you could use a select case statement

select case left(.offset(i,2),2)
case is = "CV"
'do your CV thing
case is = "XC"
'do your XC thing
end select

hth Carlo

On Dec 4, 10:44 am, Little Penny wrote:
I hope this is alittle clearer.

After or during the time my code is running I want to put a value in
column D based on the first 2 or sometimes 3 characters in column.
If the first 2 or 3 characters in column B begins with:
"CVR" than put East Coast in Colum D
"XC" then put Overseas in column D
"B85" then put Green Office in column D
"RC" then put Blue Office in column D

Example

A B C D FT78u
CVR10001 CVR10001 EAST COAST 000000 000000 46525
FT78u XC120002 XC120002 OVERSEAS 000000 000000 52415
FT78u B85CVRT B85CVRT GREEN OFFICE 000000 000000 22844
FT78u RCJJ747 RCJJ747 BLUE OFFICE 000000 000000 22844

On Mon, 3 Dec 2007 09:28:03 -0800, JLGWhiz



wrote:
Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.

"Little Penny" wrote:

Is I'm trying to add the logic to my code below

If the first three characters in .Offset(i, 1).Value = Left(FirstLine,
8) ="B85" then

..Offset(i, 3).Value = "Place1"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place2"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place3"

or If the first two characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="FC" then

..Offset(i, 3).Value = "Place4"

or If the first character in .Offset(i, 1).Value = Left(FirstLine, 8)
="X" then

..Offset(i, 3).Value = "Place23

My Entire Code...

Sub GetData4Export()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long

Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 42

Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("D:\Test\")
Set fc = f.Files

i = 0

With Res

For Each fl In fc

If UCase(Right(fl.Path, 4)) = ".IDF" Then

fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)

Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1
.Offset(i, 0).Value = "M"
.Offset(i, 1).Value = Left(FirstLine, 8)
.Offset(i, 2).Value = Left(FirstLine, 8)
.Offset(i, 3).Value = "UBS MONTH END NON-PREMIER"
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"
.Offset(i, 5).Value = Mid(ln, 9, 6)
.Offset(i, 5).NumberFormat = "000000"
.Offset(i, 6).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
.Offset(i, 6).NumberFormat = "0"

i = i + 1
End If
Next fl
.Offset(0, 8).EntireColumn.AutoFit
End With

Range("A1").Select

End Sub- Hide quoted text -

- Show quoted text -

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default Add additional logic to my code
On Dec 3, 9:56 pm, carlo wrote:
Wouldn't you overwrite the value you set he
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"

as i see it, this will be set in column d, right?

otherwise you could use a select case statement

select case left(.offset(i,2),2)
case is = "CV"
'do your CV thing
case is = "XC"
'do your XC thing
end select

hth Carlo

On Dec 4, 10:44 am, Little Penny wrote:



I hope this is alittle clearer.

After or during the time my code is running I want to put a value in
column D based on the first 2 or sometimes 3 characters in column.
If the first 2 or 3 characters in column B begins with:
"CVR" than put East Coast in Colum D
"XC" then put Overseas in column D
"B85" then put Green Office in column D
"RC" then put Blue Office in column D

Example

A B C D FT78u
CVR10001 CVR10001 EAST COAST 000000 000000 46525
FT78u XC120002 XC120002 OVERSEAS 000000 000000 52415
FT78u B85CVRT B85CVRT GREEN OFFICE 000000 000000 22844
FT78u RCJJ747 RCJJ747 BLUE OFFICE 000000 000000 22844

On Mon, 3 Dec 2007 09:28:03 -0800, JLGWhiz

wrote:
Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.

"Little Penny" wrote:

Is I'm trying to add the logic to my code below

If the first three characters in .Offset(i, 1).Value = Left(FirstLine,
8) ="B85" then

..Offset(i, 3).Value = "Place1"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place2"

or If the first three characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="B81" then

..Offset(i, 3).Value = "Place3"

or If the first two characters in .Offset(i, 1).Value =
Left(FirstLine, 8) ="FC" then

..Offset(i, 3).Value = "Place4"

or If the first character in .Offset(i, 1).Value = Left(FirstLine, 8)
="X" then

..Offset(i, 3).Value = "Place23

My Entire Code...

Sub GetData4Export()
Dim fn As String
Dim ln As String
Dim FirstLine As String
Dim Res As Range
Dim fs, f, fl, fc, s
Dim i As Long

Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 11
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 42

Set Res = Range("A1") 'upper left corner of Result range

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder("D:\Test\")
Set fc = f.Files

i = 0

With Res

For Each fl In fc

If UCase(Right(fl.Path, 4)) = ".IDF" Then

fn = fl.Path
FirstLine = ""
Open fn For Input As #1
Do While Not EOF(1)

Input #1, ln
If FirstLine = "" Then FirstLine = ln
Loop
Close #1
.Offset(i, 0).Value = "M"
.Offset(i, 1).Value = Left(FirstLine, 8)
.Offset(i, 2).Value = Left(FirstLine, 8)
.Offset(i, 3).Value = "UBS MONTH END NON-PREMIER"
.Offset(i, 4).Value = Mid(FirstLine, 9, 6)
.Offset(i, 4).NumberFormat = "000000"
.Offset(i, 5).Value = Mid(ln, 9, 6)
.Offset(i, 5).NumberFormat = "000000"
.Offset(i, 6).FormulaR1C1 = "=RC[-1]-RC[-2]+1"
.Offset(i, 6).NumberFormat = "0"

i = i + 1
End If
Next fl
.Offset(0, 8).EntireColumn.AutoFit
End With

Range("A1").Select

End Sub- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I'm sorry I did not specifiy. I want to put the value in (.Offset(i,
3).Value) Which is column "B"
I'm going to take that line of code out...




Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 48
Default Add additional logic to my code
Thanks carlo I got it.....
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear 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
How to create one additional warning flag into my logic? Danny Boy Excel Worksheet Functions 1 June 11th 09 08:51 AM
add additional code to code Marilyn Excel Discussion (Misc queries) 4 December 16th 08 01:26 PM
Logic and Loop Code oscarooko[_4_] Excel Programming 1 October 19th 05 04:23 PM
Code logic error [email protected] Excel Programming 1 September 4th 05 03:45 AM
Additional feature to code given on August 6th Metallo[_3_] Excel Programming 3 August 20th 04 10:31 AM


All times are GMT +1. The time now is 09:53 AM.

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