Excel VBA help

[MCAExcel]

Hi, I am not sure how to accomplish the following, hope you can help. I have a spreadsheet named "Upload" and on column "I" I have a string of data (Variable rows). I want to look at the first 4 numbers on that string and if it's greater than or = 4000, then type in cell on the left "Expense" or else "Revenue". I need this to loop through till the last value of column I and keep matching and writing to column C.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3000-CBD-DSF[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3000-SDT-BGC[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Expense[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5000-GTD-CCF[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Expense[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6000-HTD-VCS[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
You description & sample data don't match up. This is based on you sample.

Sub MCAExcel()
   With Range("C2", Range("I" & Rows.Count).End(xlUp).Offset(, -6))
      .Value = Evaluate(Replace("if(left(@,4)*1<=4000,""Revenue"",""Expense"")", "@", .Offset(, 6).Address))
   End With
End Sub

Alternatively you could just put this formula in C2 & copy down
=IF(LEFT(I2,4)*1<=4000,"Revenue","Expense")

 

[MCAExcel]

Hi & welcome to MrExcel.
You description & sample data don't match up. This is based on you sample.

Sub MCAExcel()
   With Range("C2", Range("I" & Rows.Count).End(xlUp).Offset(, -6))
      .Value = Evaluate(Replace("if(left(@,4)*1<=4000,""Revenue"",""Expense"")", "@", .Offset(, 6).Address))
   End With
End Sub

Alternatively you could just put this formula in C2 & copy down
=IF(LEFT(I2,4)*1<=4000,"Revenue","Expense")

I will give it a try now. Thank you for the fast response. One more question if you don't mind. I have the below in VBA. There's probably a more elegant way to use xlUp and stop rather than hardcode the cell E2:E3000. The column B in "Upload" is what I would like to use as refence to count the end of row. Hope this makes sense.

Sheets("Start").Select
Sheets("Start").Range("C2").Select
Selection.Copy
Sheets("Upload").Select
Range("E2:E3000").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Fluff]

How about

Sheets("Start").Range("C2").Copy
With Sheets("Upload")
   Range("E2", .Range("B" & Rows.Count).End(xlUp).Offset(, 3)).PasteSpecial xlPasteAll
End With