基于VBA查找最大小数并向上取整——版本编号自动化需求

Hey there! Let's tackle this VBA version automation task together. I'll break down the logic step by step and give you a fully commented code example that you can tweak to fit your workbook perfectly.

First, let's make sure we're aligned on what you need:

• Traverse a specific column in your main data sheet to find the highest decimal version number
• Write your current data to the archive area immediately below that max version (e.g., if max is 0.03, write 0.04)
• Set the main page's version number to 1 and mark its status as draft
• Round up that max decimal version to the nearest whole integer (I'll assume you want to display this somewhere on the main page)

Let's break this into manageable parts:

1. Define Your Workbook/Worksheet References #

First, we'll assign variables to your key worksheets so you don't have to hardcode names everywhere (easy to update later if your sheet names change).

2. Safely Find the Maximum Decimal Value #

Instead of relying solely on WorksheetFunction.Max (which can fail if there are non-numeric values), we'll loop through the column to check each cell—this handles blanks or text entries gracefully.

3. Write to the Archive Area #

We'll find the last used row in the archive's version column, then write the new version (max + 0.01) to the next row. I'll include an optional snippet to copy additional "current data" if you need it.

4. Update the Main Page #

Set the version to 1, status to draft, and calculate the rounded-up integer version of the max value we found.

Here's the full, commented code you can drop into your workbook's module:

Sub AutoVersioning()
    ' Define worksheet references - UPDATE THESE TO MATCH YOUR WORKBOOK!
    Dim wsMainData As Worksheet
    Dim wsArchive As Worksheet
    Dim wsMainPage As Worksheet
    
    Set wsMainData = ThisWorkbook.Worksheets("主数据表格") ' Replace with your main data sheet name
    Set wsArchive = ThisWorkbook.Worksheets("存档区域") ' Replace with your archive sheet name
    Set wsMainPage = ThisWorkbook.Worksheets("主页面") ' Replace with your main page sheet name
    
    ' Define target column in main data (e.g., column C = 3, column A = 1)
    Dim targetCol As Integer
    targetCol = 3 ' Update this to your version column number
    
    ' Step 1: Find the maximum decimal version in the target column
    Dim maxVersion As Double
    Dim lastRow As Long
    Dim i As Long
    
    maxVersion = 0 ' Start at 0 in case all values are lower
    lastRow = wsMainData.Cells(wsMainData.Rows.Count, targetCol).End(xlUp).Row
    
    ' Loop through rows (skip row 1 if it's a header)
    For i = 2 To lastRow
        ' Only check numeric values to avoid errors
        If IsNumeric(wsMainData.Cells(i, targetCol).Value) Then
            If wsMainData.Cells(i, targetCol).Value > maxVersion Then
                maxVersion = wsMainData.Cells(i, targetCol).Value
            End If
        End If
    Next i
    
    ' Step 2: Write current data to the archive's next row
    Dim archiveLastRow As Long
    archiveLastRow = wsArchive.Cells(wsArchive.Rows.Count, targetCol).End(xlUp).Row
    
    ' Write the new version number (max + 0.01)
    wsArchive.Cells(archiveLastRow + 1, targetCol).Value = maxVersion + 0.01
    
    ' Optional: Copy additional current data from main page to archive (adjust ranges as needed)
    ' Example: Copy main page A2:B2 to archive columns A:B of the new row
    ' wsMainPage.Range("A2:B2").Copy wsArchive.Range("A" & archiveLastRow + 1)
    
    ' Step 3: Update main page version and status
    ' UPDATE THESE CELL REFERENCES TO MATCH YOUR MAIN PAGE LAYOUT!
    wsMainPage.Range("B1").Value = 1 ' Main page version cell
    wsMainPage.Range("C1").Value = "draft" ' Main page status cell
    
    ' Step 4: Round up max version to nearest integer (write to main page cell D1 in this example)
    Dim roundedMax As Integer
    roundedMax = WorksheetFunction.RoundUp(maxVersion, 0)
    wsMainPage.Range("D1").Value = roundedMax
    
    ' Optional: Pop up a confirmation message
    MsgBox "Version automation done!" & vbCrLf & _
           "New archive version: " & maxVersion + 0.01 & vbCrLf & _
           "Rounded max version: " & roundedMax, vbInformation
End Sub

• Update worksheet/cell references: Make sure to replace the sheet names and cell ranges (like B1, C1) with your actual workbook's layout.
• Adjust the target column: Change targetCol = 3 to the column number that holds your version decimals (e.g., column A is 1, column D is 4).
• Expand data copying: Uncomment and modify the optional copy line if you need to move more than just the version number to the archive.
• Handle edge cases: If your main data column has no numeric values, the code will start the archive at 0.01 (since we initialized maxVersion to 0).

内容的提问来源于stack exchange，提问作者David Wilson