Manage If Then Statement

Guard913

Board Regular
Joined
Apr 10, 2016
Messages
144
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
This is My Code:
(Submit_Sub1 - Finds next empty row and fills it)
(Account#_Update - Updates the row that matches Cell Info)
What I am looking for:
Works fine if I only have "If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1" but what I am looking for it to do, is if A2 has an item it checks A3 if A3 is blank it will Fill it and then exit this section and move to code
"If Call_Form.TextBox8.Value = Sheets("Account List").Range("A2") Then Call Account1_Update" and check info against that. Eventually reaching bottom and Ending.

The idea is everytime I click on button to switch accounts it will update the correct line with what ever info is currently in my lists, and if no line has been made it will make a new line, update it then allow me to select an account.

Right now on my form I have a Submit Button and an Update button... Before I can click combobox which has a dropbuttonclick code which pulls the list. I just want the dropbuttoncode to handle all of it so I don't even have to think about it.


VBA Code:
Sub Full_Update()
If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A4") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A5") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A6") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A7") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A8") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A9") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A10") = "" Then Call Submit_Sub1
If Sheets("Account List").Range("A11") = "" Then Call Submit_Sub1
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A2") Then Call Account1_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A3") Then Call Account2_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A4") Then Call Account3_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A5") Then Call Account4_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A6") Then Call Account5_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A7") Then Call Account6_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A8") Then Call Account7_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A9") Then Call Account8_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A10") Then Call Account9_Update
If Call_Form.TextBox8.Value = Sheets("Account List").Range("A11") Then Call Account10_Update

End Sub
 
sorry for got the "or"

try

VBA Code:
If Sheets("Account List").Range("A2") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then Call Submit_Sub1: Exit sub
If Sheets("Account List").Range("A3") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then Call Submit_Sub1: Exit Sub
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
sorry for got the "or"

try

VBA Code:
If Sheets("Account List").Range("A2") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then Call Submit_Sub1: Exit sub
If Sheets("Account List").Range("A3") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then Call Submit_Sub1: Exit Sub

Well it didn't error out as soon as I put code in, but it did error after i tried to run it...
 
Upvote 0
VBA Code:
If Sheets("Account List").Range("A2") = Call_Form.TextBox8.Value Then: Exit Sub
If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1: Exit Sub

If Sheets("Account List").Range("A3") = Call_Form.TextBox8.Value Then: Exit Sub
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1: Exit Sub

If Sheets("Account List").Range("A4") = Call_Form.TextBox8.Value Then: Exit Sub
If Sheets("Account List").Range("A4") = "" Then Call Submit_Sub1: Exit Sub

If Sheets("Account List").Range("A5") = Call_Form.TextBox8.Value Then: Exit Sub
If Sheets("Account List").Range("A5") = "" Then Call Submit_Sub1: Exit Sub
.....

Created this for each set which seems to work....
 
Upvote 0
add this

VBA Code:
Sub Full_Update()
Dim ws As Worksheet
Set ws = Worksheets("Account List")

If ws.Range("A2") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then Call Submit_Sub1: Exit sub
If ws.Range("A3") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then Call Submit_Sub1: Exit Sub

I used ws as worksheet to reduce the length of the formula
 
Upvote 0
add this

VBA Code:
Sub Full_Update()
Dim ws As Worksheet
Set ws = Worksheets("Account List")

If ws.Range("A2") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then Call Submit_Sub1: Exit sub
If ws.Range("A3") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then Call Submit_Sub1: Exit Sub

I used ws as worksheet to reduce the length of the formula
hm.... still testing but if it works much nicer than my code :D
 
Upvote 0
add this

VBA Code:
Sub Full_Update()
Dim ws As Worksheet
Set ws = Worksheets("Account List")

If ws.Range("A2") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then Call Submit_Sub1: Exit sub
If ws.Range("A3") = "" or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then Call Submit_Sub1: Exit Sub

I used ws as worksheet to reduce the length of the formula
Sadly it did not work..... Compiled properly but still ran the macro (Submit_Sub1)
 
Upvote 0
so if value in A2 is not "" and its not duplicate it ran the Submit_Sub1?

for it to run sub1 value column A must be blank or duplicate. is what post 14 has.

did you need it to do something different?
 
Upvote 0
so if value in A2 is not "" and its not duplicate it ran the Submit_Sub1?

for it to run sub1 value column A must be blank or duplicate. is what post 14 has.

did you need it to do something different?
Start out working fine, but the after adding like 3rd 4th account when i starting clicking it it kept duplicating the account.

If A2 doesn't = A2-A11 or it does = Blank >Then Run Submit Otherwise Exit Sub
If A3 Doesn't = A2-A11 or it does = Blank >Then Run Submit Otherwise Exit Sub
If A4 Doesn't = A2-A11 or it does = Blank >Then Run Submit Otherwise Exit Sub
all the way to A11
 
Upvote 0
Exit Sub ends the macro if you need it to run all the way use end if instead of exit sub

like
VBA Code:
If ws.Range("A2") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then
Call Submit_Sub1
End If
If ws.Range("A3") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then
Call Submit_Sub1
End If

if you need to end macro when it runs sub use:

VBA Code:
If ws.Range("A2") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then
Call Submit_Sub1
exit sub
End If
If ws.Range("A3") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then
Call Submit_Sub1
exit sub
End If
 
Upvote 0
if you need to end macro when it runs sub use:

VBA Code:
If ws.Range("A2") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A2")) > 1 Then
Call Submit_Sub1
exit sub
End If
If ws.Range("A3") = "" Or Application.WorksheetFunction.CountIf(ws.Range("A2:A11"), ws.Range("A3")) > 1 Then
Call Submit_Sub1
exit sub
End If
Sorta works... still tries to duplicate it though from time to time.... but i may have a work around for that....
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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
Back
Top