Add additional logic to my code

L

Little Penny

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
 
J

JLGWhiz

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

Little Penny

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






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 said:
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
Click to expand...
Click to expand...
 
C

carlo

Wouldn't you overwrite the value you set here:
..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

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

Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.
Click to expand...
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 -
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
L

Little Penny

Wouldn't you overwrite the value you set here:
.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

I hope this is alittle clearer.
Click to expand...
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
Click to expand...

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
Did you mean for one of the equal signs to be a plus sign? If not, I don't
think your logic will work.
:
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 -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select Case "Procedure to large" Error 1
File Properties 2
Textbox input goes to 2 sheets in same wbk 0
Pass condition formatting across worksheets via vba. 0
Select Case code error 8
My code need to go on a diet 10
Sheet to sheet code Type Mismatch 6
Copy row info but not in normal column sequence 6

Top