If and and or question

[Sneaky Pete]

Hello all,

I've got a question about the if, and, and or function.
This function has to look in 2 cells for a value and then give a specific result.
I will explain this by a example.

Part 1
In cell A1 the user has to choose from a listbox for a value A, B, C or D.
In cell A2 the user can also choose from a listbox for a value 1, 2.
Ok, if the user select in cell A1 A and in cell B1 1, then it should return a list 100, 200, 300, 400 and 500. If the value in cell B2 is 2 then it should return a list of 600, 700, 800, 900.

Part 2
When the user select B in cell A1 and 1 in B2, the it should return a list of 110, 210, 310, 410 or 510. If the value in cell B1 is 2 then it should return a list of 610, 710, 810, 910.

I've been trying this with a if(and(or)) function.
Only for the first part it works, when I put part 2 to the formula (to create a nested function) it gives a error like you got to many variables for this function. ?
Eventually I want the formula to return these values as input for further calculations.

Hope you can give me a solution.

Thanx in advance.

Sneaky Pete

 

[tlowry]

I would like to change the value of the validation list in G4 automatic when the value in cell A and/of B changes.

Put the following code into the module for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("A1:B1"), _
                Target.Cells(1, 1)) Is Nothing Then setVal
End Sub

1. Development tab > Visual Basic ctrl R
2. Double click the worksheet
3. Paste the code
4. Save and test
5. Post results

Finally the same formula has to work for the Range A26:A40 and B26:B40.

Please give more details. Examples would help…

 

[Sneaky Pete]

The code works fine, for the cells A1, B1 en G4.
The value in cell A1 and B1 returns a validation list in cell G4.

What I want is that the same code works for the range A1:A26, range B1:B26 and G4:G29.
So the value in cell A2 and B2 returns a validation list in cell G5, A3 and B3 returns to G6, etc.

Thanx in advance.

Sneaky Pete

 

[tlowry]

    Dim testRange
    Set testRange = Range("A26:B40")
    If Application.Intersect(Target(1, 1), testRange) Is Nothing And _
        Application.Intersect(Target(1, 1), testRange) Is Nothing Then Exit Sub
    setVal (Target.Row)
End Sub

Sub setVal(irow)
    Dim getlst
    getlst = aGetList(irow)
    Select Case getlst(0)
        Case "#N/A"
            With Range("G4")
                .Validation.Delete
                .Value = "#N/A"
            End With
        Case Else
            With Range("G4").Validation
                .Delete
                Range("G4") = ""
                .Add Type:=xlValidateList, Formula1:=Join(getlst, ",")
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
    End Select
End Sub
Function aGetList(irow) As Variant
    Dim Getlist
    aGetList = Array("#N/A")
    Dim cat: cat = Join(Array(Range("A" & irow), Range("b" & irow)), ",")
    Select Case cat
        Case "A,1"
            aGetList = Array("100", "200", "300", "400", "500")
        Case "A,2"
             aGetList = Array("600", "700", "800", "900")
        Case "B,1"
             aGetList = Array("110", "210", "310", "410", "510")
        Case "B,2"
             aGetList = Array("610", "710", "810", "910")
        Case Else
    End Select
End Function

 

[Sneaky Pete]

Thanks for the code, but it doesn't work in my case.

What I want is that the code works for the range A1:A26, range B1:B26 en G1:G26.
So cell A1 and B1 gives a validation list in G1, cell A2 and B2 gives a validation list in G2, etc.

Hope you can help me.

Thanx in advance.

Sneaky Pete

 

[tlowry]

Ok, I think we are getting closer. Try this code in the worksheet's module.

Const RangeToCheck = "A1:B26"
Const ColumnToChange = "G"

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ChangeRange, cCell
    Set ChangeRange = Application.Intersect(Range(RangeToCheck), Target)
    If Not ChangeRange Is Nothing Then
        For Each cCell In ChangeRange.Cells
            setVal cCell.Row
        Next cCell
    End If
End Sub

Sub setVal(irow)
    Dim getlst
    getlst = aGetList(irow)
    Select Case getlst(0)
        Case "#N/A"
            With ActiveSheet.Cells(irow, ColumnToChange)
                .Validation.Delete
                .Value = "#N/A"
            End With
        Case Else
            With ActiveSheet.Cells(irow, ColumnToChange)
                .Value = ""
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, Formula1:=Join(getlst, ",")
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            End With
    End Select
End Sub
Function aGetList(irow) As Variant
    Dim Getlist
    aGetList = Array("#N/A")
    Dim cat: cat = Join(Array(Range("A" & irow), Range("B" & irow)), ",")
    Select Case cat
        Case "A,1"
            aGetList = Array("100", "200", "300", "400", "500")
        Case "A,2"
             aGetList = Array("600", "700", "800", "900")
        Case "B,1"
             aGetList = Array("110", "210", "310", "410", "510")
        Case "B,2"
             aGetList = Array("610", "710", "810", "910")
        Case Else
    End Select
End Function

 

[Sneaky Pete]

Great!
Many,many thanks!
It works