Trouble with wildcard "*.*"

[jhogie]

I have an excel file where in Column B there are various strings that all start with "CD-0". I am trying to enter my 'if statement' if the cell starts with "CD-0".

Note: the cells that start with "CD-0" are all several cells in the B column merged together (so it should read it in the top cell).

The following is my code;

For k = 5 To NumRows
    If wkb.Worksheets("MESL").Cells(k, 2) = ("CD-0" & "*.*") Then
        wkb.Worksheets("Summary - Hours").Cells((i + 2), 2) = wkb.Worksheets("MESL").Cells(k, 2)
        MsgBox (wkb.Worksheets("MESL").Cells(k, 2) & " " & k)
        i = i + 1
    End If
Next k

Note: In my 'IF statement' if I simply put "CD-009" (which is the string in one of the cells), it works.

You can ignore the stuff in the 'If statement'

Thanks in advance,

- Jeff

 

[PHV]

Either:
If Left(wkb.Worksheets("MESL").Cells(k, 2), 4) = "CD-0" Then
Or:
If wkb.Worksheets("MESL").Cells(k, 2) Like "CD-0*.*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Also check out the AutoFilter. You could filter on criteria operator BEGINS WITH and criteria Left(x,4) as PHV described. COPY the visible cell of data in that column and PasteSpecial xlPasteValues in the next empty row in column B on Sheet Summary - Hours in one fell swoop, without a For...Next.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jhogie]

Thanks PHV!

This one still did not work:
If wkb.Worksheets("MESL").Cells(k, 2) Like "CD-0*.*" Then

But, this seemed to do the job (should have thought of this myself):
If Left(wkb.Worksheets("MESL").Cells(k, 2), 4) = "CD-0" Then

Thanks Skip - I had thought of going that direction, but I am less confident with those commands.

I haven't used Tek-Tips in about 2 years, but remember both of you being super helpful before ... glad to see you haven't lost your touch!

- Jeff

 

[PHV]

And this ?
If wkb.Worksheets("MESL").Cells(k, 2) Like "CD-0*" Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jhogie]

Yes this does work!

If wkb.Worksheets("MESL").Cells(k, 2) Like "CD-0*" Then