+ Reply to Thread
Results 1 to 11 of 11

If ElseIf Else Statement not working

Hybrid View

  1. 05-27-2015, 07:17 AM #1
    lazyserv
    lazyserv is offline
    Registered User
    Join Date
    04-30-2014
    Location
    england
    MS-Off Ver
    Microsoft Excel 365
    Posts
    67

    If ElseIf Else Statement not working

    Hi Gurus,

    I have a userform whereby there is a drop down list for user to chose the data to be save at which outlet.

    Currently I have 3 outlet on hand therefore the data will be store according to the option selected.

    However I notice my statement doesn't work correctly for example I chose outlet "ABC" the data should store in ABC outlet but the data end up in GHI outlet
    On top of that if I chose DEF outlet the data end up go to incorrect row.

    I'm getting very confused with the if else statement code. You may refer the code here.

    Private Sub btnSubmit_Click()

Dim DataLog As Worksheet

Set DataLog = ThisWorkbook.Sheets("Datalog")

If Me.cmbOutlet.Text = "ABC" Then
'New Row 1
nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1

DataLog.Cells(nr, 2) = Me.tbDate1
DataLog.Cells(nr, 3) = "0" + Me.tbSample1
DataLog.Cells(nr, 4) = "0" + Me.tbItem1

DataLog.Cells(nr, 5) = "0" + Me.tbSample2
DataLog.Cells(nr, 6) = "0" + Me.tbItem2

DataLog.Cells(nr, 7) = "0" + Me.tbSample3
DataLog.Cells(nr, 8) = "0" + Me.tbItem3

DataLog.Cells(nr, 9) = "0" + Me.tbSample4
DataLog.Cells(nr, 10) = "0" + Me.tbItem4

DataLog.Cells(nr, 11) = "0" + Me.tbSample5
DataLog.Cells(nr, 12) = "0" + Me.tbItem5

DataLog.Cells(nr, 13) = "0" + Me.tbSample6
DataLog.Cells(nr, 14) = "0" + Me.tbItem6

DataLog.Cells(nr, 15) = "0" + Me.tbSample7
DataLog.Cells(nr, 16) = "0" + Me.tbItem7

DataLog.Cells(nr, 17) = "0" + Me.tbSample8
DataLog.Cells(nr, 18) = "0" + Me.tbItem8

DataLog.Cells(nr, 19) = "0" + Me.tbSample9
DataLog.Cells(nr, 20) = "0" + Me.tbItem9

DataLog.Cells(nr, 21) = "0" + Me.tbSample10
DataLog.Cells(nr, 22) = "0" + Me.tbItem10

DataLog.Cells(nr, 23) = "0" + Me.tbSample11
DataLog.Cells(nr, 24) = "0" + Me.tbItem11

DataLog.Cells(nr, 25) = "0" + Me.tbSample12
DataLog.Cells(nr, 26) = "0" + Me.tbItem12

'New Row 2
nr2 = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1

DataLog.Cells(nr2, 2) = Me.tbDate2
DataLog.Cells(nr2, 3) = "0" + Me.tbSample13
DataLog.Cells(nr2, 4) = "0" + Me.tbItem13

DataLog.Cells(nr2, 5) = "0" + Me.tbSample14
DataLog.Cells(nr2, 6) = "0" + Me.tbItem14

DataLog.Cells(nr2, 7) = "0" + Me.tbSample15
DataLog.Cells(nr2, 8) = "0" + Me.tbItem15

DataLog.Cells(nr2, 9) = "0" + Me.tbSample16
DataLog.Cells(nr2, 10) = "0" + Me.tbItem16

DataLog.Cells(nr2, 11) = "0" + Me.tbSample17
DataLog.Cells(nr2, 12) = "0" + Me.tbItem17

DataLog.Cells(nr2, 13) = "0" + Me.tbSample18
DataLog.Cells(nr2, 14) = "0" + Me.tbItem18

DataLog.Cells(nr2, 15) = "0" + Me.tbSample19
DataLog.Cells(nr2, 16) = "0" + Me.tbItem19

DataLog.Cells(nr2, 17) = "0" + Me.tbSample20
DataLog.Cells(nr2, 18) = "0" + Me.tbItem20

DataLog.Cells(nr2, 19) = "0" + Me.tbSample21
DataLog.Cells(nr2, 20) = "0" + Me.tbItem21

DataLog.Cells(nr2, 21) = "0" + Me.tbSample22
DataLog.Cells(nr2, 22) = "0" + Me.tbItem22

DataLog.Cells(nr2, 23) = "0" + Me.tbSample23
DataLog.Cells(nr2, 24) = "0" + Me.tbItem23

DataLog.Cells(nr2, 25) = "0" + Me.tbSample24
DataLog.Cells(nr2, 26) = "0" + Me.tbItem24

ElseIf Me.cmbOutlet.Text = "DEF" Then

'New Row 3
nr3 = DataLog.Cells(Rows.Count, 74).End(xlUp).Row + 1

DataLog.Cells(nr3, 2) = Me.tbDate1
DataLog.Cells(nr3, 3) = "0" + Me.tbSample1
DataLog.Cells(nr3, 4) = "0" + Me.tbItem1

