creating a number index for row records

[AussieSteve]

Hi guys.
This table is an example of what my ws looks like, I would like some help and information on how to code the action of creating the numbers you see along side all the records in column "C". I have tried with a macro, but it does not give me enough of an understanding of what is going on and how to proceed to modify the code. Any help is always appreciated.
Steve.


[TABLE="width: 324"]
<tbody>[TR]
[TD]Company [/TD]
[TD]Before [/TD]
[TD]After [/TD]
[TD]Transactions[/TD]
[/TR]
[TR]
[TD]Company1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Record1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Record2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Record3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Record4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Record5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Record6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]Record7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]Record8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]Record9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]Record10[/TD]
[/TR]
[TR]
[TD]Company2[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Record1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Record2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Record3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Record4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Record5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]Record6[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Hi Steve
Maybe this then, assuming company names in "A" and required numbering in "B"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
n = 1
For r = 2 To lr
    If Range("A" & r).Value <> "" Then
        Range("B" & r).Value = 1
    ElseIf Range("A" & r).Value = "" Then
        Range("B" & r).Value = Range("B" & r - 1).Value + 1
    End If
Next r
End Sub

 

[AussieSteve]

Hi Steve
Maybe this then, assuming company names in "A" and required numbering in "B"

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
n = 1
For r = 2 To lr
    If Range("A" & r).Value <> "" Then
        Range("B" & r).Value = 1
    ElseIf Range("A" & r).Value = "" Then
        Range("B" & r).Value = Range("B" & r - 1).Value + 1
    End If
Next r
End Sub

Hi Michael.
Thanks for your reply, I copied your code into a new wb and module saved wb as macro enabled wb.
Your code when run, I stepped through it, as soon as it hits sub it goes into break mode there are no syntax or run time errors. No number printed in column B. My coding and de-bugging skills are not sufficient see the problem.

 

[TotallyConfused]

Hello Steve
I think this should create a data file that resembles the sample you gave in posting #1 . It takes the data from the "Transactions" column 'D', and places the number part in the 'After' column 'C', I hope this is what you wanted.

[COLOR=black][FONT=Arial]Sub CreateNumberIndex()[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   ' https://www.mrexcel.com/forum/excel-questions/1087125-creating-number-index-row-records.html[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   ' Created February 12, 2019[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Dim NumDataRows As Long       ' Number of data rows NOT counting headings[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Dim dTransactions As String       ' Each record in column 'D'[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Dim dChar As String                    ' Alphabetic characters[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Dim dRow As Long                      ' Counts the data row number[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Dim LenCtr As Long                    ' Counts number of characters in a 'D' column cell[/FONT][/COLOR]
  
[COLOR=black][FONT=Arial]Application.ScreenUpdating = False[/FONT][/COLOR]
[COLOR=black][FONT=Arial]NumDataRows = Cells(Rows.Count, "D").End(xlUp).Row[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   For dRow = 1 To NumDataRows[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]         dTransactions = Cells(dRow, "D")[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]             For LenCtr = 1 To Len(dTransactions)[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                 dChar = Asc(Mid(dTransactions, LenCtr, 1))[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                     If (dChar >= 65) Then          'Will be a letter[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                     Else                                      'Will be a number[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                         Cells(dRow, "C") = Right(dTransactions, Len(dTransactions) - (LenCtr - 1))[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                         LenCtr = Len(dTransactions)[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]                    End If[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]             Next LenCtr[/FONT][/COLOR]
  [COLOR=black][FONT=Arial]   Next dRow
Application.ScreenUpdating = True
End Sub[/FONT][/COLOR]

TotallyConfused

 

[Rick Rothstein]

Hello Steve
I think this should create a data file that resembles the sample you gave in posting #1 . It takes the data from the "Transactions" column 'D', and places the number part in the 'After' column 'C', I hope this is what you wanted.

The layout was not that clear to me either. Using your assumptions, here is a more compact macro to do what your code does...

[table="width: 500"]
[tr]
	[td]Sub CreateNumberIndex()
  Dim Ar As Range
  For Each Ar In Range("A1:A" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 2).Resize(Ar.Count + 1) = Evaluate("ROW(1:" & Ar.Count + 1 & ")")
  Next
End Sub[/td]
[/tr]
[/table]

 

[AussieSteve]

The layout was not that clear to me either. Using your assumptions, here is a more compact macro to do what your code does...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CreateNumberIndex()
  Dim Ar As Range
  For Each Ar In Range("A1:A" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 2).Resize(Ar.Count + 1) = Evaluate("ROW(1:" & Ar.Count + 1 & ")")
  Next
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick.
Your code works well enough for what I need to do, only two minor problems, the first is: if the company only has one record the program leaves that cell blank (no number recorded ) the second is : on the last company name it will not record numbers for, because it doesn't have a reference for it. So one cell down from the row of the last record I put the text, LastRowStop for the column it references. That works.
Thanks Steve.

 

[Rick Rothstein]

Hi Rick.
Your code works well enough for what I need to do, only two minor problems, the first is: if the company only has one record the program leaves that cell blank (no number recorded ) the second is : on the last company name it will not record numbers for, because it doesn't have a reference for it. So one cell down from the row of the last record I put the text, LastRowStop for the column it references. That works.

The following code fixes the first problem. As for the second problem... I am not sure what you are referring to as my code appears to number all the way down to the last company's record.

[table="width: 500"]
[tr]
	[td]Sub CreateNumberIndex()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 2).Resize(Ar.Count + 1) = Evaluate("ROW(1:" & Ar.Count + 1 & ")")
  Next
  On Error GoTo NoSingles
  Range("C1:C" & LastRow).SpecialCells(xlBlanks).Value = 1
NoSingles:
End Sub[/td]
[/tr]
[/table]

 

[AussieSteve]

The following code fixes the first problem. As for the second problem... I am not sure what you are referring to as my code appears to number all the way down to the last company's record.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub CreateNumberIndex()
  Dim LastRow As Long, Ar As Range
  LastRow = Cells(Rows.Count, "D").End(xlUp).Row
  For Each Ar In Range("A1:A" & LastRow).SpecialCells(xlBlanks).Areas
    Ar(1).Offset(-1, 2).Resize(Ar.Count + 1) = Evaluate("ROW(1:" & Ar.Count + 1 & ")")
  Next
  On Error GoTo NoSingles
  Range("C1:C" & LastRow).SpecialCells(xlBlanks).Value = 1
NoSingles:
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Hi Rick.
Thanks for all your help, I will trust you in what say about your code, I am the one learning from you and the people that contribute to this forum. And thanks to Michael, and Totally Confused for their contribution as well.
Steve.