How to address cell number separately with variable for combo box in VBA

Question

How to address cell numbers separately? I tried with x integer, but it failed.

Private Sub Combo_Selection()

Select Case ComboBox1

      Case  'condition any cell in column A of page1
                  Application.Worksheets("page2") = Activate
                  call Range_selection

      Case  'condition any cell in column B of page1
                  Application.Worksheets("page3") = Activate
                  call Range_selection

      Case  'condition any cell in column C of page1
                  Application.Worksheets("page4") = Activate
                  call Range_selection
End Select

End sub


Private Sub Range_selection()

Select case ComboBox1

    Case ' condition first row in any column
                Application.ActiveWorksheets. Range ("A1:E50")

    Case ' condition second row in any column
                Application.ActiveWorksheets. Range ("A51:E100")

'so on upto 10th row

End Select

End Sub

Please be more specific about what your problem is!! Do you get an error message? What do want to achieve with the code/what is your expected result or output? If I understand you correctly by reading your code you want to call the Range_Selections when you select a value in the ComboBox? — Wizhi, Oct 13 '18 at 10:37
@Wizhi yes. You are correct. I need to activate page 1,2,3.... depends on column A,B,C...And then got to Range1,2,3,4.... in active sheet depends on row number 1,2,3..So I am trying to address my cell selected in combo box. I'm not a programmer. Just a beginner here. Please help.... thanking you....nithesh — Nithesh Thekkelath, Oct 13 '18 at 13:32

score 0 · Answer 1 · edited Oct 14 '18 at 15:34

This is a working program for selecting range from combo Box. This includes a lot of for loops. Any help me to change this to nested for loops.

See code below

Sub CommandButton1_Click()


On Error Resume Next

If ComboBox3.Value = "Select" Then

MsgBox "Please Select a Seller"

   Else

Call Show_Page

Unload UserForm1

End If

End Sub




Sub Show_Page()

For Each cell In Range("A2:A10")

 If cell.Value = Me.ComboBox3.Value
 Then
Worksheets("p1").Activate

Call Show_Range

Exit For

Exit Sub

End If

Next


For Each cell In Range("B2:B10")


    If cell.Value = Me.ComboBox3.Value 
 Then

    Worksheets("p2").Activate

    Call Show_Range

    Exit For

    End If

Next

End Sub

For Each cell In Range("C2:C10")


    If cell.Value = Me.ComboBox3.Value Then

    Worksheets("p3").Activate

    Call Show_Range

    Exit For

    End If

Next

End Sub


Sub Show_Range()


For Each cell In Range("A2:C2")

    If cell.Value = Me.ComboBox3.Value Then

    ActiveSheet.Range("A1:E20").Select

    End If

    Exit For

    Exit Sub    

Next

For Each cell In Range("A3:C3")

    If cell.Value = Me.ComboBox3.Value Then

    ActiveSheet.Range("A21:E40").Select

    Exit For

    Exit Sub

    End If

Next

For Each cell In Range("A4:C4")

    If cell.Value = Me.ComboBox3.Value Then

    ActiveSheet.Range("A41:E60").Select

    Exit For

    Exit Sub

    End If


For Each cell In Range("A5:C5")

    If cell.Value = Me.ComboBox3.Value Then

    ActiveSheet.Range("A1:j106").Select

    Exit For

    Exit Sub

    End If

Next

End Sub

How to address cell number separately with variable for combo box in VBA

1 Answers1