Look for multiple values

[Foxy Lady]

Hi there,

I need vba code for when I press a button in sheet 1 (Look for balance,over,split) it will go through the sheet to find a balance split and over

It needs to find all three of these items other wise a message box should appear stating please enter balance or please enter split or please enter over.

Basically a message box for each item not appearing.

And then if the criteria is met a message box to say Correct

Can this be done.

Hope someone can help

Regards

 

[snowblizz]

Can you be more specific.

Are you looking for the text "balance" and "over" and "split", or for a number that corresponds to these?

 

[Sandeep Warrier]

Hi there,

I need vba code for when I press a button in sheet 1 (Look for balance,over,split) it will go through the sheet to find a balance split and over

It needs to find all three of these items other wise a message box should appear stating please enter balance or please enter split or please enter over.

Basically a message box for each item not appearing.

And then if the criteria is met a message box to say Correct

Can this be done.

Hope someone can help

Regards

Looks like you need to search your sheet for the 3 options and display relevant message boxes.

Try

Sub Test()
    Dim Rng As Range, Rng1 As Range, Rng2 As Range
    With ActiveSheet.UsedRange
        Set Rng = .Find(What:="balance", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set Rng1 = .Find(What:="split", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set Rng2 = .Find(What:="over", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not (Rng Is Nothing And Rng1 Is Nothing And Rng2 Is Nothing) Then
            MsgBox "Correct"
        ElseIf Not (Rng Is Nothing And Rng1 Is Nothing) Then
            MsgBox "Please enter Over"
        ElseIf Not (Rng Is Nothing And Rng2 Is Nothing) Then
            MsgBox "Please enter Split"
        ElseIf Not (Rng1 Is Nothing And Rng2 Is Nothing) Then
            MsgBox "Please enter balance"
        Else
            MsgBox "Please enter Balance, Split and Over"
        End If
    End With
End Sub

<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Foxy Lady]

Hi there Sandeep,

Thanks for the code, however I can't seem to get it to work correctly.

When i only enter a 'over' it gives me a correct (Same with balance and split)

I need the code to when there all three is there give me "correct"

It also doesn't give me when 'over and split' is there the msg box "please add balance. (the same with other combinations.)

Will it be possible to just look in certain rows for that option say rows: 16,25.34.43.53.

Thanx in advance

 

[Sandeep Warrier]

Hi there Sandeep,

Thanks for the code, however I can't seem to get it to work correctly.

When i only enter a 'over' it gives me a correct (Same with balance and split)

I need the code to when there all three is there give me "correct"

It also doesn't give me when 'over and split' is there the msg box "please add balance. (the same with other combinations.)

Will it be possible to just look in certain rows for that option say rows: 16,25.34.43.53.

Thanx in advance

Hi Foxy Lady...

This should work. Edited it to only look in Rows 16, 25, 34, 43 and 53

Sub Test()
    Dim Rng As Range
    Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
    Set Rng = Range("16:16,25:25,34:34,43:43,53:53")
    With Rng
        Set Rng1 = .Find(What:="balance", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set Rng2 = .Find(What:="split", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        Set Rng3 = .Find(What:="over", After:=.Cells(.Rows.Count, 1), LookIn:=xlValues, LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If (Not Rng1 Is Nothing) And (Not Rng2 Is Nothing) And (Not Rng3 Is Nothing) Then
            MsgBox "Correct"
        ElseIf (Not Rng1 Is Nothing) And (Not Rng2 Is Nothing) Then
            MsgBox "Please enter Over"
        ElseIf (Not Rng1 Is Nothing) And (Not Rng3 Is Nothing) Then
            MsgBox "Please enter Split"
        ElseIf (Not Rng2 Is Nothing) And (Not Rng3 Is Nothing) Then
            MsgBox "Please enter balance"
        ElseIf (Not Rng1 Is Nothing) And (Rng2 Is Nothing) And (Rng3 Is Nothing) Then
            MsgBox "Please enter split and over"
        ElseIf (Rng1 Is Nothing) And (Not Rng2 Is Nothing) And (Rng3 Is Nothing) Then
            MsgBox "Please enter balance and over"
        ElseIf (Rng1 Is Nothing) And (Rng2 Is Nothing) And (Not Rng3 Is Nothing) Then
            MsgBox "Please enter balance and split"
        Else
            MsgBox "Please enter Balance, Split and Over"
        End If
    End With
End Sub

 

[Foxy Lady]

Sandeep,

Thanx a lot it works perffect!!!

What do I do if balance split or over stands after someting ex. (CJ2)Balance

 

[Sandeep Warrier]

Sandeep,

Thanx a lot it works perffect!!!

What do I do if balance split or over stands after someting ex. (CJ2)Balance

I'm glad it worked.

Try changing the

LookAt:=xlWhole to LookAt:=xlPart in the .Find portions of the code.

 

[Foxy Lady]

Perfect. Thanx a lot!!!!!!!!!!!!!

 

[Foxy Lady]

Sandeep please see my other post ( placing picture when certain criteria is met)