VBA Cell Value

[Pinaceous]

Hi All,

I'm trying to provide a sub where if it finds a specific text (for example "BookOne.xlsx") in Range("B:B") Then it will provide a MsgBox "Positive result" for all of its entries.

Likewise, if it does not find the text I'd like it to provide only one MsgBox "Negative result" pop-up and not a pop-up for every entry.

Sub string_validation()

    Dim cel As Range

    For Each cel In Range("B:B")
        If cel.Value = "BookOne.xlsx" Then
            MsgBox "Positive result"
            
            Workbooks.Open cel.Offset(, 1)
              
        Else
            MsgBox "Negative result"
        End If
    Next cel

End Sub

Please lend a hand.

Thank you!
pinaceous

 

[YasserKhalil]

Avoid using loops in that case. You can either use Application Match

Sub Test_Match()
    Const sTxt As String = "BookOne.xlsx", colToCheck As Long = 2
    Dim x
    With ActiveSheet
        x = Application.Match(sTxt, .Columns(colToCheck), 0)
        If Not IsError(x) Then
            MsgBox "Positive Result"
            Workbooks.Open .Cells(x, colToCheck + 1).Value
        Else
            MsgBox "Negative Result"
        End If
    End With
End Sub

Or you can use CountIf

Sub Test_CountIf()
    Const sTxt As String = "BookOne.xlsx", colToCheck As Long = 2
    Dim cnt As Long
    With ActiveSheet
        cnt = Application.WorksheetFunction.CountIf(.Columns(colToCheck), sTxt)
        If cnt > 0 Then
            MsgBox "Positive Result"
            Workbooks.Open .Cells(.Columns(colToCheck).Find(sTxt).Row, colToCheck + 1).Value
        Else
            MsgBox "Negative Result"
        End If
    End With
End Sub

 

[Trebor76]

Hi pinaceois,

Here's my attempt:

Option Explicit
Sub Macro1()

    Dim rngCell As Range
   
    Application.ScreenUpdating = False

    If Application.WorksheetFunction.CountIf(Range("B:B"), "BookOne.xlsx") = 0 Then
        MsgBox "Negative Result"
    Else
        For Each rngCell In Range("B1", Range("B" & Rows.Count).End(xlUp))
            If Len(rngCell) > 0 And Len(rngCell.Offset(0, 1) > 0) Then
                Workbooks.Open rngCell.Offset(0, 1)
            End If
        Next rngCell
        MsgBox "Positive Result"
    End If
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Pinaceous]

Good Day Trebor76,

Yes, I used a part of your sub and run it after the first sub determines if there is a positive result or not and it works well.

Thank you!
pinaceous

 

[Pinaceous]

Avoid using loops in that case. You can either use Application Match

Sub Test_Match()
    Const sTxt As String = "BookOne.xlsx", colToCheck As Long = 2
    Dim x
    With ActiveSheet
        x = Application.Match(sTxt, .Columns(colToCheck), 0)
        If Not IsError(x) Then
            MsgBox "Positive Result"
            Workbooks.Open .Cells(x, colToCheck + 1).Value
        Else
            MsgBox "Negative Result"
        End If
    End With
End Sub

Or you can use CountIf

Sub Test_CountIf()
    Const sTxt As String = "BookOne.xlsx", colToCheck As Long = 2
    Dim cnt As Long
    With ActiveSheet
        cnt = Application.WorksheetFunction.CountIf(.Columns(colToCheck), sTxt)
        If cnt > 0 Then
            MsgBox "Positive Result"
            Workbooks.Open .Cells(.Columns(colToCheck).Find(sTxt).Row, colToCheck + 1).Value
        Else
            MsgBox "Negative Result"
        End If
    End With
End Sub

Thank you

YasserKhalil !!!​

 

[Pinaceous]

Thank you

YasserKhalil !!!​

Out of curiosity, what happens when there is more than one found entry?

For example, if there are two or more "BookOne.xlsx" on the list.

How can this best be approached?

What do you think?

 

[YasserKhalil]

The code will check for the first occurrence only.

 

[Trebor76]

Mine loops through all cells in Col. B and will open the workbook in Col. C if that helps.

 

[Pinaceous]

Hey thanks guys!

It all helps and it is always appreciated!

Thanks again!
-p