Excel VBA help

MCAExcel

New Member
Joined
Feb 8, 2019
Messages
2
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.

ABCDEFGHI
1Revenue3000-CBD-DSF
2Revenue3000-SDT-BGC
3Expense5000-GTD-CCF
4Expense6000-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
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 8, 2019
Messages
2
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
Sheets("Upload").Select
Range("E2:E3000").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sheets("Start").Range("C2").Copy
With Sheets("Upload")
   Range("E2", .Range("B" & Rows.Count).End(xlUp).Offset(, 3)).PasteSpecial xlPasteAll
End With
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top