VBA if x like... returning false

[taikvei]

Hi there,

I have the below bit of code. In my head, it should loop through columns 1 to 52 of row 8 of the spreadsheet assigned to cTeam, and return True if the value is like "tel".

The values in those cells are Contact Tel. Contact Tel. 2 Home Tel. Home Tel. 2 etc., but the Like operator keeps returning false - am I doing it wrong somehow? I've tried having it as "*Tel*" as well as what it is now, but it hasn't helped. I've stepped through all 52 iterations with F8 and it hasn't picked up any of the columns with the Tels

With cTeam

    For logCol = 1 To 52 Step 1
    colLetter = Split(Cells(1, logCol).Address, "$")(1)
        If Cells(8, logCol).Value Like "*" & "Tel" & "*" Then
        cTeam.Range(colLetter & "8" & ":" & logCol).SpecialCells(xlCellTypeVisible).Copy
        cSheet.Cells(1, cSheetCol).PasteSpecial Paste:=xlPasteValues
        cSheetCol = cSheetCol + 1
        End If
    
    Next logCol
End With

 

[taikvei]

Sorry, I forgot to mention it - those cells are the ones that the filter arrows are on - could that potentially be a problem?
I tried googling but was only able to find guides on how to use the Like operator and examples, but not anything that would troubleshoot why it returns false.

 

[Fluff]

Does the word "Tel" appear anywhere in row 8?

 

[taikvei]

Row 8 is where the header columns for the data below are. There's roughly 6 headers that are a variation of Contact Tel. or Home Tel., but they all have "Tel" in them.

 

[steve the fish]

Are you sure its Tel and not tel or TEL

 

[John_w]

You have unqualified Range references inside With cTeam, so without the dot qualifier these ranges refer to the active sheet, not cTeam. Fix like this:

   colLetter = Split(.Cells(1, logCol).Address, "$")(1)
        If .Cells(8, logCol).Value Like "*Tel*" Then

Also note that Like is case-sensitive, so "Contact tel." doesn't match "*Tel*". Use LCase on the left side of the Like and match against "*tel*" if it must be case-insensitive:

        If LCase(.Cells(8, logCol).Value) Like "*tel*") Then

 

[taikvei]

Here's a screenshot of what these specific

column headers in that row are
https://imgur.com/a/VPB6G9j

 

[taikvei]

You have unqualified Range references inside With cTeam, so without the dot qualifier these ranges refer to the active sheet, not cTeam. Fix like this:

   colLetter = Split(.Cells(1, logCol).Address, "$")(1)
        If .Cells(8, logCol).Value Like "*Tel*" Then

Also note that Like is case-sensitive, so "Contact tel." doesn't match "*Tel*". Use LCase on the left side of the Like and match against "*tel*" if it must be case-insensitive:

        If LCase(.Cells(8, logCol).Value) Like "*tel*") Then

I've tried doing that, and the code block now spits out an error. Here's what it looks like at the moment.

With cTeam

    For logCol = 1 To 52 Step 1
    colLetter = Split(.Cells(1, logCol).Address, "$")(1)
        If .Cells(8, logCol).Value Like "*" & "Tel" & "*" Then
        cTeam.Range(colLetter & "8" & ":" & logCol).SpecialCells(xlCellTypeVisible).Copy '<-- error is here
        cSheet.Cells(1, cSheetCol).PasteSpecial Paste:=xlPasteValues
        cSheetCol = cSheetCol + 1
        End If
    
    Next logCol
End With

It gives me an error saying Method 'Range' of object '_Worksheet' failed. I tried taking the cTeam out of that line, leaving just .Range(colLetter..................., but still the same error.

 

[steve the fish]

This isnt valid:

colLetter & "8" & ":" & logCol

It resolves to A8:1

when logCol is 1. What is it meant to be?

 

[taikvei]

Oh wow. You are right. It should've been colLetter & logCol. It works now.
such a silly mistake that cost me the last 3-4 hrs of racking my brain..

Any idea why it never popped up with an error before? which is why I thought the issue was with the Like operator?