DataLog.Cells(nr3, 5) = "0" + Me.tbSample2
DataLog.Cells(nr3, 6) = "0" + Me.tbItem2

DataLog.Cells(nr3, 7) = "0" + Me.tbSample3
DataLog.Cells(nr3, 8) = "0" + Me.tbItem3

DataLog.Cells(nr3, 9) = "0" + Me.tbSample4
DataLog.Cells(nr3, 10) = "0" + Me.tbItem4

DataLog.Cells(nr3, 11) = "0" + Me.tbSample5
DataLog.Cells(nr3, 12) = "0" + Me.tbItem5

DataLog.Cells(nr3, 13) = "0" + Me.tbSample6
DataLog.Cells(nr3, 14) = "0" + Me.tbItem6

DataLog.Cells(nr3, 15) = "0" + Me.tbSample7
DataLog.Cells(nr3, 16) = "0" + Me.tbItem7

DataLog.Cells(nr3, 17) = "0" + Me.tbSample8
DataLog.Cells(nr3, 18) = "0" + Me.tbItem8

DataLog.Cells(nr3, 19) = "0" + Me.tbSample9
DataLog.Cells(nr3, 20) = "0" + Me.tbItem9

DataLog.Cells(nr3, 21) = "0" + Me.tbSample10
DataLog.Cells(nr3, 22) = "0" + Me.tbItem10

DataLog.Cells(nr3, 23) = "0" + Me.tbSample11
DataLog.Cells(nr3, 24) = "0" + Me.tbItem11

DataLog.Cells(nr3, 25) = "0" + Me.tbSample12
DataLog.Cells(nr3, 26) = "0" + Me.tbItem12

'New Row 4
nr4 = DataLog.Cells(Rows.Count, 74).End(xlUp).Row + 1

DataLog.Cells(nr4, 2) = Me.tbDate2
DataLog.Cells(nr4, 3) = "0" + Me.tbSample13
DataLog.Cells(nr4, 4) = "0" + Me.tbItem13

DataLog.Cells(nr4, 5) = "0" + Me.tbSample14
DataLog.Cells(nr4, 6) = "0" + Me.tbItem14

DataLog.Cells(nr4, 7) = "0" + Me.tbSample15
DataLog.Cells(nr4, 8) = "0" + Me.tbItem15

DataLog.Cells(nr4, 9) = "0" + Me.tbSample16
DataLog.Cells(nr4, 10) = "0" + Me.tbItem16

DataLog.Cells(nr4, 11) = "0" + Me.tbSample17
DataLog.Cells(nr4, 12) = "0" + Me.tbItem17

DataLog.Cells(nr4, 13) = "0" + Me.tbSample18
DataLog.Cells(nr4, 14) = "0" + Me.tbItem18

DataLog.Cells(nr4, 15) = "0" + Me.tbSample19
DataLog.Cells(nr4, 16) = "0" + Me.tbItem19

DataLog.Cells(nr4, 17) = "0" + Me.tbSample20
DataLog.Cells(nr4, 18) = "0" + Me.tbItem20

DataLog.Cells(nr4, 19) = "0" + Me.tbSample21
DataLog.Cells(nr4, 20) = "0" + Me.tbItem21

DataLog.Cells(nr4, 21) = "0" + Me.tbSample22
DataLog.Cells(nr4, 22) = "0" + Me.tbItem22

DataLog.Cells(nr4, 23) = "0" + Me.tbSample23
DataLog.Cells(nr4, 24) = "0" + Me.tbItem23

DataLog.Cells(nr4, 25) = "0" + Me.tbSample24
DataLog.Cells(nr4, 26) = "0" + Me.tbItem24


ElseIf Me.cmbOutlet.Text = "GHI" Then

'New Row 5
nr5 = DataLog.Cells(Rows.Count, 143).End(xlUp).Row + 1

DataLog.Cells(nr5, 2) = Me.tbDate1
DataLog.Cells(nr5, 3) = "0" + Me.tbSample1
DataLog.Cells(nr5, 4) = "0" + Me.tbItem1

DataLog.Cells(nr5, 5) = "0" + Me.tbSample2
DataLog.Cells(nr5, 6) = "0" + Me.tbItem2

DataLog.Cells(nr5, 7) = "0" + Me.tbSample3
DataLog.Cells(nr5, 8) = "0" + Me.tbItem3

DataLog.Cells(nr5, 9) = "0" + Me.tbSample4
DataLog.Cells(nr5, 10) = "0" + Me.tbItem4

DataLog.Cells(nr5, 11) = "0" + Me.tbSample5
DataLog.Cells(nr5, 12) = "0" + Me.tbItem5

DataLog.Cells(nr5, 13) = "0" + Me.tbSample6
DataLog.Cells(nr5, 14) = "0" + Me.tbItem6

DataLog.Cells(nr5, 15) = "0" + Me.tbSample7
DataLog.Cells(nr5, 16) = "0" + Me.tbItem7

DataLog.Cells(nr5, 17) = "0" + Me.tbSample8
DataLog.Cells(nr5, 18) = "0" + Me.tbItem8

DataLog.Cells(nr5, 19) = "0" + Me.tbSample9
DataLog.Cells(nr5, 20) = "0" + Me.tbItem9

DataLog.Cells(nr5, 21) = "0" + Me.tbSample10
DataLog.Cells(nr5, 22) = "0" + Me.tbItem10

