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]

not even that. should've been colLetter & Cells(Rows.Count, 1).End(xlUp).Row

 

[steve the fish]

You mean .Cells(.Rows etc.

 

[taikvei]

I'm sorry - I'm pretty new at VBA and have done most of everything via googling/macro recorder and gluing stuff together. Could you explain why I need the dots? It seems to be working without them?

 

[John_w]

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

It depends where you put the code. In a standard module, Cells(...) refers to the active sheet and .Cells(...) refers to the With cTeam sheet. So if the cTeam sheet isn't active Cells(...) is looking at the wrong sheet. In a sheet module, Cells(...) refers to that sheet, regardless of the active sheet.

 

[taikvei]

okay, that makes sense.

Thanks a lot!