IF Statement that will assign account types based on "account number"

In excel I have financial data that has "account number" (ex. 1000, 1001, etc) in column 1, "account description" (cash, a/r) in column 2 and the account balance (8,794) in column 3.

Basically I want to know how I can construct an IF statement in VBA that says IF the value in column 1 is greater than 1000 but less than 1005 THEN assign it a value of "cash" in column 4 for all of the account numbers within this range.

Any input would be greatly appreciated!

attached is the project and also how I need the spreadsheet to look like
IDS 331 Project (2).xlsm

http://i.imgur.com/zM1vhE9.png

Please Login or Register  to view this content.

Cory,

Thanks for the if statement. If i needed to continue this with the next sequence of account numbers (1006-1010) would I just repeat the IF statement using those values??

Can you show me an example of this?

Thanks again!

IDS 331 Project (2).xlsm

This way will work but can be cleaned up quite a bit once you look at it you can figure out how to make it look better and make an easier way of it to work.

Note that the macro is named InputcolD... and look at each of the if statements and you will see how the account numbers and stuff is setup you can change them accordingly...

Cory, you are a genius!!

Thanks so much man.

Wondering if I can pick your brain about one more thing. After running this macro and assigning the proper "account types" to each of the accounts, do you think it is feasible to use a SUM + Vlookup function to populate the balance sheet data on the Balance Sheet tab?

I have a user defined function in there now but its not working as desired (it requires me to input each account number separated by a comma as a delimeter)

Thanks again for your input!

Basically, I need the "Balance Sheet" tab in the workbook populated by the data in the "Data" tab. For example, in the "Balance Sheet" tab, there is a line called "Cash", I need all of the account balances labeled "Cash" that we accomplished by the IF statement to be summed onto 1 line onto the "Balance Sheet" labeled Cash. I was hoping to accomplish this using the Sum +Vlookup function in each of the "Balance Sheet" lines. Any idea on if this is the right approach or another way you would do this?

If you want take a look at my user defined function in Module 3, not sure if i was on the right track there but that proved to be incorrect cause I had to input each of the account numbers to get it to sum the values which is not flexible enough

Cory this is great stuff!

One last question, if I wanted to add the Liability accounts (ex. Payables, long term debt) should I just add those within the IF statement in the BalanceSheet Macro? Also I will need to add them as variables too correct?

Any tips on how to do this?

Thanks again, i really appreciate it!

Yes just add it the way cash is but put lisbilities or whatever else you need... Copy one of the starenents that is already there

Did you get it the way you wanted?