DataLog.Cells(nr5, 23) = "0" + Me.tbSample11
DataLog.Cells(nr5, 24) = "0" + Me.tbItem11

DataLog.Cells(nr5, 25) = "0" + Me.tbSample12
DataLog.Cells(nr5, 26) = "0" + Me.tbItem12

'New Row 6
nr6 = DataLog.Cells(Rows.Count, 143).End(xlUp).Row + 1

DataLog.Cells(nr6, 2) = Me.tbDate2
DataLog.Cells(nr6, 3) = "0" + Me.tbSample13
DataLog.Cells(nr6, 4) = "0" + Me.tbItem13

DataLog.Cells(nr6, 5) = "0" + Me.tbSample14
DataLog.Cells(nr6, 6) = "0" + Me.tbItem14

DataLog.Cells(nr6, 7) = "0" + Me.tbSample15
DataLog.Cells(nr6, 8) = "0" + Me.tbItem15

DataLog.Cells(nr6, 9) = "0" + Me.tbSample16
DataLog.Cells(nr6, 10) = "0" + Me.tbItem16

DataLog.Cells(nr6, 11) = "0" + Me.tbSample17
DataLog.Cells(nr6, 12) = "0" + Me.tbItem17

DataLog.Cells(nr6, 13) = "0" + Me.tbSample18
DataLog.Cells(nr6, 14) = "0" + Me.tbItem18

DataLog.Cells(nr6, 15) = "0" + Me.tbSample19
DataLog.Cells(nr6, 16) = "0" + Me.tbItem19

DataLog.Cells(nr6, 17) = "0" + Me.tbSample20
DataLog.Cells(nr6, 18) = "0" + Me.tbItem20

DataLog.Cells(nr6, 19) = "0" + Me.tbSample21
DataLog.Cells(nr6, 20) = "0" + Me.tbItem21

DataLog.Cells(nr6, 21) = "0" + Me.tbSample22
DataLog.Cells(nr6, 22) = "0" + Me.tbItem22

DataLog.Cells(nr6, 23) = "0" + Me.tbSample23
DataLog.Cells(nr6, 24) = "0" + Me.tbItem23

DataLog.Cells(nr6, 25) = "0" + Me.tbSample24
DataLog.Cells(nr6, 26) = "0" + Me.tbItem24

Else
'Do Nothing

End If


'Unload Form
Unload DataLogForm

End Sub

Private Sub btnGoTo_Click()

'Go to Summary Worksheet and Unload Form
Unload Me

End Sub

Private Sub UserForm_Initialize()

Me.tbDate1 = Me.tbWeekBox + Format(DateAdd("D", -Weekday(Date), Date), "dd-mmm-yy")
Me.tbDate2 = Me.tbWeekBox + Format(DateAdd("D", -Weekday(Date) + 1, Date), "dd-mmm-yy")

For Each blah In[List2]
    Me.cmbOutlet.AddItem blah
Next blah

End Sub
    To make you easy understand what problem I'm Facing, I attached the duplicate file together.

    Thank you.

    Report-Test.xlsm
    Reply With QuoteRegister To Reply
  2. 05-27-2015, 07:47 AM #2
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: If ElseIf Else Statement not working

    u have wrongly define the Last row

    nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1
    that is what its going in GHI
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
    Reply With QuoteRegister To Reply
  3. 05-27-2015, 07:55 AM #3
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: If ElseIf Else Statement not working

    Just replace your Row finding code with below one

    In ABC
    'New Row 1
nr = DataLog.Cells(70, 2).End(xlUp).Row + 1

'New Row 2
nr2 = DataLog.Cells(70, 2).End(xlUp).Row + 1
    In DEF
    'New Row 3
nr3 = DataLog.Cells(139, 2).End(xlUp).Row + 1

'New Row 4
nr4 = DataLog.Cells(39, 2).End(xlUp).Row + 1
    In GHI

    'New Row 5
nr5 = DataLog.Cells(Rows.Count, 2).End(xlUp).Row + 1

'New Row 6
nr6 = DataLog.Cells(Rows.Count, 2).End(xlUp).Row + 1
    Reply With QuoteRegister To Reply
  4. 05-27-2015, 08:35 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    The problem is this: 
    nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1
    That is the equivalent of saying go to the bottom of column E and press the End key and the Up Arrow key.

    Unfortunately, that takes you to the area related to outlet GHI, regardless of what outlet you select in the drop down combo box.

    You need to modify the way that you locate the relevant row, something like the code below:

    Private Sub btnSubmit_Click()

Dim DataLog As Worksheet

Set DataLog = ThisWorkbook.Sheets("Datalog")

If Me.cmbOutlet.Text = "ABC" Then
'New Row 1
'nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1
nr = Application.Match("ABC", Columns("B"), 0)
nrx = Application.Match(Me.tbWeekBox + 0, Range("A" & nr & ":A" & Rows.Count), 0)
nr = nr + nrx - 1

DataLog.Cells(nr, 2) = Me.tbDate1
DataLog.Cells(nr, 3) = "0" + Me.tbSample1
DataLog.Cells(nr, 4) = "0" + Me.tbItem1

