Row Reference by Page Number and Alphabetical Order.

[nitrammada]

Hi Folks,
I'm looking for assistance for line referencing and would appreciate any help given. Each row of data in my report requires a row reference in this format
1/A for row 1 and
1/B for row 2 and so on. 1 being the page number and "A" being the first alphabetical reference for the first row.

So for page 2 I need the row reference to start again at "A" but for page 2 the reference needs to looke like
2/A
2/B
etc
If there are more than 26 rows 2/AA, 2/AB is fine.

I have been trying to use VBA to reference the page count using the HPageBreaks with the pgbr.Location command but it's a bit beyond me to be honest.
If any one can assist with some vba code, I would be most grateful.
Thanks in advance.

Below is what I'm trying to achieve, I hope this makes sense.

 

[Marc L]

Hi,​

​

as it's easier to help with an attachment … I can give some directions but it seems you are not confident with your VBA skills.​

 

[nitrammada]

Hi Marc,
Thanks for your reply, I've tried the XL2BB add in but my computer just falls over when I run it.
Can I send you the file somehow?
Regards
Adam

 

[Marc L]

​

Use some files hosting website …​

 

[nitrammada]

See if this works Marc
I've never used this XL2BB before. Can you read this? Does it help any?

PR-007601 Stage 01 Estimate
Estimate Summary 24 April 2021
REF	DESCRIPTION	Quantity	Unit
1/A	COMMERCIAL IMPACT AS ADVISED BY CLIENT
1/B	COMMERCIAL IMPACTS
1/C	COMMERCIAL IMPACTS
1/D	COMMERCIAL IMPACTS
1/E	Commercial Impacts
1/F	Advertising	1	Item
1/G	Relocation Costs	1	Item
1/H	Retail Lease Losses	1	Item
1/I
1/J
1/K	TOTAL PROJECTED CLIENT OUTTURN COST
1/L	OTHER COSTS
1/M	OTHER COSTS
1/N	OTHER COSTS
1/O	Other Costs
1/P	Client Overhead Recovery	0.0372	%
1/Q
2/A
2/B	TOTAL PROJECT COSTS
2/C	CONSTRUCTION CONTINGENCY
2/D	CONSTRUCTION CONTINGENCY
2/E	CONSTRUCTION CONTINGENCY
2/F	Construction Contingency
2/G	Construction Contingency - held by client	0.05	%
2/H

 

[nitrammada]

​

Use some files hosting website …​

 

[nitrammada]

So the 2/A, 2/B etc would start at the beginning of page 1
I tried to run something like this:

Sub RunPgTest()
'Adds page numbers for the Ref column on Print2

Dim saveActiveCell As Range
Dim lastRow As Long, i As Long
Dim pb As HPageBreak

Set saveActiveCell = ActiveCell

With ActiveWorkbook.ActiveSheet

'Select last cell so that Excel recalculates automatic page breaks. Can prevent "Subscript out of range" error when referring to HPageBreaks

lastRow = .UsedRange.Rows.count - .UsedRange.Row + 1
.Cells(lastRow, "H").Select

For i = .HPageBreaks.count To 1 Step -1
Set pb = .HPageBreaks(i)
'.Rows(pb.Location.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Cells(pb.Location.Row - 0, "H").Select
ActiveCell.Value = 6
Next

End With
saveActiveCell.Select
Range("h56").Select

End Sub

But I couldn't get it to work.

 

[Marc L]

According to posts #1 & 5 a VBA demonstration as a beginner starter :​

Sub Demo1()
  Const C = 10, F1 = "=""", F2 = "/""&LEFT(ADDRESS(1,ROW()-", F3 = ",2),1+(ROW()>", F4 = "))"
        R& = 5
  With ActiveSheet
    For L& = 1 To .HPageBreaks.Count
        P& = R
        R& = .HPageBreaks(L).Location.Row
       .Columns(C).Rows(P & ":" & R - 1).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
    Next
        P = R
        R = .UsedRange.Rows.Count
        If P <= R Then .Columns(C).Rows(P & ":" & R).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
  End With
End Sub

 

[nitrammada]

According to posts #1 & 5 a VBA demonstration as a beginner starter :​

Sub Demo1()
  Const C = 10, F1 = "=""", F2 = "/""&LEFT(ADDRESS(1,ROW()-", F3 = ",2),1+(ROW()>", F4 = "))"
        R& = 5
  With ActiveSheet
    For L& = 1 To .HPageBreaks.Count
        P& = R
        R& = .HPageBreaks(L).Location.Row
       .Columns(C).Rows(P & ":" & R - 1).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
    Next
        P = R
        R = .UsedRange.Rows.Count
        If P <= R Then .Columns(C).Rows(P & ":" & R).Formula = F1 & L & F2 & P - 1 & F3 & P + 25 & F4
  End With
End Sub

 

[nitrammada]

Marc, thank you so much, you are a genius. It works perfectly, I will study it to try and understand how you did this with my limited VBA skills (actually zero VBA skills) but thank you so much it was exactly what I was looking for.
Appreciatively yours
Adam