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.

	A	B	C	D	E	F	G	H	I
1			Revenue						3000-CBD-DSF
2			Revenue						3000-SDT-BGC
3			Expense						5000-GTD-CCF
4			Expense						6000-HTD-VCS

<tbody>
</tbody>

 

[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