DataLog.Cells(nr, 5) = "0" + Me.tbSample2
DataLog.Cells(nr, 6) = "0" + Me.tbItem2

DataLog.Cells(nr, 7) = "0" + Me.tbSample3
DataLog.Cells(nr, 8) = "0" + Me.tbItem3

DataLog.Cells(nr, 9) = "0" + Me.tbSample4
DataLog.Cells(nr, 10) = "0" + Me.tbItem4

DataLog.Cells(nr, 11) = "0" + Me.tbSample5
DataLog.Cells(nr, 12) = "0" + Me.tbItem5

DataLog.Cells(nr, 13) = "0" + Me.tbSample6
DataLog.Cells(nr, 14) = "0" + Me.tbItem6

DataLog.Cells(nr, 15) = "0" + Me.tbSample7
DataLog.Cells(nr, 16) = "0" + Me.tbItem7

DataLog.Cells(nr, 17) = "0" + Me.tbSample8
DataLog.Cells(nr, 18) = "0" + Me.tbItem8

DataLog.Cells(nr, 19) = "0" + Me.tbSample9
DataLog.Cells(nr, 20) = "0" + Me.tbItem9

DataLog.Cells(nr, 21) = "0" + Me.tbSample10
DataLog.Cells(nr, 22) = "0" + Me.tbItem10

DataLog.Cells(nr, 23) = "0" + Me.tbSample11
DataLog.Cells(nr, 24) = "0" + Me.tbItem11

DataLog.Cells(nr, 25) = "0" + Me.tbSample12
DataLog.Cells(nr, 26) = "0" + Me.tbItem12

'New Row 2
nr2 = nr + 1

DataLog.Cells(nr2, 2) = Me.tbDate2
DataLog.Cells(nr2, 3) = "0" + Me.tbSample13
DataLog.Cells(nr2, 4) = "0" + Me.tbItem13

DataLog.Cells(nr2, 5) = "0" + Me.tbSample14
DataLog.Cells(nr2, 6) = "0" + Me.tbItem14

DataLog.Cells(nr2, 7) = "0" + Me.tbSample15
DataLog.Cells(nr2, 8) = "0" + Me.tbItem15

DataLog.Cells(nr2, 9) = "0" + Me.tbSample16
DataLog.Cells(nr2, 10) = "0" + Me.tbItem16

DataLog.Cells(nr2, 11) = "0" + Me.tbSample17
DataLog.Cells(nr2, 12) = "0" + Me.tbItem17

DataLog.Cells(nr2, 13) = "0" + Me.tbSample18
DataLog.Cells(nr2, 14) = "0" + Me.tbItem18

DataLog.Cells(nr2, 15) = "0" + Me.tbSample19
DataLog.Cells(nr2, 16) = "0" + Me.tbItem19

DataLog.Cells(nr2, 17) = "0" + Me.tbSample20
DataLog.Cells(nr2, 18) = "0" + Me.tbItem20

DataLog.Cells(nr2, 19) = "0" + Me.tbSample21
DataLog.Cells(nr2, 20) = "0" + Me.tbItem21

DataLog.Cells(nr2, 21) = "0" + Me.tbSample22
DataLog.Cells(nr2, 22) = "0" + Me.tbItem22

DataLog.Cells(nr2, 23) = "0" + Me.tbSample23
DataLog.Cells(nr2, 24) = "0" + Me.tbItem23

DataLog.Cells(nr2, 25) = "0" + Me.tbSample24
DataLog.Cells(nr2, 26) = "0" + Me.tbItem24

ElseIf Me.cmbOutlet.Text = "DEF" Then

'New Row 3
'nr3 = DataLog.Cells(Rows.Count, 74).End(xlUp).Row + 1
nr3 = Application.Match("DEF", Columns("B"), 0)
nrx = Application.Match(Me.tbWeekBox + 0, Range("A" & nr3 & ":A" & Rows.Count), 0)
nr3 = nr3 + nrx - 1

DataLog.Cells(nr3, 2) = Me.tbDate1
DataLog.Cells(nr3, 3) = "0" + Me.tbSample1
DataLog.Cells(nr3, 4) = "0" + Me.tbItem1

DataLog.Cells(nr3, 5) = "0" + Me.tbSample2
DataLog.Cells(nr3, 6) = "0" + Me.tbItem2

DataLog.Cells(nr3, 7) = "0" + Me.tbSample3
DataLog.Cells(nr3, 8) = "0" + Me.tbItem3

DataLog.Cells(nr3, 9) = "0" + Me.tbSample4
DataLog.Cells(nr3, 10) = "0" + Me.tbItem4

DataLog.Cells(nr3, 11) = "0" + Me.tbSample5
DataLog.Cells(nr3, 12) = "0" + Me.tbItem5

DataLog.Cells(nr3, 13) = "0" + Me.tbSample6
DataLog.Cells(nr3, 14) = "0" + Me.tbItem6

DataLog.Cells(nr3, 15) = "0" + Me.tbSample7
DataLog.Cells(nr3, 16) = "0" + Me.tbItem7

DataLog.Cells(nr3, 17) = "0" + Me.tbSample8
DataLog.Cells(nr3, 18) = "0" + Me.tbItem8

DataLog.Cells(nr3, 19) = "0" + Me.tbSample9
DataLog.Cells(nr3, 20) = "0" + Me.tbItem9

