1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2003 Like Operator

Discussion in 'Business Applications' started by computerman29642, Apr 4, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Does anyone know how to use the LIKE operator in an Excel macro? As far as I know, wildcards cannot be used in an Excel macro, so I thought the next best thing would be the LIKE operator.

    What I am trying to do is take the following code....

    Code:
    For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
            If Cells(r, 1) = "" Then Rows(r).Delete
        Next r
    
    Instead of it deleting rows that are blank, I was hoping change the following line:

    Code:
    If Cells(r, 1) = "" Then Rows(r).Delete
    
    TO

    Code:
    If Cells(r, 1) = LIKE(ac) OR Like(Sample)  Then 
    'Do Nothing
    Else
    Rows(r).Delete
    
    If anyone has any suggestions, they will be greatly appreciated.
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    take the equals out before LIKE, and use quotes around the text to compare. You need the wildards for it to work


    Public Sub test()

    Dim x As Integer

    For x = 1 To 10

    If Range("A" & x).Value Like ("AC*") Or Range("A" & x).Value Like ("SAMPLE") Then

    Else

    Rows(x).Delete

    End If

    Next


    End Sub
     
  3. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Ziggy,

    Thank you for the reply. I will give a shot, and let you know how it works.
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    actually I should have put it the way you had...


    Code:
    Public Sub test2()
    
    
    For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    
    
            If Cells(r, 1) Like ("AC*") Or Cells(r, 1) Like ("SAMPLE") Then
            
            Else
            
            Rows(r).Delete
            
            End If
            
        Next r
    
    
    
    End Sub
     
  5. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    This works great. Thanks very much Ziggy.
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    hi Chris. Is this like the issue we were discussing on your workbook the other day and the formulas which evaluate to null? if so, we'll be able to work with this with the dynamic range of formulas and not need an expensive loop. Try to stay away from loops like we were talking about. I'll await your response for further.

    Sent via mobile device.
     
  8. computerman29642

    computerman29642 Thread Starter

    Joined:
    Dec 4, 2007
    Messages:
    2,895
    Zack,

    Yes & No. The code I was asking about here does not really tie in with the workbook, but I needed it to setup a file that will be copied into the workbook. The machine that created the file places a lot of extra lines and headers that are not needed.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/700267

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice