Macro to Add Numbers to End of A Column

[Michael151]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman";} </style> <![endif]--> Hello all,

Looking to write a macro that will add numbers in a column until the end of text in another column.

I have two columns: “numbers” and “title” (labeled in row 1 header). They are not always in the same letter, which is why I’d like to use the header in row 1 to identify. The macro needs to add sequential numbers in the “numbers” column, starting in row 2, to the end of text in the “title” column.

Before:



<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">numbers</td> <td style="width: 48pt;" width="64">title</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Title6</td> </tr> </tbody></table>After:

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">numbers</td> <td style="width: 48pt;" width="64">title</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1</td> <td>Title1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">2</td> <td>Title2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3</td> <td>Title3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">4</td> <td>Title4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">5</td> <td>Title5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">6</td> <td>Title6</td> </tr> </tbody></table>

Macro should add numbers even if there is only one row. If there are no titles in the "title" column, then do nothing.

Help is most appreciated - thanks!

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">
</td><td style="width: 48pt;" width="64">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" align="right" height="17">
</td><td>
</td></tr></tbody></table>

 

[Dryver14]

Non Macro option would be

=IF(B1<>"",1,"") in A1

then

=IF(B2<>"",A1+1,"") in A2 then drag down the column

 

[Michael151]

Hi Dryver14,

How about I incorporate this into a macro?

Also, the columns are not always in A and B - they can be in any letter column so I'd like to use the header in row 1 to identify.

Thanks!

 

[Dryver14]

A Macro is not always the answer to the question believe it or not in this case you would be looking for a change event macro on an undefined column to enact an action on another If you are talking film titles then I assume the max columns you will have will be 26 X 2 and even if you offset the answer by 1 column then you could cause problems if you click in a number cell and then away, the Macro does not care you did it wrong it will blindly change the text to the left.

If you use the formula in row A2 and drag it right across the page as many columns as you want then in A3 the second formula and drag all across and down

=IF(B2<>"",1,"") in A2
=IF(B3<>"",A2+1,"")
then delete the columns you will put text into I think you will have the sheet you want


I do hope this helps

 

[Michael151]

Here's what I have so far:

<!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:"Table Normal";     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0in 5.4pt 0in 5.4pt;     mso-para-margin:0in;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";} </style> <![endif]-->  [FONT=Arial]Dim F1 As Range, F2 As Range, TR As Long[/FONT]
  [FONT=Arial]Set F1 = Rows(1).Find(What:="Title", LookIn:=xlValues, LookAt:=xlWhole)[/FONT]
  [FONT=Arial]Set F2 = Rows(1).Find(What:="Numbers", LookIn:=xlValues, LookAt:=xlWhole)[/FONT]
  [FONT=Arial]TR = Cells(Rows.Count, F1.Column).End(xlUp).Row[/FONT]
  
  [FONT=Arial]With Cells(2, F2.Column)[/FONT]
  [FONT=Arial]    .Value = 1[/FONT]
  [FONT=Arial]    .Offset(1).Value = 2[/FONT]
  [FONT=Arial]    .Resize(2).AutoFill Destination:=Range(Cells(2, F2.Column), Cells(TR, F2.Column))[/FONT]
  [FONT=Arial]    .EntireColumn.Font.Bold = True[/FONT]
  
  [FONT=Arial]End With[/FONT]

This works in every case except if the title column has 2 or fewer titles. Is there any way to add in an error message or something to the effect of: if no autofill used or titles less than 2, then don't autofill?

Thanks!

 

[Dryver14]

I am still learning VBA so this is not great but it does put the numbers in rows 2 onwards however it does seem to require headings in every second column eg b d f h etc perhaps you can have a play with it to make it what you want VBA'ers be kind to me.....I have "L" plates on!!!

Sub Number()
Dim a As Long
Dim i As Long
Dim j As Long
Dim Finalrow As Long
Dim FinalCol
a = 1
i = 1
j = 0
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To FinalCol
Cells(a, 1).Select
    If Cells(a, i + 1).Value = "" Then i = i + 1
Finalrow = Cells(Rows.Count, i + 1).End(xlUp).Row
    For a = 2 To Finalrow
If Cells(a, i + 1).Value <> "" Then
    j = j + 1
    Cells(a, i).Value = j
End If
    Next
    i = i + 1
    j = 0
    a = 1
    Next
End Sub

 

[Michael151]

Thanks Dryver14, I was able to adapt the code and get it to work - thanks for your help!