DataLog.Cells(nr3, 21) = "0" + Me.tbSample10
DataLog.Cells(nr3, 22) = "0" + Me.tbItem10

DataLog.Cells(nr3, 23) = "0" + Me.tbSample11
DataLog.Cells(nr3, 24) = "0" + Me.tbItem11

DataLog.Cells(nr3, 25) = "0" + Me.tbSample12
DataLog.Cells(nr3, 26) = "0" + Me.tbItem12

'New Row 4
nr4 = nr3 + 1

DataLog.Cells(nr4, 2) = Me.tbDate2
DataLog.Cells(nr4, 3) = "0" + Me.tbSample13
DataLog.Cells(nr4, 4) = "0" + Me.tbItem13

DataLog.Cells(nr4, 5) = "0" + Me.tbSample14
DataLog.Cells(nr4, 6) = "0" + Me.tbItem14

DataLog.Cells(nr4, 7) = "0" + Me.tbSample15
DataLog.Cells(nr4, 8) = "0" + Me.tbItem15

DataLog.Cells(nr4, 9) = "0" + Me.tbSample16
DataLog.Cells(nr4, 10) = "0" + Me.tbItem16

DataLog.Cells(nr4, 11) = "0" + Me.tbSample17
DataLog.Cells(nr4, 12) = "0" + Me.tbItem17

DataLog.Cells(nr4, 13) = "0" + Me.tbSample18
DataLog.Cells(nr4, 14) = "0" + Me.tbItem18

DataLog.Cells(nr4, 15) = "0" + Me.tbSample19
DataLog.Cells(nr4, 16) = "0" + Me.tbItem19

DataLog.Cells(nr4, 17) = "0" + Me.tbSample20
DataLog.Cells(nr4, 18) = "0" + Me.tbItem20

DataLog.Cells(nr4, 19) = "0" + Me.tbSample21
DataLog.Cells(nr4, 20) = "0" + Me.tbItem21

DataLog.Cells(nr4, 21) = "0" + Me.tbSample22
DataLog.Cells(nr4, 22) = "0" + Me.tbItem22

DataLog.Cells(nr4, 23) = "0" + Me.tbSample23
DataLog.Cells(nr4, 24) = "0" + Me.tbItem23

DataLog.Cells(nr4, 25) = "0" + Me.tbSample24
DataLog.Cells(nr4, 26) = "0" + Me.tbItem24

ElseIf Me.cmbOutlet.Text = "GHI" Then

'New Row 5
'nr5 = DataLog.Cells(Rows.Count, 143).End(xlUp).Row + 1
nr5 = Application.Match("GHI", Columns("B"), 0)
nrx = Application.Match(Me.tbWeekBox + 0, Range("A" & nr5 & ":A" & Rows.Count), 0)
nr5 = nr5 + nrx - 1

DataLog.Cells(nr5, 2) = Me.tbDate1
DataLog.Cells(nr5, 3) = "0" + Me.tbSample1
DataLog.Cells(nr5, 4) = "0" + Me.tbItem1

DataLog.Cells(nr5, 5) = "0" + Me.tbSample2
DataLog.Cells(nr5, 6) = "0" + Me.tbItem2

DataLog.Cells(nr5, 7) = "0" + Me.tbSample3
DataLog.Cells(nr5, 8) = "0" + Me.tbItem3

DataLog.Cells(nr5, 9) = "0" + Me.tbSample4
DataLog.Cells(nr5, 10) = "0" + Me.tbItem4

DataLog.Cells(nr5, 11) = "0" + Me.tbSample5
DataLog.Cells(nr5, 12) = "0" + Me.tbItem5

DataLog.Cells(nr5, 13) = "0" + Me.tbSample6
DataLog.Cells(nr5, 14) = "0" + Me.tbItem6

DataLog.Cells(nr5, 15) = "0" + Me.tbSample7
DataLog.Cells(nr5, 16) = "0" + Me.tbItem7

DataLog.Cells(nr5, 17) = "0" + Me.tbSample8
DataLog.Cells(nr5, 18) = "0" + Me.tbItem8

DataLog.Cells(nr5, 19) = "0" + Me.tbSample9
DataLog.Cells(nr5, 20) = "0" + Me.tbItem9

DataLog.Cells(nr5, 21) = "0" + Me.tbSample10
DataLog.Cells(nr5, 22) = "0" + Me.tbItem10

DataLog.Cells(nr5, 23) = "0" + Me.tbSample11
DataLog.Cells(nr5, 24) = "0" + Me.tbItem11

DataLog.Cells(nr5, 25) = "0" + Me.tbSample12
DataLog.Cells(nr5, 26) = "0" + Me.tbItem12

'New Row 6
nr6 = nr5 + 1

DataLog.Cells(nr6, 2) = Me.tbDate2
DataLog.Cells(nr6, 3) = "0" + Me.tbSample13
DataLog.Cells(nr6, 4) = "0" + Me.tbItem13

DataLog.Cells(nr6, 5) = "0" + Me.tbSample14
DataLog.Cells(nr6, 6) = "0" + Me.tbItem14

DataLog.Cells(nr6, 7) = "0" + Me.tbSample15
DataLog.Cells(nr6, 8) = "0" + Me.tbItem15

