wuletawwonte · December 6, 2024 12:31
diff --git a/increment_verse.vb b/increment_verse.vb
 Sub IncrementVersesWithProgress()
    Dim ws As Worksheet
    Dim lastVerse As String
    Dim lastBook As String
    Dim lastNumber As Long
    Dim cell As Range
    Dim parts() As String
    Dim totalRows As Long
    Dim processedCount As Long
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets(1)
    
    ' Apply filter to exclude empty rows in column "CT"
    ws.Range("CT:CT").AutoFilter Field:=1, Criteria1:="<>" & vbNullString
    
    ' Get the visible cells in column "CT"
    On Error Resume Next
    Dim filteredRange As Range
    Set filteredRange = ws.Range("CT:CT").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' Exit if no visible rows
    If filteredRange Is Nothing Then
        MsgBox "No visible data found in column CT.", vbExclamation
        ws.AutoFilterMode = False
        Exit Sub
    End If
    
    ' Count the total number of rows to process
    totalRows = filteredRange.Cells.Count - 1 ' Exclude the header row
    processedCount = 0
    lastVerse = ""
    
    ' Loop through visible cells
    For Each cell In filteredRange
        If cell.Row > 1 Then ' Skip the header row
            processedCount = processedCount + 1
            
            Dim cellValue As String
            cellValue = Trim(cell.Value)
            
            If cellValue <> "" Then
                If InStr(cellValue, ":") > 0 Then
                    ' Update lastVerse if this is a verse
                    lastVerse = cellValue
                    
                ElseIf cellValue = "1" Then
                    ' Increment the verse number if lastVerse exists
                    If lastVerse <> "" Then
                        parts = Split(lastVerse, ":")
                        lastBook = parts(0)
                        lastNumber = Val(parts(1))
                        
                        lastNumber = lastNumber + 1
                        
                        ' Update the cell with the new verse
                        cell.Value = lastBook & ":" & lastNumber
                        lastVerse = lastBook & ":" & lastNumber
                    End If
                End If
            End If
            
            ' Update the status bar with progress
            Application.StatusBar = "Processing row " & processedCount & " of " & totalRows
        End If
    Next cell
    
    ' Clear the filter and reset the status bar
    ws.AutoFilterMode = False
    Application.StatusBar = False
    
    MsgBox "Verses updated successfully!", vbInformation
 End Sub
	Sub IncrementVersesWithProgress()
	Dim ws As Worksheet
	Dim lastVerse As String
	Dim lastBook As String
	Dim lastNumber As Long
	Dim cell As Range
	Dim parts() As String
	Dim totalRows As Long
	Dim processedCount As Long

	' Set the worksheet
	Set ws = ThisWorkbook.Sheets(1)

	' Apply filter to exclude empty rows in column "CT"
	ws.Range("CT:CT").AutoFilter Field:=1, Criteria1:="<>" & vbNullString

	' Get the visible cells in column "CT"
	On Error Resume Next
	Dim filteredRange As Range
	Set filteredRange = ws.Range("CT:CT").SpecialCells(xlCellTypeVisible)
	On Error GoTo 0

	' Exit if no visible rows
	If filteredRange Is Nothing Then
	MsgBox "No visible data found in column CT.", vbExclamation
	ws.AutoFilterMode = False
	Exit Sub
	End If

	' Count the total number of rows to process
	totalRows = filteredRange.Cells.Count - 1 ' Exclude the header row
	processedCount = 0
	lastVerse = ""

	' Loop through visible cells
	For Each cell In filteredRange
	If cell.Row > 1 Then ' Skip the header row
	processedCount = processedCount + 1

	Dim cellValue As String
	cellValue = Trim(cell.Value)

	If cellValue <> "" Then
	If InStr(cellValue, ":") > 0 Then
	' Update lastVerse if this is a verse
	lastVerse = cellValue

	ElseIf cellValue = "1" Then
	' Increment the verse number if lastVerse exists
	If lastVerse <> "" Then
	parts = Split(lastVerse, ":")
	lastBook = parts(0)
	lastNumber = Val(parts(1))

	lastNumber = lastNumber + 1

	' Update the cell with the new verse
	cell.Value = lastBook & ":" & lastNumber
	lastVerse = lastBook & ":" & lastNumber
	End If
	End If
	End If

	' Update the status bar with progress
	Application.StatusBar = "Processing row " & processedCount & " of " & totalRows
	End If
	Next cell

	' Clear the filter and reset the status bar
	ws.AutoFilterMode = False
	Application.StatusBar = False

	MsgBox "Verses updated successfully!", vbInformation
	End Sub