Macro with Multiple If Statements

PosieQ

New Member
Joined
Mar 14, 2017
Messages
8
Hello,

I would like some help in creating a button macro for a spreadsheet that is essentially a user form. The form has four cells in which the user enters requested information in cells B6, B8, B10, or B12. Not all of the cells require a number. When the user clicks on the button, I would like the number or combination of numbers to appear in B27 according to the conditions listed below. Also, if the number in B27 has more than 30 characters, I would like a message box to appear informing the user that the number needs to be (manually) changed in B27.


  • If a number is in cells B6, B8, or B12 only, that number alone should appear in cell B27.
  • If numbers are in B8 and B10, both numbers should appear in B27 separated by a forward slash (i.e., B8/B10).
  • If numbers are in B6 and B10 and B8 is blank, the number in B27 should appear as B6 and B10 separated by a forward slash (i.e., B6/B10).
  • If a number is in B10, it is expected that a number should also be entered in either B6 or B8. Therefore, if a number is in B10 but not in B6 or B8, the button message will say, "You have entered only an Order number. Please enter either an appropriate Schedule No., GWAC No., IDIQ No., or BPA No, or make any other necessary corrections."
  • If a number is entered in all cells (B6, B8, B10, or B12), then button message will say, "You have entered too many numbers. Please delete the extraneous numbers."
  • If the number that shows up in B27 exceeds 30 characters, button message will say, "The contract number exceeds 30 characters. Please shorten the number by entering it directly in B27."


I know this is a bit complicated, and I hope I’ve explained it well. I appreciate any help anyone can give. Thank you.
 

PosieQ

New Member
Joined
Mar 14, 2017
Messages
8
Hello,

I am bumping this post with a few adjustments. Please let me know if I've missed providing any information. Thank you!


  • If value is in B6 only, put that value in B27.
  • If value is in B8 only, put that value in B27.
  • If value is in B12 only, put that value in B27.
  • If values are in B8 and B10, put values in B27 separated by a slash (i.e., B8/B10)
  • If values are in B6 and B10 and B8 is blank, put B6 and B10 separated by a slash in B27 (i.e., B8/B10)
  • If a value is in B10 but B6 or B8 are blank, button message should say "The Contract Number is a required field because you have entered an Order number. Please make the necessary adjustments.” Message box is "Okay".
  • If values are in B6, B8, B10 or B12, button message should say “You have entered too many contract numbers in the General Contract Information section (cells B6-B12). Please make the necessary adjustments.” Message box is "Okay".
  • If the resultant number in B27 exceeds 30 characters, button message should say "The Contract No. exceeds 30 characters. Please shorten the number by entering it directly in B27." Message box is "Okay".
 

Forum statistics

Threads
1,082,048
Messages
5,362,890
Members
400,696
Latest member
Kclynn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top