CODEWITHSUNDEEP
excelvba
Symorix
Symorix

Reputation: 15

VBA - If statement that references specific text in a cell in a different worksheet

I'm trying to build logic where if a specific cell in a specific worksheet is populated with a specific string, it skips running that sub and goes on to the next one. What I think is my closest attempt so far from some research:

        Dim Rng_1 As Range
        Dim x_sheet As Worksheet
        Dim Value_X

        Value_X = "<>Could not generate report for*"
        Set x_sheet = ActiveWorkbook.Worksheets("worksheet_name")
        Set Rng_1 = x_sheet.Range("B8")
            If Rng_1.value = Value_X Then
           'run code
        End If
       'It should continue from here if the referenced cell did contain the string.

Running the above always skips the code in between regardless of how I edit the does not contain value or even if I add "else if". At this point, i'm not even sure if I'm on the right track.

Referenced Sheet
enter image description here

Upvotes: 1

Views: 876

Answers (2)

Samuel Hulla
Samuel Hulla

Reputation: 7089

From the looks of the data you provided, you can reverse-engineer your problem, to check instead, whether data is in correct format (numbers only)

Private Sub loop_though()

  Dim ws as Worksheet: set ws = Sheets("worksheet_name")
  Dim cell as Range
  Dim looprange as Range: Set looprange = ws.Range("B2:B10")

  For each cell in looprange
       If Not IsNumeric(cell) Then 'cell does not contain only numbers
          ' further code here
       End If
  Next cell

End Sub

Alternatively, if you insist on checking for the "value_x" within the cells, then this would be the solution.

Private Sub loop_though()

  Dim ws as Worksheet: set ws = Sheets("worksheet_name")
  Dim cell as Range
  Dim looprange as Range: Set looprange = ws.Range("B2:B10")
  Dim value_x as String: value_x = "Could not generate report for"

  For each cell in looprange
       If InStr(1, cell, value_x) <> 0 Then
          ' further code here
       End If
  Next cell

End Sub

Upvotes: 0

user11314630
user11314630

Reputation:

The Like comparison operator needs to be used when dealing with wildcards. The 'no match' for this is Not this Like that. You don't add the <> operator to produce 'does not match'.

You need Else if you want plan A and plan B.

    Value_X = "Could not generate report for*"  'removed <>
    Set x_sheet = ActiveWorkbook.Worksheets("worksheet_name")
    Set Rng_1 = x_sheet.Range("B8")
    If Not Rng_1.value Like Value_X Then
       Debug.Print "msg not found"
       'run code
    Else
       Debug.Print "msg found"
       'It should continue from here if the referenced cell did contain the string.
    End If

fwiw, from your sample data it looks like you could also test for IsNumeric(Rng_1.value).

Upvotes: 2

Related Questions