# Excel VBA help

#### MCAExcel

##### New Member
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>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
You description & sample data don't match up. This is based on you sample.
Code:
``````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

##### New Member
Hi & welcome to MrExcel.
You description & sample data don't match up. This is based on you sample.
Code:
``````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
Range("E2:E3000").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````Sheets("Start").Range("C2").Copy
Range("E2", .Range("B" & Rows.Count).End(xlUp).Offset(, 3)).PasteSpecial xlPasteAll
End With``````

Replies
1
Views
441
Replies
0
Views
95
Replies
2
Views
68
Replies
2
Views
93
Replies
2
Views
99

1,130,303
Messages
5,641,426
Members
417,209
Latest member
Agbarker

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back