You are here:

Excel/Labelling in sequence

Advertisement


Question
QUESTION: Tom: Im sorry to bother you so frequently, but you're the pinnacle of expertise to my ignorance.

I have some 7000 rows of text data in column K where sporadically there is text labeled <Player>1</Player>.  This might occur some 50 times.

In "R1" is a number used as a starter.

I want a macro that starts in "K1" (it could definitely stop by row 9999) and searches down for every <Player>1</Player> and renames it sequentially <Player>whatever value is in R1</Player> for the first instance, <Player>1 more than whatever value is in R1</Player>for the second instance, <Player>2 more than whatever value is in R1</Player> etc for every additional instance.

Another macro would be needed to find a similar entry, but before the 1 is always the player's initials, so it might be AC1, KT1 WB1 etc, this macro would need to keep the initials while sequentially renaming the number 1s. so it would be AC2, KT3, WB4 (assuming R1 is 2 as a starter).



thanks,

Pete

ANSWER: Pete

Sub abc()
Dim l as String, r as Range, cell as Range
l = Range("R2")
set r = range("K1",Cells(rows.count, "K").End(xlup))
for each cell in r
if instr(1,cell,</Player>,vbTextcompare) then
  
  cell.Value = "<Player>" & l & "</Player>"
  l = l + 1
end if
end Sub

I am not following your second request.  this is what I hear you saying

<Player>AC1</Player>

<Player>AC1</Player>

<Player>AC1</Player>

and you want that to become


<Player>AC2</Player>

<Player>AC3</Player>

<Player>AC4</Player>


How does the relate to the first situation?  Where do I look for this construct.  (obviously if this is in column K then my macro will process those cells same as the ones you describe so don't even run the macro).

If you can send a sample workbook and a complete explanation of what you want done, I will try to see if I can come up  with something for you.

by the way, I answered your first post - not sure why you posted again except another person complained they got no answer when I did answer.

twogilvy@msn.com

--
Regards,
Tom Ogilvy





---------- FOLLOW-UP ----------

QUESTION: Tom: I am sorry for the double post, I did it twice because the first submission never registered by the service, I thought maybe it poofed into cyberspace.

I got the first macro to work (missed a next command , no biggie)

I also apologize for my confusing explanation.  I will modify the file so that the SECOND <Player>FL1</Player> is instead <CodePlayer>FL1000</CodePlayer> where F is first initial and L is last initial and 1000 is always 1000, but for every player his initials will be different..

But I dont want it to be AC1 AC2 AC3 AC4 because those wouldn't be the player's initials, if the player were you it would be TO1, I would be PE2, the president would be BO3.  Since I want the digits to be 4 we'd actually be TO1001, PE1002, BO1003.

I'll mess around on it some myself but the variability of all the initials is my issue.

Hope that's better.

Answer
Pete,

so there was another problem with the code.  Should have been

Sub abc()
Dim l As String, r As Range, cell As Range
l = Range("R2")
Set r = Range("K1", Cells(Rows.Count, "K").End(xlUp))
For Each cell In r
If InStr(1, cell, "</Player>", vbTextCompare) Then
 
 cell.Value = "<Player>" & l & "</Player>"
 l = l + 1
End If
Next
End Sub

so that was tested and worked

so the modification would be minimal:

if we can assume that XX1000 is in the middle of the brackets (where XX is the players initials and they will be different) then

Sub abc_intials()
Dim l As String, r As Range, cell As Range
l = Range("R2")
Set r = Range("K1", Cells(Rows.Count, "K").End(xlUp))
For Each cell In r
 iloc = InStr(1, cell, ">", vbTextCompare)
 iloc1 = InStr(1, cell, "</CodePlayer>", vbTextCompare)
If iloc1 <> 0 Then
 s = Mid(cell, iloc + 1, 6)
 init = Left(s, 2)
 cnt = 1 * Mid(s, 3, 4)
 cnt = cnt + l
 cell.Value = "<CodePlayer>" & init & cnt & "</CodePlayer>"
 l = l + 1
End If
Next
End Sub

That was tested as well with the assumptions stated.

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.