Check multiple offset

[Ciccio86]

Hi, i have this code and im tryng to add another offset to check but doesn't work
For example the -2 Offset has "BlaBla 345W" i want to check if 345W is in the column E of sheet List
In BOLD the part that im tryng to add

Sub Listing(chk As OLEObject)

Dim ViewSheet As Worksheet
Dim ListSheet As Worksheet
Dim ViewValue As Variant
Dim LeftWValue As Variant
Dim ValueFound As Boolean
Dim ValueExpired As Boolean
Dim ValueW As Boolean
Dim i As Long
 

    Set ViewSheet = ThisWorkbook.Sheets("View")
    Set ListSheet = ThisWorkbook.Sheets("List")
 
    If TypeName(chk.Object) = "CheckBox" Then
        ViewValue = chk.TopLeftCell.Offset(0, -1).Value
        LeftWValue = chk.TopLeftCell.Offset(0, -2).Value Like "*W*"  
        ValueFound = False
        ValueExpired = False
        ValueW = False
     
        For i = 1 To ListSheet.Range("A1").End(xlDown).Row
            If ListSheet.Range("B" & i).Value = ViewValue Then
                ValueFound = True
                If ListSheet.Range("E" & i).Value = LeftWValue Then
                    ValueW = True
                    If ListSheet.Range("C" & i).Value < Date Then
                        ValueExpired = True
                Else
                    ValueExpired = False
                End If
            End If
          End If
        Next i
    End If
 
    If ValueFound = True And ValueW = True And ValueExpired = False Then
        chk.TopLeftCell.Offset(0, 1).Value = "OK"
    Else
        MsgBox "Not in the List"
        chk.Object = False
        chk.TopLeftCell.Offset(0, 1).Value = "NOT OK"
    End If

End Sub

 

[rlv01]

"Doesn't work" isn't very informative. LeftWValue is a True/False boolean which indicates the presence of "W" a cell value. Then you take that boolean value and try compare it to a presumably non-boolean value: If ListSheet.Range("E" & i).Value = LeftWValue Then, Why?

Using your "BlaBla 345W" example and making a lot of guesses, maybe you need something like this instead

LeftWValue = Right(chk.TopLeftCell.Offset(0, -2).Value,4)

If InStr(ListSheet.Range("E" & i).Value, LeftWValue) > 0 Then

 

[Ciccio86]

"Doesn't work" isn't very informative. LeftWValue is a True/False boolean which indicates the presence of "W" a cell value. Then you take that boolean value and try compare it to a presumably non-boolean value: If ListSheet.Range("E" & i).Value = LeftWValue Then, Why?

Using your "BlaBla 345W" example and making a lot of guesses, maybe you need something like this instead

LeftWValue = Right(chk.TopLeftCell.Offset(0, -2).Value,4)

If InStr(ListSheet.Range("E" & i).Value, LeftWValue) > 0 Then

I don't need a right because the W value can be in a text "BlaBla 345W blabla"

So I have to check if the W value is in the cell and then check if that value is in a table on another sheet

 

[rlv01]

It appears you are trying to extract a substring from a larger string based on some kind of criteria or rule then and then test for the presence of the substring in other cells. What is the criteria for extracting the substring from the larger string? Instead of "BlaBla 345W" perhaps you should provide a few actual examples?

 

[Ciccio86]

It appears you are trying to extract a substring from a larger string based on some kind of criteria or rule then and then test for the presence of the substring in other cells. What is the criteria for extracting the substring from the larger string? Instead of "BlaBla 345W" perhaps you should provide a few actual examples?

For example:
Column A---------------------------------------Column B
"The data is 345W from the lap"--"345W 30L"

I need to check if value in in column B is the same of column A and then check if is in a table on another sheet

 

[rlv01]

Maybe

        'LeftWValue = chk.TopLeftCell.Offset(0, -2).Value Like "*W*"
        LeftWValue = GetSearchTerm(chk.TopLeftCell.Offset(0, -2).Value)

and

                'If ListSheet.Range("E" & I).Value = LeftWValue Then
                If LeftWValue <> "" And InStr(ListSheet.Range("E" & I).Value, LeftWValue) > 0 Then

