[ExcelVBA] Re: need to find a specific column in a table

 

This will work if it has several additions...

--- Purushottam Kumar wrote:
> -------
> sub test()
> dim i as integer ' I'd use thre cariable "Col"

... You need a For.Next loop for all the rows from start to some upper limit.

Dim Row as Integer ' or Long

For Row = 2 to "Several hundred" ' U supply the limit.

> for i= 1 to 30 'Total no. of columns

Becomes:
For Col = 1 to Whatever

> if cells(2,i).value="MAX*" or cells(2,i).value="DS*" then

Since the Characters are the *beginning* of the part number with digits afterword, you need a "Left$(string, count))" Then the above would become:

if LEFT$(cells(Row, Col).value, 3) ="MAX" or LEFT$(cells(Row, Col).value, 2) ="DS" then

You can also add a test for null values ("") and stop the search when you get to blank rows IF a single blank cell won't/can't occur.

Or split into TWO IFs so you can tally the MAX's seperately from the DS's

> msgbox columns(i) & " contains Part Number"

Rather than a MsgBox you can tally the occurrances for each column. If two part numbers can be in a single row, you must allow for that.
For this I'd use an array of 2 x 30. The 2 is for a count of either "MAX" or "DS" and the 30 for which column they are in. This way you can see how many of each occur in each column. If you don't care whether it is a "MAX" or "DS" then it can be a 1 x 30 array.

Or you could use two 1 x 30 arrays. MAX(col) and DS(col). Then whenever you get a match, MAX(Col) = MAX(Col) + 1 ... etc

--
Regards, Steve, K9DCI

>
> sunnywulf <wolfram.schneider@...>wrote:
>
> > I have an Excel table where one of the columns contains our company's part
> > number. This part number has the prefix MAX or DS followed by a number that
> > is at least 3 digits long (examples: MAX123 or DS987).
> >
> > The table dimensions can be several hundred rows and up to 30 columns. The
> > column with the part number has no fixed location and can be anywhere.
> >
> > How can I determine in VBA the number of the column that contains the Part
> > Number?
> >
> > I did not find anything wen googling this question. I hope somebody can
> > help me with this challenge.
> > Thank you for your replies,
> > sunnywulf
> >
> >
> >
>
>
>
> --
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (4)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___