Find text Sequentially Number

[pwk]

I have a spread sheet that has: Item: 0308 0730 in cell A24, Item:
0308 0763 in cell A39 and so forth, I would like to put a 1 in cell
AB24, 2 in AB39 and so forth. I could probably base my macro on
Finding the text "Item" and Sequentially Numbering in the allied colum
AB. I've tried using find and search functions to no avail. I know
this is probably easy using a fuction or FindNext but I'm also having
a problem Sequentially Numbering. THANKS in Advance. Paul

 

[jjones]

There may be other ways of doing this, but I think you could accomplish it
using a couple of helper columns. I am making 2 assumptions--1) that the
other rows in between what you have provided do not contain the word "Item"
in column A and 2) columns AC and AD are available for use. If so...

On the row with your first item, (row 24?) put a 1 in cell AB24, AC24, and
AD24. In cell AB24 enter the formula: =FIND("Item",A25). In cell AC25
enter the formula: =IF(ISERROR(AB25),AC24,AC24+1). Finally, in cell AD 25
enter the formula =IF(ISERROR(AB25)," ",AC25). Pull those formulas all the
way down the column and see if column AD gives you what you're looking for.

 

[Rick Rothstein]

I think this formula will do what you want...

=IF(A2="","",1+COUNTIF(A:A,"<"&A2))

Put it in AB2 (this assumes your first data value is in row 2; change the A2
reference if it is not) and copy down.

 

[Mike H]

You posted this in general questions. Stick to one thread and don't
multi-post, it wastes the freely given time of those who respond not knowing
you already have responses

Mike H

 

[pwk]

You posted this in general questions. Stick to one thread and don't
multi-post, it wastes the freely given time of those who respond not knowing
you already have responses

Mike H





- Show quoted text -

Sorry, I wasn't aware this was forbidden. Do you have a solution?

 

[Rick Rothstein]

Do you have a solution?

Did my suggested formula not work?

--
Rick (MVP - Excel)

You posted this in general questions. Stick to one thread and don't
multi-post, it wastes the freely given time of those who respond not
knowing
you already have responses

Mike H





- Show quoted text -

Sorry, I wasn't aware this was forbidden. Do you have a solution?

 

[Rick Rothstein]

You posted this in general questions. Stick to one thread and don't

Sorry, I wasn't aware this was forbidden.

Forbidden? No, just common courtesy. Please consider the following...

From a post by Jeff Johnson:

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer
answering questions on newsgroups do see it as a problem. You can't
imagine how annoying it is for a volunteer to read a question,
research background material, test sample code and then formulate
and post an answer to the original question only to go to another
newsgroup and find the question posted and ALREADY answered over
there. On top of that, if you cross-post your question, all of the
readers in all the newsgroups it is cross-posted to see both the
original question and all of the answers given to it. This is
beneficial to you because then we can add additional material to,
add clarification to, as well as add additional examples to an
answer you have received previously... that means you end up with
a more complete solution to your problem. This is a win-win
situation for all of us."

 

[Mike H]

Right click your sheet tab, view code and paste this in and run it.

Sub sonic()
Num = 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A24:A" & lastrow)
For Each c In myrange
If UCase(Left(c.Value, 4)) = "ITEM" Then
c.Offset(, 27).Value = Num
Num = Num + 1
End If
Next
End Sub

Mike

 

[pwk]

Right click your sheet tab, view code and paste this in and run it.

Sub sonic()
Num = 1
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Range("A24:A" & lastrow)
For Each c In myrange
    If UCase(Left(c.Value, 4)) = "ITEM" Then
        c.Offset(, 27).Value = Num
        Num = Num + 1
    End If
Next
End Sub

Mike






- Show quoted text -

Thanks to all who posted. I learned a lot from your suggestions,
including the correct manner to post to newsgroups.
All of your examples were relevant to my needs but using Mike's macro
made it 'portable' and easier for repetive use.

 

[pwk]

There may be other ways of doing this, but I think you could accomplish it
using a couple of helper columns.  I am making 2 assumptions--1) that the
other rows in between what you have provided do not contain the word "Item"
in column A and 2) columns AC and AD are available for use.  If so...

On the row with your first item, (row 24?) put a 1 in cell AB24, AC24, and
AD24.  In cell AB24 enter the formula:  =FIND("Item",A25).  In cell AC25
enter the formula:  =IF(ISERROR(AB25),AC24,AC24+1).  Finally, in cell AD 25
enter the formula =IF(ISERROR(AB25)," ",AC25).  Pull those formulas all the
way down the column and see if column AD gives you what you're looking for.





- Show quoted text -

As an adjunct to a macro and as a learning experience I was trying to
get your formulas to work. Is there a typo or am I just missing
something. I can't get it to work.