ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Add additional logic to my code (https://www.excelbanter.com/excel-programming/402116-add-additional-logic-my-code.html)

Little Penny[_2_]
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

JLGWhiz
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

Little Penny[_3_]
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

carlo
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 -

Little Penny[_2_]
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...

Little Penny[_3_]
Add additional logic to my code
 
Thanks carlo I got it.....


All times are GMT +1. The time now is 10:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com