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 -