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

Hi

Thanks for Left function

another way to find out part no, row wise & column wise

Sub test()
Dim lrow As Long, lcol As Long, temp As Long, temp1 As Long
Dim I as long
lrow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
For I = 1 To lcol
temp = 0
temp1 = 0

temp = WorksheetFunction.CountIf(Range(Cells(2, I), Cells(lrow, I)),
"MAX*")
temp1 = WorksheetFunction.CountIf(Range(Cells(2, I), Cells(lrow, I)),
"DS*")

If temp > 0 Or temp1 > 0 Then
MsgBox Columns(I) & " Contains Part Numbers"
End If

Next I
End Sub



On Fri, May 18, 2012 at 2:20 AM, noskosteve <noskosteve@yahoo.com> wrote:

> **
>
>
>
> Ignore that man behind the curtain, OOPS.
>
> I read it as determining the number of different columns containing part
> numbers, not simply the column it is in in any given row...
>
> Some of what I said still applies (the "LEFT$()" part).
>
> --
> Steve
>
> --- In ExcelVBA@yahoogroups.com, "noskosteve" <noskosteve@...> wrote:
> >
> > 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 30
> >
> >
> > > 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
> >
> > Hope I got all the typos...
> >
> > >
> > > 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]
> > >
> >
>
>
>



--
*Regards,

**Purushottam Kumar
(09234431314)*
**
*"Failure is necessary for Success"*


[Non-text portions of this message have been removed]



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

----------------------------------
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

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/