Excel Find Method, Conditional Method? 1

[kjv1611]

Is there a way to use the Excel Find statement - or something like it, in a conditional way? Or should I just loop through the region I want to search, looking for it in other ways?

Here's an example of the Find Statement:

Cells.Find(What:="Monkey",After:=ActiveSheet.Cells("A1") _
                       , LookIn:=xlValues ,LookAt:=xlPart _
                       , SearchOrder:=xlByRows _
                       , SearchDirection:=xlNext, MatchCase:=False _
                       , SearchFormat:=False
                 ).Activate

Here's an example of how I might do it if I were just just straight look for the value:

For Each Cell in MyRange.Cells
  If Instr(Cell.Formula,"monkey") Or Instr(Cell.Formula,"mnky") Or Instr(Cell.Formula,"monky") Then
    Cell.Column.Select
    Selection.Format = "xlText"
  End If
Next Cell

And that latter code is probably incorrect, I just took to typing, didn't test it... just getting the idea out there.

So, is there a way to do an Excel find, otherwise, using multiple possible conditions? Not as joint conditions, but OR conditions?

Thanks for any ideas, references, links, etc.

 

[combo]

What about three separate Finds and Union results next?

combo

 

[SkipVought]

Hi,

  If InStr(Cell.Formula, "monkey") Or InStr(Cell.Formula, "mnky") Or InStr(Cell.Formula, "monky") Then
    Intersect(Cell.EntireColumn, ActiveSheet.UsedRange).NumberFormat = "@"
  End If

Skip,

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

 

[kjv1611]

Skip,

Does the UsedRange of the worksheet equal the same thing as ActiveCell.CurrentRange, or does it differ any?

But that does seem like exactly the route I'm trying to go! I'll have to give it a try and post back... hopefully I can test this before getting pulled away to something else.

 

[kjv1611]

Hmm... I think they'd be different... accomplishing similar, but different tasks. So, ignore my question.

 

[SkipVought]

Does the UsedRange of the worksheet equal the same thing as ActiveCell.CurrentRange, or does it differ any?

UsedRange is the range (min top-left to max bottom-right) on the sheet that has cells some property assigned, not necessarily a value.

CurrentRegion is a data range of contiguous cells.

They are different.

Skip,

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

 

[kjv1611]

Okay... so far, I think that will work... haven't fully tested yet.

But this now has me thinking... How can I tell Excel that when I'm using the InStr() function to not take into consideration the capitalization of the formula contents? Do I need to format the value to all lowercase or all uppercase in the If statement to be sure?

I tried messing with the compare options of the InStr function, but that seems to be going down the wrong path.

I'm looking in the mean time, thanks for any suggestions.

 

[kjv1611]

Okay, thanks skip on the range definitions. That is a big help. Too bad I can't give more than one pinky there.

 

[SkipVought]

Thx Steve!

Skip,

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

 

[kjv1611]

Okay... I got it! On the lowercase thing... just forgot, knew it was there...

I used the LCase function..

I'll share the whole thing once I've got it finished.

 

[kjv1611]

Woohoo! I'm a big part of the way there, now!

Here's what I've got... Well, first, why I'm doing it:

Where I work, we often copy/paste results from MS SQL or Access to Excel for quick analysis. For some of us, it happens multiple times in a day.

Well, dealing with various types of accounts, oftentimes, the account numbers are longer than whatever Excel's allowable "number" length is w/o going into scientific notation. So when you paste such records as that, if it doesn't auto-format itself beforehand as text, it'll go whacko after pasting it into Excel.

So I've had this idea for some time to build a macro that could "auto fix" the Acct Number Column, and make it suitable for use.

The first step is finding the column with the account number. The second step is formatting that column as text. Then the next step will be to re-paste the same range of data... which I'll get to later.

Here's what I've got for now - currently working:

Sub FixAcctNum2()
'testing skips method from
'[URL unfurl="true"]http://www.tek-tips.com/viewthread.cfm?qid=1656669&page=1[/URL]
Dim cell As Range

    For Each cell In ActiveCell.CurrentRegion
        If cell.Row <> ActiveCell.CurrentRegion.Row Then Exit For
        If InStr(LCase(cell.Formula), "acc") Then
            If InStr(LCase(cell.Formula), "num") Or InStr(cell.Formula, "#") Or InStr(LCase(cell.Formula), "no") Then
                If Len(ActiveSheet.Cells(cell.Row + 1, cell.Column).Formula) > 6 Then
                    Intersect(cell.EntireColumn, ActiveSheet.UsedRange).NumberFormat = "@"
                ElseIf Len(ActiveSheet.Cells(cell.Row + 1, cell.Column).Formula) = 0 Then
                    If Len(ActiveSheet.Cells(cell.Row + 1, cell.Column).Formula) > 6 Then
                        Intersect(cell.EntireColumn, ActiveSheet.UsedRange).NumberFormat = "@"
                    End If
                End If
            End If
        End If
    Next cell
    