DataLog.Cells(nr6, 9) = "0" + Me.tbSample16
DataLog.Cells(nr6, 10) = "0" + Me.tbItem16

DataLog.Cells(nr6, 11) = "0" + Me.tbSample17
DataLog.Cells(nr6, 12) = "0" + Me.tbItem17

DataLog.Cells(nr6, 13) = "0" + Me.tbSample18
DataLog.Cells(nr6, 14) = "0" + Me.tbItem18

DataLog.Cells(nr6, 15) = "0" + Me.tbSample19
DataLog.Cells(nr6, 16) = "0" + Me.tbItem19

DataLog.Cells(nr6, 17) = "0" + Me.tbSample20
DataLog.Cells(nr6, 18) = "0" + Me.tbItem20

DataLog.Cells(nr6, 19) = "0" + Me.tbSample21
DataLog.Cells(nr6, 20) = "0" + Me.tbItem21

DataLog.Cells(nr6, 21) = "0" + Me.tbSample22
DataLog.Cells(nr6, 22) = "0" + Me.tbItem22

DataLog.Cells(nr6, 23) = "0" + Me.tbSample23
DataLog.Cells(nr6, 24) = "0" + Me.tbItem23

DataLog.Cells(nr6, 25) = "0" + Me.tbSample24
DataLog.Cells(nr6, 26) = "0" + Me.tbItem24

Else
'Do Nothing

End If


'Unload Form
Unload DataLogForm

End Sub

    See the attached example.

    Regards, TMS
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 05-27-2015, 08:49 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    Money where mouth is ...

    Private Sub btnSubmit_Click()

Dim DataLog As Worksheet

Set DataLog = ThisWorkbook.Sheets("Datalog")

If Me.cmbOutlet.Text = "ABC" _
Or Me.cmbOutlet.Text = "DEF" _
Or Me.cmbOutlet.Text = "GHI" _
Then

'New Row 1
'nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1
nr = Application.Match(Me.cmbOutlet.Text, Columns("B"), 0)
nrx = Application.Match(Me.tbWeekBox + 0, Range("A" & nr & ":A" & Rows.Count), 0)
nr = nr + nrx - 1

DataLog.Cells(nr, 2) = Me.tbDate1
DataLog.Cells(nr, 3) = "0" + Me.tbSample1
DataLog.Cells(nr, 4) = "0" + Me.tbItem1

DataLog.Cells(nr, 5) = "0" + Me.tbSample2
DataLog.Cells(nr, 6) = "0" + Me.tbItem2

DataLog.Cells(nr, 7) = "0" + Me.tbSample3
DataLog.Cells(nr, 8) = "0" + Me.tbItem3

DataLog.Cells(nr, 9) = "0" + Me.tbSample4
DataLog.Cells(nr, 10) = "0" + Me.tbItem4

DataLog.Cells(nr, 11) = "0" + Me.tbSample5
DataLog.Cells(nr, 12) = "0" + Me.tbItem5

DataLog.Cells(nr, 13) = "0" + Me.tbSample6
DataLog.Cells(nr, 14) = "0" + Me.tbItem6

DataLog.Cells(nr, 15) = "0" + Me.tbSample7
DataLog.Cells(nr, 16) = "0" + Me.tbItem7

DataLog.Cells(nr, 17) = "0" + Me.tbSample8
DataLog.Cells(nr, 18) = "0" + Me.tbItem8

DataLog.Cells(nr, 19) = "0" + Me.tbSample9
DataLog.Cells(nr, 20) = "0" + Me.tbItem9

DataLog.Cells(nr, 21) = "0" + Me.tbSample10
DataLog.Cells(nr, 22) = "0" + Me.tbItem10

DataLog.Cells(nr, 23) = "0" + Me.tbSample11
DataLog.Cells(nr, 24) = "0" + Me.tbItem11

DataLog.Cells(nr, 25) = "0" + Me.tbSample12
DataLog.Cells(nr, 26) = "0" + Me.tbItem12

'New Row 2
nr2 = nr + 1

DataLog.Cells(nr2, 2) = Me.tbDate2
DataLog.Cells(nr2, 3) = "0" + Me.tbSample13
DataLog.Cells(nr2, 4) = "0" + Me.tbItem13

DataLog.Cells(nr2, 5) = "0" + Me.tbSample14
DataLog.Cells(nr2, 6) = "0" + Me.tbItem14

DataLog.Cells(nr2, 7) = "0" + Me.tbSample15
DataLog.Cells(nr2, 8) = "0" + Me.tbItem15

DataLog.Cells(nr2, 9) = "0" + Me.tbSample16
DataLog.Cells(nr2, 10) = "0" + Me.tbItem16

DataLog.Cells(nr2, 11) = "0" + Me.tbSample17
DataLog.Cells(nr2, 12) = "0" + Me.tbItem17

DataLog.Cells(nr2, 13) = "0" + Me.tbSample18
DataLog.Cells(nr2, 14) = "0" + Me.tbItem18

DataLog.Cells(nr2, 15) = "0" + Me.tbSample19
DataLog.Cells(nr2, 16) = "0" + Me.tbItem19

DataLog.Cells(nr2, 17) = "0" + Me.tbSample20
DataLog.Cells(nr2, 18) = "0" + Me.tbItem20

