If statement stopping after first true value

Larcen60

New Member
Joined
Sep 12, 2017
Messages
18
I have a User Form where a user can select multiple values where they want to enter a person's name onto the spreadsheet. The macro should be looping through all 4 potential selections and placing the name in the "D" Column based upon conditions set in columns "O" and "P". However, my loop is stopping after it enters the first person's name. I believe it's because once the first "True" statement is found, it stops evaluating the rest of the statement. I've searched on the internet, but I'm having trouble figuring out a better way to complete this. Any help would be greatly appreciated, thanks!

VBA Code:
Private Sub btnAddToScoreSheet_Click()

Dim RelayNumber1 As Long, RelayNumber2, RelayNumber3, RelayNumber4
Dim PostNumber1 As Long, PostNumber2, PostNumber3, PostNumber4
Dim SquadNumber1 As Long, SquadNumber2, SquadNumber3, SquadNumber4
Dim PlayerName As String
Dim WS As Worksheet
Dim LastRow As Long

SquadNumber1 = Me.txtSquad1.Value
SquadNumber2 = Me.txtSquad2.Value
SquadNumber3 = Me.txtSquad3.Value
SquadNumber4 = Me.txtSquad4.Value

RelayNumber1 = Me.txtRelay1.Value
RelayNumber2 = Me.txtRelay2.Value
RelayNumber3 = Me.txtRelay3.Value
RelayNumber4 = Me.txtRelay4.Value

PostNumber1 = Me.txtPost1.Value
PostNumber2 = Me.txtPost2.Value
PostNumber3 = Me.txtPost3.Value
PostNumber4 = Me.txtPost4.Value

PlayerName = Me.txtPlayerName.Value


Application.ScreenUpdating = False
 
Set WS = ActiveWorkbook.Worksheets("Score Sheet")
LastRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
 For r = 2 To LastRow

If WS.Cells(r, 15) = SquadNumber1 And WS.Cells(r, 16) = PostNumber1 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad1.Value = ""
    Me.txtPost1.Value = ""
    Me.txtRelay1.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber2 And WS.Cells(r, 16) = PostNumber2 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad2.Value = ""
    Me.txtPost2.Value = ""
    Me.txtRelay2.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber3 And WS.Cells(r, 16) = PostNumber3 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad3.Value = ""
    Me.txtPost3.Value = ""
    Me.txtRelay3.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber4 And WS.Cells(r, 16) = PostNumber4 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad4.Value = ""
    Me.txtPost4.Value = ""
    Me.txtRelay4.Value = ""
    
     End If

Next r
 

MsgBox "Added all squads to sheet"
Application.ScreenUpdating = True
 
Unload Me

End Sub

Sample4.png
Sample5.png
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,643
Office Version
  1. 2010
Platform
  1. Windows
Try turning off the worksheet events by adding this code where you have got the screenupdating code:
VBA Code:
 Application.ScreenUpdating = False
 Application.EnableEvents = False
'' rest of code
Application.ScreenUpdating = True
 Application.EnableEvents = True
'
 

Larcen60

New Member
Joined
Sep 12, 2017
Messages
18
Thanks for the suggestion! Unfortunately, that didn't work and I still only get the first value being written to the spreadsheet and the macro ignores the second value.
 

Larcen60

New Member
Joined
Sep 12, 2017
Messages
18
Thanks everyone for taking the time to look at this, the solution is that each variable needs to be declared individually to work. Here's the code that works in case anyone ever comes up with this problem again:

VBA Code:
Private Sub btnAddToScoreSheet_Click()

Dim RelayNumber1 As String
Dim RelayNumber2 As String
Dim RelayNumber3 As String
Dim RelayNumber4 As String
Dim PostNumber1 As String
Dim PostNumber2 As String
Dim PostNumber3 As String
Dim PostNumber4 As String
Dim SquadNumber1 As String
Dim SquadNumber2 As String
Dim SquadNumber3 As String
Dim SquadNumber4 As String
Dim PlayerName As String
Dim WS As Worksheet
Dim LastRow As Long

SquadNumber1 = Me.txtSquad1.Value
SquadNumber2 = Me.txtSquad2.Value
SquadNumber3 = Me.txtSquad3.Value
SquadNumber4 = Me.txtSquad4.Value

RelayNumber1 = Me.txtRelay1.Value
RelayNumber2 = Me.txtRelay2.Value
RelayNumber3 = Me.txtRelay3.Value
RelayNumber4 = Me.txtRelay4.Value

PostNumber1 = Me.txtPost1.Value
PostNumber2 = Me.txtPost2.Value
PostNumber3 = Me.txtPost3.Value
PostNumber4 = Me.txtPost4.Value

PlayerName = Me.txtPlayerName.Value

Application.ScreenUpdating = False
 
Set WS = ActiveWorkbook.Worksheets("Score Sheet")
LastRow = WS.Cells(Rows.Count, 1).End(xlUp).Row
 For r = 2 To LastRow
If WS.Cells(r, 15) = SquadNumber1 And WS.Cells(r, 16) = PostNumber1 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad1.Value = ""
    Me.txtPost1.Value = ""
    Me.txtRelay1.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber2 And WS.Cells(r, 16) = PostNumber2 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad2.Value = ""
    Me.txtPost2.Value = ""
    Me.txtRelay2.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber3 And WS.Cells(r, 16) = PostNumber3 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad3.Value = ""
    Me.txtPost3.Value = ""
    Me.txtRelay3.Value = ""
ElseIf WS.Cells(r, 15) = SquadNumber4 And WS.Cells(r, 16) = PostNumber4 Then
    WS.Cells(r, 4).Value = PlayerName
    Me.txtSquad4.Value = ""
    Me.txtPost4.Value = ""
    Me.txtRelay4.Value = ""
    
     End If

Next r
 

MsgBox "Added all squads to sheet"

 
Unload Me

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,251
Office Version
  1. 2013
Platform
  1. Windows
I have looked at this posting and I'm assuming you have solved your own posting
From your image you have 3 ComboBoxes
And about 12 Textboxes on a Userform.

Can you tell me what the overall objective is.
I believe your using a lot more code then needed.

You could have wrote one line of code like this:
Instead of your way like this:
WS.Cells(r, 4).Value = PlayerName
You can do it like this:
WS.Cells(r, 4).Value =Me.txtPlayerName.Value

You said:
the solution is that each variable needs to be declared individually to work.
With my version of Excel 2013 that is not required.
Not sure what version of Excel your using
 

Watch MrExcel Video

Forum statistics

Threads
1,122,297
Messages
5,595,301
Members
413,985
Latest member
daniel1969

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
Top