Where GetSearchTerm is

Function GetSearchTerm(ByVal S As String) As String
    Dim SA As Variant, I As Long

    SA = Split(S, " ")
    S = ""
    For I = 0 To UBound(SA)
        If (Right(SA(I), 1) = "W") And IsNumeric(Left(SA(I), 2)) Then
            S = SA(I)
            Exit For
        End If
    Next I
    GetSearchTerm = S
End Function

 

[Ciccio86]

Maybe

        'LeftWValue = chk.TopLeftCell.Offset(0, -2).Value Like "*W*"
        LeftWValue = GetSearchTerm(chk.TopLeftCell.Offset(0, -2).Value)

and

                'If ListSheet.Range("E" & I).Value = LeftWValue Then
                If LeftWValue <> "" And InStr(ListSheet.Range("E" & I).Value, LeftWValue) > 0 Then

Where GetSearchTerm is

Function GetSearchTerm(ByVal S As String) As String
    Dim SA As Variant, I As Long

    SA = Split(S, " ")
    S = ""
    For I = 0 To UBound(SA)
        If (Right(SA(I), 1) = "W") And IsNumeric(Left(SA(I), 2)) Then
            S = SA(I)
            Exit For
        End If
    Next I
    GetSearchTerm = S
End Function

With this it will search only if has W value inside.

I didn't explain myself well, the cell doesn't always have the value "W" inside

Sorry i will try to explain better.

When i run the sub it will check if chk.TopLeftCell.Offset(0, -1).Value is in the sheet "List" table and then check if is expired.
Now im tryng to check if a part of the value chk.TopLeftCell.Offset(0, -2).Value is the same of A column on sheet "List".

Sheet View

Sheet List

 

[Ciccio86]

Im tryng with Instr but i have error with

If InStr(Partial_Text, rng)

The error appears as “run-time error 13 – Type mismatch”

Sub Test(chk As OLEObject)

Dim ViewSheet As Worksheet
Dim ListSheet As Worksheet
Dim Partial_Text As Variant
Dim DataFound As Boolean

Dim rng As Range


Set ViewSheet = ThisWorkbook.Sheets("View")
Set ListSheet = ThisWorkbook.Sheets("List")
Set rng = ListSheet.Range("B:B")

    If TypeName(chk.Object) = "CheckBox" Then
        Partial_Text = chk.TopLeftCell.Offset(0, -1).Value
        
        DataFound = False

For i = 1 To ListSheet.Range("A1").End(xlDown).Row
    If InStr(Partial_Text, rng) <> 0 Then
        DataFound = True
        End If
    Next i
End If

If DataFound = True Then
    MsgBox "Correct"
Else
    MsgBox "Incorrect"
End If
End Sub

 

[rlv01]

With this it will search only if has W value inside.

No, it will always search. It will only return a result if there is string beginning with numbers and ending with "W" inside. If it does not find that, it returns an empty string "".

Book2
	A	B
1	The data is 345W from the lap	345W
2	The data is 934Z from the lap
3	The data is 934W from the lap	934W
View

Cell Formulas
Range	Formula
B1:B3	B1	=GetSearchTerm(A1)

Im tryng with Instr but i have error with

If InStr(Partial_Text, rng)

The error appears as “run-time error 13 – Type mismatch”

That won't work because rng is a range variable referencing multiple cells (Set rng = ListSheet.Range("B:B")). Instr requires two parameters that resolve to type string.

For i = 1 To ListSheet.Range("A1").End(xlDown).Row
    If InStr(Partial_Text, rng) <> 0 Then
        DataFound = True
        End If
    Next i
End If

This loop is non-functional because you are not using the looping variable i to do anything inside the loop.

FWIW, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others. this free tool instead to post some sample data.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Ciccio86]

Fixed using

If InStr(1,Partial_Text, ListSheet.Range("A" & i).Value, vbBinaryCompare) Then
DataFound = True

Instead Of

If InStr(Partial_Text, rng) <> 0 Then
DataFound = True