End Sub

 

[kjv1611]

combo,

Out of curiosity, how could use use the 3 separate finds in the union you're talking about? All I can figure is setting a range to each one, and then comparing those results. But I'm probably missing something, since you said to union the results.

Can you put together an example? It might be useful some time.

Thanks.

 

[SkipVought]

Where I work, we often copy/paste results from MS SQL or Access to Excel for quick analysis

I often do ad hoc queries in Excel to Oracle or DB2, (occasionally Access) that precludes opening another application, setting criteria, copy, & paste into Excel. The entire process often takes just a few seconds.

I would avoid copy 'n' paste, as you get the problems you are trying to program around.

Skip,

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

 

[kjv1611]

Yeah, I realize that. I could change my habits that I've picked up, but I know I won't change the others' habits. I mean, I just mention the words, database, with some and they get squeemish. Or I talk about what's going on in a SQL script, and others get squeemish... and so on.

I keep thinking I need to make myself use MS Query in Excel on occasion, but I usually just skip it and do it the way I know to do - straight in the MS SQL SSMS (SQL Server Management Studio) window, and then paste the results.

Which, using the SSMS application has its advantages for sure as well. As of SQL 2008, MS includes the "auto text" features that VBA has had for quite some time. It's nowhere near as good as VBA, I think, but it works. Sometimes it gets rather annoying - hopefully they'll improve it for the next SQL release.

 

[SkipVought]

BTW, if your Account Number data column contains NUMERIC values in Excel, [red]changing the NumericFormat to TEXT changes NOTHING![/red] The underlying value remains numeric, dispite the format.

You must actually CONVERT the numbers to TEXT.

Skip,

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

 

[kjv1611]

Actually, these are the manual steps which have worked every time so far:
1. Paste in the data... see the acctnumber as a scientifically notated number.
2. Format the acctnumber column as text.
3. delete pasted data
4. Re-paste the same data.

After that, the AcctNumbers are now all in text, and even if I select the cell, click within the cell, and then on to something else, the format stays the same - text.

So, perhaps this way, it allows Excel to convert it to text?

What I would like to do, long-term, if I can find it possible is to basically build a macro that would become the "paste" for such issues. So instead of using the normal paste, the person would run the macro (button) and it would look at the data on the clipboard, see the acctNumber, and format the Excel destination accordingly BEFORE the paste.

But this method seemed like the easiest/quickest fix for the moment.

I'm definitely open to any ideas on what I was thinking of for a long-term better fix, perhaps. Though, I'd imagine, could end up needing the other anyway, b/c I doubt folks would always remember to use a macro vs standard copy/paste..

Seems silly, I know.

Here's another qeustion: I've built a query before in MS Query for Excel... but is there a way to already have the SQL for a query built, and just paste it into a query window for MS query, and it just run? If I knew it before, I've just forgotten it.

Thanks again for the help.

 

[SkipVought]

[tt]
2. Format the acctnumber column as text.
3. delete pasted data
4. Re-paste the same data.
[/tt]
THAT is totally different! That does work, obviously.
Are you doing all this copy 'n' paste for a ONE TIME thing, or do you repeatedly paste over this 'template' once created?

You REALLY need to query!

SQL is not SQL. You probably cannot just paste some other sql into the MS Query sql, just as you probably can do that in Access either.

Skip,

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

 

[kjv1611]

No, it's never done in a template, b/c that field is not always the same size, nor is it always in the same location in the data source.

Yes, querying from MS query would be more ideal. Unfortunately, it's not easy to think of all the possibilities up front (as I've learned over the past 8 or so months), b/c the requirements and such are constantly changing where I'm working. And it's not from poor planning or management. It's just that we're working in an area that has a lot of fluctuation at times.

Those things that are run on a regular basis, it's no biggie. However, much of this is on an ad-hoc basis.

The copy/paste thing is not on the same workbook practically ever. Some data will need to be analyzed quickly, so it's queried in MS SSMS, and then pasted to Excel. Or on occasion, a couple of the folks may do those queries in Excel.

But yes, building the queries directly into Excel would be ideal. I will try to put some effort in there, maybe on getting a couple default templates built that way, and then teaching the others on how to modify the queries there to fit their needs. We'll see if it'll stick. In my case, I don't think it's so much that folks have a desire to not change (thankfully, oddly), but rather that everyone is so busy that it isn't always feasible to take the time to teach/learn different methods.

Anyway, thanks for the tips. Hopefully, we'll get to work the use of MS Query into the equation.