DataLog.Cells(nr2, 19) = "0" + Me.tbSample21
DataLog.Cells(nr2, 20) = "0" + Me.tbItem21

DataLog.Cells(nr2, 21) = "0" + Me.tbSample22
DataLog.Cells(nr2, 22) = "0" + Me.tbItem22

DataLog.Cells(nr2, 23) = "0" + Me.tbSample23
DataLog.Cells(nr2, 24) = "0" + Me.tbItem23

DataLog.Cells(nr2, 25) = "0" + Me.tbSample24
DataLog.Cells(nr2, 26) = "0" + Me.tbItem24

End If

'Unload Form
Unload DataLogForm

End Sub
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-27-2015, 08:43 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    The form specifies a Week Number so, presumably, it's not just a case of going up to the next available blank row for an outlet, otherwise there would be little point specifying a week number and listing the weeks down column A

    Hence the reason that I have used: 
    nr = Application.Match("ABC", Columns("B"), 0)
nrx = Application.Match(Me.tbWeekBox + 0, Range("A" & nr & ":A" & Rows.Count), 0)
nr = nr + nrx - 1
    and, later:

    nr2 = nr + 1

    Using separate row numbers for each outlet means the code is, of necessity, repetitive and redundant. I haven't changed it, but, instead of hard coding the store in the MATCH, you could use the input store. You could then replace the nr3, nr4, nr5 and nr6 and remove two thirds of the code.


    Regards, TMS
    Reply With QuoteRegister To Reply
  7. 05-27-2015, 10:14 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    Thank you for your kind comments in your Private Messages.

    If these suggestions have resolved your issue, please mark this thread as solved.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  8. 05-27-2015, 10:31 AM #8
    lazyserv
    lazyserv is offline
    Registered User
    Join Date
    04-30-2014
    Location
    england
    MS-Off Ver
    Microsoft Excel 365
    Posts
    67

    Re: If ElseIf Else Statement not working

    Hi TMS,

    Yes your solution do solve partly of my problem, however due to the form have interrelated problem, I hope you don't mind to take a look at this thread

    http://www.excelforum.com/excel-prog...-userform.html

    this code work great with specific of week enter in the column
    nrx = Application.Match(Me.tbWeekBox + 0, Range("B" & nr & ":B" & Rows.Count), 0)
    However, my current setup required the week column value that Enter by the User therefore, the code you provide couldn't work.

    Please advise if there is another round of code that able to solve this problem?

    Thank you so much!

    Quote Originally Posted by TMS View Post
    Thank you for your kind comments in your Private Messages.

    If these suggestions have resolved your issue, please mark this thread as solved.


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.
    Reply With QuoteRegister To Reply
  9. 05-27-2015, 10:48 AM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    Please don't quote entire posts. It is unnecessary and just clutters the thread.

    However, my current setup required the week column value that Enter by the User therefore, the code you provide couldn't work.
    The code, as provided (by me) in both versions does work. It uses the Week Number entered into the box in the User Form to determine which rows to update. Other responses just addressed the fact that using End(xlUp) would not necessarily get you to where you wanted to be, and provided workarounds.

    How else would the user enter a Week Number? And what is the purpose of having the box on the User Form if you do not use it to match the week number in the worksheet?

    Have you checked and tested the updated workbooks that I uploaded? Note that the second one is about 3kb smaller from removing the redundant code.

    Personally, I probably wouldn't have separate areas for each Outlet. I'd just have an extra column and write the Outlet Code there. Similarly, use the Week Number entered in the User Form in a separate column instead of pre-coding the entries. Then you can just sort and filter the data by Outlet and Week Number.

    But, in practical terms, this isn't really the place to design your workbook/worksheet/application on the fly.

    Regards, TMS
    Reply With QuoteRegister To Reply
  10. 05-27-2015, 11:27 AM #10
    lazyserv
    lazyserv is offline
    Registered User
    Join Date
    04-30-2014
    Location
    england
    MS-Off Ver
    Microsoft Excel 365
    Posts
    67

    Re: If ElseIf Else Statement not working

    I fixed the date problem and amend the whole file according to your suggestion, therefore I have this error code in debug mode

    nr = nr + nrx - 1
    You may refer attached file to see the changes.

    Thank you so much.

    Edit: I notice if my Week column empty which causing this problem, may I know how to solve it?

    The code:

    Private Sub btnSubmit_Click()

Dim DataLog As Worksheet

Set DataLog = ThisWorkbook.Sheets("Datalog")

If Me.cmbOutlet.Text = "ABC" _
Or Me.cmbOutlet.Text = "DEF" _
Or Me.cmbOutlet.Text = "GHI" _
Then

'New Row 1
'nr = DataLog.Cells(Rows.Count, 5).End(xlUp).Row + 1
nr = Application.Match(Me.cmbOutlet.Text, Columns("A"), 0)
nrx = Application.Match(Me.TextBox1 + 0, Range("B" & nr & ":B" & Rows.Count), 0)
nr = nr + nrx - 1

DataLog.Cells(nr, 3) = Me.TextBox2
DataLog.Cells(nr, 4) = "0" + Me.tbSample1
DataLog.Cells(nr, 5) = "0" + Me.tbItem1

