Manage If Then Statement

[Guard913]

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.

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

 

[hajiali]

can you not use Exit Sub then Else like below:

try

If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1
exit sub
Else
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1
Exit Sub
Else
......

 

[Guard913]

can you not use Exit Sub then Else like below:

try

If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1
exit sub
Else
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1
Exit Sub
Else
......

I get Compile Error:
Else without If

 

[hajiali]

what about just:

If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1: Exit sub
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1: Exit Sub

 

[Guard913]

what about just:

If Sheets("Account List").Range("A2") = "" Then Call Submit_Sub1: Exit sub
If Sheets("Account List").Range("A3") = "" Then Call Submit_Sub1: Exit Sub

Sorta works (VERY HAPPY).... Now I have a new problem which may need whole new code.... If 1 of the cells already = 1 of the lines then don't call submit_Sub1.... Cuzz now if i switch back to a previous account then click to switch to a new 1 it auto makes new row vs just letting me change accounts. If you understand what i mean...

 

[hajiali]

might need more clarification so if you have duplicate value is in column A to not call that sub?

 

[Guard913]

might need more clarification so if you have duplicate value is in column A to not call that sub? and looking at your code it seems to call submit_Sub1 for all A2 -A11

Correct there will never, and should never be any duplicates. If it Finds that a particular account exists, it should now only update the line with what ever new info is presented. 9Will mess around with Or Function while you attempt a fix

Edit: However it will only accept a max of 10 Rows as a side note.

 

[Guard913]

might need more clarification so if you have duplicate value is in column A to not call that sub?

Exactly!!

 

[hajiali]

i think this is what your looking for

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

 

[Guard913]

i think this is what your looking for

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

Goes full Red when pasted in by itself....

Compile Error: Expected Then or GoTo