Excel VBA问题：保留与Q1周数匹配的行并保留Weeknum公式

Hey there! Let's work through your VBA issues together. From what you shared, you're facing two key problems:

1. Column K is showing raw week numbers instead of keeping the WEEKNUM formula
2. You need to filter rows to only keep those matching the week number entered by the user (stored in Q1)

Here's how to fix both:

1. Keep the WEEKNUM Formula in Column K #

The most likely reason your formulas are turning into static values is that your code is using .Value to assign data to column K, which replaces formulas with their calculated results. Instead, use .Formula to preserve the formula itself.

For example, if your dates are in column J (adjust this to match your actual date column), use this line to set the formula for column K:

' Replace "J2" with your date column's cell reference, adjust the WEEKNUM parameter as needed
ws.Range("K2:K" & lastRow).Formula = "=WEEKNUM(J2,2)"

The 2 in WEEKNUM(J2,2) sets Monday as the first day of the week—change this to 1 if you want Sunday as the start, based on your needs.

2. Filter Rows to Match the Week Number in Q1 #

To filter rows correctly, you'll need to validate the user's input, write it to Q1, then apply Excel's AutoFilter to column K.

Full Working VBA Code #

Sub FilterByTargetWeek()
    Dim userInput As Variant
    Dim lastRow As Long
    Dim targetSheet As Worksheet
    
    ' Set your worksheet (replace "DataSheet" with your actual sheet name)
    Set targetSheet = ThisWorkbook.Worksheets("DataSheet")
    
    ' Get week number from user
    userInput = InputBox("Enter the week number to filter:", "Week Number Filter")
    
    ' Validate input is a number
    If Not IsNumeric(userInput) Then
        MsgBox "Please enter a valid number!", vbExclamation
        Exit Sub
    End If
    
    ' Write validated week number to Q1
    targetSheet.Range("Q1").Value = CLng(userInput)
    
    ' Find last row with with data in column K
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "K").End(xlUp).Row
    
    ' Ensure column K has the WEEKNUM formula (adjust date column reference if needed)
    targetSheet.Range("K2:K" & lastRow).Formula = "=WEEKNUM(J2,2)"
    
    ' Clear existing filters if any
    If targetSheet.AutoFilterMode Then targetSheet.AutoFilterMode = False
    
    ' Apply filter to column K (Field:=11 because K is the 11th column in the range A1:K—adjust if your range differs)
    targetSheet.Range("A1:K" & lastRow).AutoFilter Field:=11, Criteria1:=targetSheet.Range("Q1").Value
    
    MsgBox "Filter complete! Only rows for week " & userInput & " are visible.", vbInformation
End Sub

Key Notes: #

• Input Validation: The code checks if the user entered a number to avoid errors from non-numeric inputs.
• Preserve Formulas: Using .Formula ensures column K keeps the dynamic WEEKNUM calculation instead of static values.
• Clean Filters: We clear any existing filters before applying the new one to prevent unexpected behavior.
• Column Position: If your filter range starts at a different column, update the Field number to match K's position within that range (e.g., if your range is B1:L..., K is the 10th column, so use Field:=10).

If you had existing code that was overwriting the formulas, double-check for lines that use .Value to assign data to column K—replace those with .Formula to keep the dynamic calculation intact.

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