DataLog.Cells(nr, 7) = "0" + Me.tbSample2
DataLog.Cells(nr, 8) = "0" + Me.tbItem2

DataLog.Cells(nr, 10) = "0" + Me.tbSample3
DataLog.Cells(nr, 11) = "0" + Me.tbItem3

DataLog.Cells(nr, 13) = "0" + Me.tbSample4
DataLog.Cells(nr, 14) = "0" + Me.tbItem4

DataLog.Cells(nr, 16) = "0" + Me.tbSample5
DataLog.Cells(nr, 17) = "0" + Me.tbItem5

DataLog.Cells(nr, 19) = "0" + Me.tbSample6
DataLog.Cells(nr, 20) = "0" + Me.tbItem6

DataLog.Cells(nr, 22) = "0" + Me.tbSample7
DataLog.Cells(nr, 23) = "0" + Me.tbItem7

DataLog.Cells(nr, 25) = "0" + Me.tbSample8
DataLog.Cells(nr, 26) = "0" + Me.tbItem8

DataLog.Cells(nr, 28) = "0" + Me.tbSample9
DataLog.Cells(nr, 29) = "0" + Me.tbItem9

DataLog.Cells(nr, 31) = "0" + Me.tbSample10
DataLog.Cells(nr, 32) = "0" + Me.tbItem10

DataLog.Cells(nr, 34) = "0" + Me.tbSample11
DataLog.Cells(nr, 35) = "0" + Me.tbItem11

DataLog.Cells(nr, 37) = "0" + Me.tbSample12
DataLog.Cells(nr, 38) = "0" + Me.tbItem12

'New Row 2
nr2 = nr + 1

DataLog.Cells(nr2, 3) = Me.TextBox3
DataLog.Cells(nr2, 4) = "0" + Me.tbSample13
DataLog.Cells(nr2, 5) = "0" + Me.tbItem13

DataLog.Cells(nr2, 7) = "0" + Me.tbSample14
DataLog.Cells(nr2, 8) = "0" + Me.tbItem14

DataLog.Cells(nr2, 10) = "0" + Me.tbSample15
DataLog.Cells(nr2, 11) = "0" + Me.tbItem15

DataLog.Cells(nr2, 13) = "0" + Me.tbSample16
DataLog.Cells(nr2, 14) = "0" + Me.tbItem16

DataLog.Cells(nr2, 16) = "0" + Me.tbSample17
DataLog.Cells(nr2, 17) = "0" + Me.tbItem17

DataLog.Cells(nr2, 19) = "0" + Me.tbSample18
DataLog.Cells(nr2, 20) = "0" + Me.tbItem18

DataLog.Cells(nr2, 22) = "0" + Me.tbSample19
DataLog.Cells(nr2, 23) = "0" + Me.tbItem19

DataLog.Cells(nr2, 25) = "0" + Me.tbSample20
DataLog.Cells(nr2, 26) = "0" + Me.tbItem20

DataLog.Cells(nr2, 28) = "0" + Me.tbSample21
DataLog.Cells(nr2, 29) = "0" + Me.tbItem21

DataLog.Cells(nr2, 31) = "0" + Me.tbSample22
DataLog.Cells(nr2, 32) = "0" + Me.tbItem22

DataLog.Cells(nr2, 34) = "0" + Me.tbSample23
DataLog.Cells(nr2, 35) = "0" + Me.tbItem23

DataLog.Cells(nr2, 37) = "0" + Me.tbSample24
DataLog.Cells(nr2, 38) = "0" + Me.tbItem24

End If

'Unload Form
Unload DataLogForm

End Sub

Private Sub btnGoTo_Click()

'Go to Summary Worksheet and Unload Form
Unload Me
'ThisWorkbook.Sheets("Summary").Select

End Sub

Private Sub UserForm_Initialize()

For Each blah In[List2]
    Me.cmbOutlet.AddItem blah
Next blah

End Sub

Private Sub TextBox1_Change()
    TextBox2.Text = (7 * Val(TextBox1.Text)) + DateSerial(Year(Date), 1, 1) - 5
    TextBox3.Text = (7 * Val(TextBox1.Text)) + DateSerial(Year(Date), 1, 1) - 4
End Sub
    Report-Test-v1.xlsm
    Last edited by lazyserv; 05-27-2015 at 01:26 PM. Reason: added info
    Reply With QuoteRegister To Reply
  11. 05-28-2015, 03:38 AM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,455

    Re: If ElseIf Else Statement not working

    OK, that wasn't quite what I had in mind My intention/expectation is that there are NO ENTRIES at all for Outlet and Week Number, and they are completed from the User Form.

    Please see the attached updated example. I have cleared the "raw data" input sheet. Just complete the form for each Outlet/Week and Save the data.

    Regards, TMS
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Adding CountIf Statement within If/ElseIf Statement
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2014, 05:09 AM
  2. [SOLVED] If...Then...ElseIf statement
    By BrownBoy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2014, 05:19 PM
  3. Code stepping in to elseif statement when elseif is definitely not true
    By DFrank231 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-13-2013, 05:31 PM
  4. If then elseif statement not working.........
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-11-2011, 02:27 PM
  5. If then elseif statement
    By Mac10 in forum Excel General
    Replies: 3
    Last Post: 10-02-2010, 09:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1