VBA IF STATEMENTS

[LaToya Phillip]

I am trying to create IF VBA statements.  FYI-I have no knowledge of writing VBA language. 

The goal is to create a template for users to use when when requesting wire transfers.  On this template there is a Transfer Funds From section and a Transfer Funds To section.  Under each section there are information that is needed in order to transfer funds.

Transfer Funds From Section

Information required are:

• Agency
• Bank Name
• Account Number

Transfer Funds To Section

Information required are:

• Beneficiary Name
• Beneficiary Address
• Receiving Bank
• ABA#
• Beneficiary Account Number

I would like that if the user enters the Agency under the Transfer Funds From Section then Bank Name and Account Number will automatically populate.  So lets say the Agency's name is entered in cell C7 then the bank name associated with the agency should populate in cell C8 and the Account number associated with the Agency should populate in cell C9.

For the Bank Name I currently have a vLookup formula.  So based on what the user entered in cell C7 the bank name would populate.  However if I am going to use VBA I would rather use IF statements.  For the account number I am currently using IF statements based on what is entered in C7 the account number is populated.  Sample formula =IF(C7="Sneaker",99999999).

Under the Transfer Funds To Section if user enters the beneficiary name then the beneficiary address, receiving bank, ABA#, and Beneficiary Account Number will automatically populate.  So lets say the beneficiary name is in cell C13 then the beneficiary address should populate in cell C14, receiving bank cell C15, ABA# cell C16, and beneficiary account number cell C17.

Note:  in some cases we would not have the beneficiary address.  I would also need the VBA language from start to finish.  

Any assistance will be greatly appreciated.

Thanks much

[#2]

Does it have to be VBA? From your description it sounds like it could all be achieved by having a table with the details in and then using more vlookups or index(match()).

[#3]

Hi Philip I have created a table and was using IF statements however there are multiple Transfer Funds to Agents and Transfer fund to Beneficiaries. I am only able to use the vlookup on the bank name field. Everywhere else I am using IF statements. My concern about using these formulas is for some reason a user may accidentally delete the formulas. I guess I wanted a better way to protect the information on the template.

[#4]

I am not familiar with the index/match.  I tried to do some research on it but was unsuccessful with making it work for my situation do to lack of knowledge.

[#5]

Hi LaToya, no worries..

It would help a little if you could upload an workbook that shows the structure of your data and what the end result should look like.

[#6]

Hi Philip 

Please see attached.

[#7 · Accepted solution]

hia,

thanks for that. I've attached what I think solves your problem.

on sheet1 i've added 2 tables, one is called tbl_to and one is called tbl_from that contains the details of the accounts.

Then on your template i've added the index match for the various rows. If you change the names you should see the account details change.

[#8]

Thank you so much Philip!!! You are the best!!! This worked perfectly!!!

[#9]

Super! Here is a tutorial that explains how index match works:

https://www.deskbright.com/excel/using-index-match/

Its a pretty useful technique, well worth learning.

[#10]

Philip,

Your workbook works great.  If I may offer two suggestions though.  The drawback to the way your data validation dropdown is referenced, is that you have to manually update the reference each time you add a row normally, by pressing Tab from the bottom right cell of the table.  You could create a named range using the table column, that you can then reference in the data validation which will not have to be updated.  If you right click in the table, and select insert row, this isn't an issue, but one of the reasons I like tables, is that you can enter info and just keep tabbing to keep going on to the next row.  I would also unlock the 2 dropdown cells, and protect the sheet.  I've probably over-analyzed this, but it comes from having to un-break forms that I've created for others.

[#11]

Hia Bob,

nope you are abolutly right, my normal preference is to use a table to hold the options and then use =indirect("tbl_name[column]") as the validation list.

I actually only added a validation list here because i made up the extra people in the example list and thought that it might help see the example work if i limited the options.

Also good idea about protecting the sheet, nothing worse then getting everything working and then someone acidently breaking everything right away :D