Try this, assuming your data starts in M2 & N2 - You only need to enter the formula in N3 and drag down
Excel 2010
M N 1 Pages Intervals 2 2 1-2 3 5 3-7 4 3 8-10 5 4 11-14 6 25 15-39 7 55 40-94 8 12 95-106 9 1000 107-1106
<tbody>
</tbody>Sheet4
Worksheet Formulas
Cell Formula N2 =1&"-"&M2+0 N3 =SUBSTITUTE(RIGHT(N2,FIND("-",N2)),"-","")+1&"-"&SUM($M$2:M3) N4 =SUBSTITUTE(RIGHT(N3,FIND("-",N3)),"-","")+1&"-"&SUM($M$2:M4) N5 =SUBSTITUTE(RIGHT(N4,FIND("-",N4)),"-","")+1&"-"&SUM($M$2:M5) N6 =SUBSTITUTE(RIGHT(N5,FIND("-",N5)),"-","")+1&"-"&SUM($M$2:M6) N7 =SUBSTITUTE(RIGHT(N6,FIND("-",N6)),"-","")+1&"-"&SUM($M$2:M7) N8 =SUBSTITUTE(RIGHT(N7,FIND("-",N7)),"-","")+1&"-"&SUM($M$2:M8) N9 =SUBSTITUTE(RIGHT(N8,FIND("-",N8)),"-","")+1&"-"&SUM($M$2:M9)
<tbody>
</tbody>
<tbody>
</tbody>
Option Explicit
Sub calc()
Dim nPage As Long
Dim prevInterval As String
Dim prevIntervalArr() As String
Dim newInterval1 As Long
Dim newInterval2 As Long
Dim i As Long
Dim j As Long
Dim ref As Worksheet
Set ref = ThisWorkbook.Sheets("Sheet1")
For i = 2 To ref.Range("M" & Rows.Count).End(xlUp).Row
If i = 2 Then
ref.Range("N2").Value = CStr(1 & " - " & ref.Range("M2").Value)
Else
nPage = ref.Range("M" & i).Value
prevIntervalArr = Split(ref.Range("N" & i).Offset(-1, 0).Value, " - ", 2)
For j = LBound(prevIntervalArr) To UBound(prevIntervalArr)
prevInterval = prevIntervalArr(1)
Next j
newInterval1 = CLng(prevInterval) + 1
newInterval2 = newInterval1 + nPage - 1
ref.Range("N" & i).Value = CStr(newInterval1 & " - " & newInterval2)
End If
Next i
End Sub
Fill your M column with page numbers then run this code.
Code:Option Explicit Sub calc() Dim nPage As Long Dim prevInterval As String Dim prevIntervalArr() As String Dim newInterval1 As Long Dim newInterval2 As Long Dim i As Long Dim j As Long Dim ref As Worksheet Set ref = ThisWorkbook.Sheets("Sheet1") For i = 2 To ref.Range("M" & Rows.Count).End(xlUp).Row If i = 2 Then ref.Range("N2").Value = CStr(1 & " - " & ref.Range("M2").Value) Else nPage = ref.Range("M" & i).Value prevIntervalArr = Split(ref.Range("N" & i).Offset(-1, 0).Value, " - ", 2) For j = LBound(prevIntervalArr) To UBound(prevIntervalArr) prevInterval = prevIntervalArr(1) Next j newInterval1 = CLng(prevInterval) + 1 newInterval2 = newInterval1 + nPage - 1 ref.Range("N" & i).Value = CStr(newInterval1 & " - " & newInterval2) End If Next i End Sub
ALT + F11 to open VBA editor. Right click to add a new module -> paste this code then hit F5 to run! Good luck
I've adjusted the formula to handle this. Should work now.
=RIGHT(N2,LEN(N2)-FIND("-",N2))+1&"-"&SUM($M$2:M3)
Whats your sheet name then? you can go by sheet number instead? Has an error been thrown? what was it