CODEWITHSUNDEEP
excelvba
cjvdg
cjvdg

Reputation: 533

VBA Excel If statement with AND/OR function

Just want to know your idea on this matter. So the thing is I'm trying to show the information in the UserForm with the MultiPage control. I have a Cert sheet where the data are stored. So in the Cert sheet, the count of the data can be equals to 1, 2, or 3. I already figured out the flow for 1 and 3 but I'm having a problem with the 2.

My plan is like this:

Here's a visual for your guide.

USERFORM: If the data is equal to 2 or 3, data should be shown in their respective boxes.

UserForm

SHEET DATA:

Sheet Data

The code below is the one that I'm currently using for 2.

For r = 9 To Lastrow

    If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then        'data
    
        If _
            ws.Cells(r, 3) = CStr( ThisWorkbook.Sheets("HOME").Range("K11").value ) And _
            ( ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 2 ) _
        Then
    
            If ws.Cells(r, 12).value = 1 Then        'show the data with the value of 1
                'FIRST BOX
                txtBox_LRN.Text = ws.Cells(r, 3).value
                txtBox_name.Text = ws.Cells(r, 4).value
                txtBox_grd.Text = ws.Cells(r, 5).value
    
            ElseIf ws.Cells(r, 12).value = 2 Then        'show the data with the value of 2
                'SECOND BOX
                sb_txtBox_LRN.Text = ws.Cells(r, 3).value
                sb_txtBox_name.Text = ws.Cells(r, 4).value
                sb_txtBox_grd.Text = ws.Cells(r, 5).value
            End If

        ElseIf _
            ws.Cells(r, 3) = CStr(ThisWorkbook.Sheets("HOME").Range("K11").value) And _
           ( ws.Cells(r, 12).value = 1 Or ws.Cells(r, 12).value = 3 ) _
        Then

            If ws.Cells(r, 12).value = 1 Then        'show the data with the value of 1
                'FIRST BOX
                txtBox_LRN.Text = ws.Cells(r, 3).value
                txtBox_name.Text = ws.Cells(r, 4).value
                txtBox_grd.Text = ws.Cells(r, 5).value

            ElseIf ws.Cells(r, 12).value = 2 Then        'show the data with the value of 3
                'THIRD BOX
                tb_txtBox_LRN.Text = ws.Cells(r, 3).value
                tb_txtBox_name.Text = ws.Cells(r, 4).value
                tb_txtBox_grd.Text = ws.Cells(r, 5).value
            End If

        ElseIf _
            ws.Cells(r, 3) = CStr( ThisWorkbook.Sheets("HOME").Range("K11").value ) And _
           ( ws.Cells(r, 12).value = 2 Or ws.Cells(r, 12).value = 3 ) _
        Then

            If ws.Cells(r, 12).value = 2 Then        'show the data with the value of 2
                'SECOND BOX
                sb_txtBox_LRN.Text = ws.Cells(r, 3).value
                sb_txtBox_name.Text = ws.Cells(r, 4).value
                sb_txtBox_grd.Text = ws.Cells(r, 5).value

            ElseIf ws.Cells(r, 12).value = 3 Then        'show the data with the value of 3
                'THIRD BOX
                tb_txtBox_LRN.Text = ws.Cells(r, 3).value
                tb_txtBox_name.Text = ws.Cells(r, 4).value
                tb_txtBox_grd.Text = ws.Cells(r, 5).value
            End If

        End If
    End If

Next r

The If box = 1 or box = 2 Then and If box = 1 or box = 3 Then are working but I'm having a problem with If box = 2 or box = 3 Then:

Upvotes: 3

Views: 409

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

This seems to be what your code is doing but I'm not sure it's correct...

Dim box, kValue

If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then        'data

    kValue =  CStr( ThisWorkbook.Sheets("HOME").Range("K11").value )

    For r = 9 To Lastrow

        If ws.Cells(r, 3) = kValue Then

            box = ws.Cells(r, 12).value

            If box = 1 Then
                txtBox_LRN.Text = ws.Cells(r, 3).value
                txtBox_name.Text = ws.Cells(r, 4).value
                txtBox_grd.Text = ws.Cells(r, 5).value
            Elseif box = 2 Then
                sb_txtBox_LRN.Text = ws.Cells(r, 3).value
                sb_txtBox_name.Text = ws.Cells(r, 4).value
                sb_txtBox_grd.Text = ws.Cells(r, 5).value
            Elseif box = 3 Then
                tb_txtBox_LRN.Text = ws.Cells(r, 3).value
                tb_txtBox_name.Text = ws.Cells(r, 4).value
                tb_txtBox_grd.Text = ws.Cells(r, 5).value
            End if

        end if

    Next r
end if

EDIT - slightly shorter:

Dim box As Long, kValue, pref As String

If Application.CountIf(Worksheets(ws_output).Columns(3), LRN) = 2 Then        'data

    kValue = CStr(ThisWorkbook.Sheets("HOME").Range("K11").Value)

    For r = 9 To Lastrow
        If ws.Cells(r, 3) = kValue Then
            box = ws.Cells(r, 12).Value
            If box >= 1 And box <= 3 Then
                pref = Array("", "sb_", "tb_")(box - 1)                      'get the control name prefix
                Me.Controls(pref & "txtBox_LRN").Text = ws.Cells(r, 3).Value 'reference controls by name...
                Me.Controls(pref & "txtBox_name").Text = ws.Cells(r, 4).Value
                Me.Controls(pref & "txtBox_grd").Text = ws.Cells(r, 5).Value
            End If
        End If
    Next r
End If

Upvotes: 2

Related Questions