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

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

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
<(E-Mail Removed)> 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").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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>

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

On Dec 4, 10:44 am, Little Penny <LittlePenn...@gmail.com> 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
>
>
>
> <JLGW...@discussions.microsoft.com> 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").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 -

On Dec 3, 9:56 pm, carlo <carlo.ramu...@gmail.com> wrote:
> 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
>
> On Dec 4, 10:44 am, Little Penny <LittlePenn...@gmail.com> 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
>
> > <JLGW...@discussions.microsoft.com> 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").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...

Thanks carlo I got it.....