Posts by cbanks

Re: Search In a Macro/VBA

i cant get that too work.. here is what i am using that is working. The problem is my search range is the entire workbook not just one column. basically i am searching in column a-z and rows 1-3000. and my problem is my criteria is about 15 different options which means if i cant find a fix im going to have to do about 45000 of these and im gonna have to go through and change each j2 to j3, j4, etc.. the same with q.

If InStr(UCase(Range("J2").Value), "WARN") Then
    Range("Q2") = "Warning"
    End If
If Range("L2") = "2086" Then
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "Warning"

Re: Search In a Macro/VBA

Im looking in for values in more than one column.. Sometimes in column l, sometimes in column N, if either of them match my criteria (which i have several criteria), then i need it to put a warning in the that same row on column q.

Yes I got the rules post.

Re: Search In a Macro/VBA

I would love to show you an example workbook but it is confidential and im afraid i would lose my job so i will try to explain things a little better.

The workbook has 3000 rows full of info. I need it to search a certain column and if it finds the critera to post a message in the corresponding cell in column q. Hope that helps. Thanks again for your help

Re: VBA String for contains.

o you are my hero.. i posted another issue im having with searching in a macro.. basically i had this really long formula (see below) and i needed to add alot more criteria too it but it was telling my i couldnt add over 7 functions to a formula.. i cant recall the exact error.. so i decided to just build this macro in VBA. My question i posted is below also

Formula in excel:
=IF(ISNUMBER(SEARCH("WARN",J58)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N58)),"Sales",IF(ISNUMBER(SEARCH("WARN",L58)),"Warning",IF(ISNUMBER(SEARCH("2699",L58)),"Warning",IF(ISNUMBER(SEARCH("4004",L58)),"Warning",IF(ISNUMBER(SEARCH("2036",L58)),"Warning",""))))))

Posted question:
It sounds simple but its really causing me problems.. Here is what I am
currently having to do..
If InStr(UCase(Range("J2").Value), "WARN") Then
Range("Q2") = "Warning"
End If
If Range("N2") = "PSSNAP" Then
Range("Q2").Select
ActiveCell.FormulaR1C1 = "Sales"
End If

Ok. This list goes on an on.. I basically have about 15 more strings like
this to add on.. Is there not away to have the macro search through the
columns and have it input my answers on the corresponding cell in column "Q"
if the data from column J/L matches what i need? I need help on this bad.
ASAP. Thanks a ton!

It sounds simple but its really causing me problems.. Here is what I am
currently having to do..
[vba]If InStr(Range("J2").Value, "Warn") Then
Range("Q2") = "Warn"
End If
If Range("N2") = "PSSNAP" Then
Range("Q2").Select
ActiveCell.FormulaR1C1 = "Sales"
End If
If Range("L2") = "2669" Then
Range("Q2").Select
ActiveCell.FormulaR1C1 = "Warning"
End If[/vba]
Ok. This list goes on an on.. I basically have about 15 more strings like
this to add on.. Is there not away to have the macro search through the
columns and have it input my answers on the corresponding cell in column "Q"
if the data from column J/L matches what i need? I need help on this bad.
Thanks a ton!

Re: VBA String for contains.

thanks that worked, but its still specific on whether its caps or not caps..

Here is what i have.. Sometimes what the range contains is Warning, Warning 101, Warning 102, etc.. I need it to pick up everytime Warning is used.. but i do not know what the string for contains is.. Basically if the cell contains any sort of warning i want it to post warn somewhere else.

If Range("J2") <> "Warn" Then
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "Warn"