Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Search in macro question (Excel XP)

    Hi again!

    I've used the following statement in a number of macros with good success. The idea is that if the state "CT" shows up in the address line then I get flagged with a message box reminding me to check to see if CT sales tax applies.

    I've done the same thing with other worksheets for the word "layer" when we have multiple layer products and only want an identification label for the entire moulding to print instead of one for each layer. Normally it would print an ID label for each layer, but this statement below prevents duplicate labels from being printed.

    In this case though it is only working if CT is the ONLY thing entered in a cell. In my invoice the town, state and zip all appear in the same cell.

    So, if E11 contains "CT" the message box appears. But if E11 contains " Thompson, CT" it does not appear. In my "layer" example the statement works if the corresponding cell says "Layer", "LayerA", "layerB" etc.

    I can't figure out why this isn't working just the same for "ct".

    If InStr(LCase(Range("E11")), "ct") = 1 Then
    MsgBox ("This is a CT customer and may be taxable.")
    End If

    Thanks,
    BH

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search in macro question (Excel XP)

    If you change the line

    If InStr(LCase(Range("E11")), "ct") = 1 Then

    to

    If InStr(LCase(Range("E11")), "ct") > 0 Then

    it will detect the presence of the letter combination CT anywhere in the cell. Warning: it will also detect the letters CT in a word such as "Protection" or "Active"!

  3. #3
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search in macro question (Excel XP)

    Well, I've done a bit more experimenting to see the differences between this situation and the one with the word "layer".

    It seems the search term "ct" needs to be the first letters in the cell in order to be found. That is always the case when I'm searching for the word "layer" in the other worksheet so I tried it with "ct" being the first letters in E11 and it worked as it should. That is, "Thompson, CT" does not work, but "CT, Thompson" does work.

    Obviously CT cannot be the first letters in my cell when the city, state and zip are all combined into one cell.

    So, is there another way to make "ct" be found even when it is NOT the first letters in the cell?

    Thanks,
    BH

  4. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    CT
    Posts
    361
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search in macro question (Excel XP)

    Thanks Hans. You were posting the answer to my question even as I was posting the newly discovered details.

    I expected to have the message come up even when CT shows up in a city, town name. For the rare time that might happen I can certainly live with it if if keeps my wife off my butt for not adding sales tax to invoices when I should have done so!!!

    Thanks again!!

    BH

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Search in macro question (Excel XP)

    See my previous reply. The test Instr(...) = 1 only checks whether the search text ("label" or "ct") is found in the 1st position. If you change it to Instr(...) > 0, you check whether the search text occurs in any position.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •