Find string in sheet and pass location to variable?

[YellowLabPro]

Searching for a keyword in a sheet, (the first instance of it by row count), "Style", for example and pass the column number to a variable.
Also, any sort of characters after would be ignored, example is "Style Name" would be ok, so my thinking is that if a wildcard search is possible, i.e. "Style*"
How do I write this and what will the variable be Dim'd as?

Thanks-

YLP

[mdmackillop]

Hi Doug,
A good use for the macro recorder, Macro1, then modified to what you need, Macro 2

Sub Macro1()
Cells.Find(What:="Style", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate
End Sub

Sub Macro2()
Dim Col As Long
Col = Cells.Find(What:=InputBox("Enter search term"), After:=Range("A1"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Column
MsgBox "Found in column " & Col

End Sub

[YellowLabPro]

Good Point Malcolm,
I am still not using the macro recorder as a tool, mental block there or something.... who knows- I really think it had to do w/ me whacking too many soccerballs off the head all those years.....

[unmarkedhelicopter]

So you wouldn't be interested in say "Bold Style" ?
What about "STYLE" or "style" ?
Are you happy just to find the first occurance every time ?
I'm not sure what you'd want this for as the 'search for' facilty is already there, so you must want to use it in code ... for what ?

[unmarkedhelicopter]

shouldn't this be a reply to mdmackillop's post ?
(Sorry if this is explained anywhere ... I'm fairly new here :) )

[Bob Phillips]

So you wouldn't be interested in say "Bold Style" ?
What about "STYLE" or "style" ?
Are you happy just to find the first occurance every time ?
I'm not sure what you'd want this for as the 'search for' facilty is already there, so you must want to use it in code ... for what ?

Oh no, not you HERE as well :devil2:

[Bob Phillips]

Good Point Malcolm,
I am still not using the macro recorder as a tool, mental block there or something.... who knows- I really think it had to do w/ me whacking too many soccerballs off the head all those years.....

We've been heading FOOTBALLS since we could walk, even leather laced ones, and it never did us any harm:roller:

[YellowLabPro]

I am searching for certain keywords to assign these to column locations in another expression, which could change on every sheet. I am also looping through every sheet w/in the active workbook.
You do raise an intresting point, it will need to be for any case, upper, lower or mixed.
But I will solve for that when I get there.... when :yes I get there

[unmarkedhelicopter]

Oh no, not you HERE as well :devil2:
Well I was bored (I'm Ill ! guey green stuff involved) so I wandered round looking for another board and found I was already a member here !!!!

Sorry xld do I know you ? are you Bob ?

[Bob Phillips]

Indeed you do, indeed I am, who else would introduce themselves by insulting you :)?

Odd, another guy here was ill recently, so he started posting more!

[unmarkedhelicopter]

I am searching for certain keywords to assign these to column locations in another expression, which could change on every sheet. I am also looping through every sheet w/in the active workbook.
You do raise an intresting point, it will need to be for any case, upper, lower or mixed.
But I will solve for that when I get there.... when :yes I get there

So does that mean that (say) for each sheet you want a complete list of matches, which you then process or do you want to do it one at a time ? (this is much harder (you can still use the full list one at a time !))

Are we searching a specific area or the full sheet ?
If it's the full sheet and you're doing this for multiple sheets (and maybe mutiple workbooks) this is gonna take ages !

Sorry, why do you not reply to the corespondant rather than yourself ? (as I said before, I'm new here :think: )

[unmarkedhelicopter]

:hi:
Sorry, but it wasn't much of an insult (not up to your usual high standards) so I assumed it was someone who aspired to be merely anoying ! :rotlaugh:

I'm trying (obviously) to keep the same handle but you'd be surprised at the number of forums that truncate your user name to just 12 !!! (or maybe you wouldn't :giggle ).

Why do your details say you are in Chile ?

[YellowLabPro]

Heli:
Not sure how to answer your question how you might be asking it, I will just explain a little bit more.
I receive worksheets from companies every quarter that we place orders for their products. To automate and standardize this process, rather than use theirs, we use our own PO, (purchase order). To extract the data from the companies' sheet to my own I need to find which column these pieces of data are in, Style, Color, Price, Item Name, Item# and Delivery Date.
But as I am sure you can guess these items are in different columns in each of the different companies sheets, and a lot of times even in different columns within the companies own sheets, i.e. a companies sheet that has their shoes on one sheet has a setup different from their clothing sheet and is bound to change for the next season. But the consistent part of all of these sheets are the headers, so if I find the header name, assign it a variable and then place the variable in my expression I can search and find the data and place it in my sheet very quickly.
Does that help?

The area, it is typically the upper left quadrant of a sheet, but once it finds the defined name I want it to stop.

Not sure what you mean here:
why do you not reply to the corespondant rather than yourself

[YellowLabPro]

We've been heading FOOTBALLS since we could walk, even leather laced ones, and it never did us any harm:roller:
So you think..... and if they were laced ones then how do you remember :rofl:

[Bob Phillips]

I'm trying (obviously) to keep the same handle but you'd be surprised at the number of forums that truncate your user name to just 12 !!! (or maybe you wouldn't :giggle ).
You could always adopt UMH!


Why do your details say you are in Chile ?
When I first joined this board, I was in Chile, so I used their flag. I keep it so as to confuse people.

[unmarkedhelicopter]

Look at the structure of your thread.
At the top is your original questions, off that are all the people who responded directly to that post, then you have people who respond to the sub posts e.g. the conversation between XLD (that could be a clothing term for something that was merely Large and got so stretched it became XtraLarge hence it has been XLD'd) and myself where he appears off my response and then I appear off his.

Where as ALL yours so far seem to come off the original post ??? :dunno

[YellowLabPro]

Heli,
I see your point now. I had switched to the Hybrid way of displaying and I think I was getting all tangled up in the way they were showing up. In fact right before you posted about that, I switched back to the standard linear method.
Sorry it got out of whack- I did not even see some of the posts until I open it back up.

[YellowLabPro]

Heli, Bob,
Heli you wrote:

the conversation between XLD (that could be a clothing term for something that was merely Large and got so stretched it became XtraLarge hence it has been XLD'd) and myself where he appears off my response and then I appear off his.

I do not have a post on my end with anything regarding the word "Large" and "XtraLarge".

Heli would you copy out and repost for me please both yours and Bobs posts on the "Large" and "XtraLarge"?

But in guessing, that will not be an issue, I am not searching on those terms, only these:
Style, Item#, Color, Cost, Delivery Date.

[unmarkedhelicopter]

And yet you still reply to yourself !!! ...

I find the different views VERY confusing (very much prefer the standard linear view of things, but am trying the otherway (as is default) so I can see what all the fuss is about, don't really see it as an indispensible tool as yet, but I'll persevere :banghead: )

[unmarkedhelicopter]

Sorry, I was making fun of XLD's handle (I know him as ukBob) :giggle

[johnske]

:hi:
Sorry, but it wasn't much of an insult (not up to your usual high standards) so I assumed it was someone who aspired to be merely anoying ! :rotlaugh: No, he slacks off sometimes :)


I'm trying (obviously) to keep the same handle but you'd be surprised at the number of forums that truncate your user name to just 12 !!! (or maybe you wouldn't :giggle ).UMH, PM Jake (DRJ) and get him to change it - after all, "Jan Karel Pieterse" for example is more than 12 characters...


Why do your details say you are in Chile ?Bob likes to fly incognito sometimes, "ukBob" and "XLD" are just two of the pseudonyms I know of that he posts under to keep everyone guessing, he even posts on some boards using the pseudonym "Bob Phillips" - maybe one day we'll find out who he really is :rofl:

[Bob Phillips]

Bob likes to fly incognito sometimes, "ukBob" and "XLD" are just two of the pseudonyms I know of that he posts under to keep everyone guessing, he even posts on some boards using the pseudonym "Bob Phillips" - maybe one day we'll find out who he really is :rofl:
Does he even really exist?

Bob Phillips, that's ridiculous, no-one would believe that!

[unmarkedhelicopter]

Does he even really exist?

Bob Phillips, that's ridiculous, no-one would believe that!

Yeah that's such a poor joke it's a flagrant psudonym for "Elvis Presly" and 'some' people think that he is dead :whistle: