Excel VBA开发需求：跨表判断赋值与批量记录表创建

Got it, let's work through these two Excel VBA tasks step by step. I'll provide clean, commented code for each requirement along with notes to adapt it to your specific workbook setup.

This macro checks a specified "other" worksheet for cells containing "Create" (case-insensitive) and writes "Yes" to the adjacent cell in your current worksheet.

Sub AddYesBasedOnOtherSheet()
    Dim wsCurrent As Worksheet
    Dim wsOther As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Update these sheet names to match your workbook
    Set wsCurrent = ThisWorkbook.Worksheets("YourCurrentSheet")
    Set wsOther = ThisWorkbook.Worksheets("OtherSheetWithCreateValues")
    
    ' Get the last row with data in the "other" sheet (adjust column if needed)
    lastRow = wsOther.Cells(wsOther.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row to check values
    For i = 1 To lastRow
        ' Check if the other sheet's cell is "Create" (ignores case)
        If LCase(wsOther.Cells(i, "A").Value) = "create" Then
            ' Write "Yes" to the adjacent cell in current sheet (column B here)
            wsCurrent.Cells(i, "B").Value = "Yes"
        Else
            ' Optional: Clear the cell if it's not "Create"
            wsCurrent.Cells(i, "B").ClearContents
        End If
    Next i
End Sub

Notes: #

• Replace YourCurrentSheet and OtherSheetWithCreateValues with your actual worksheet names.
• Adjust the column letters (e.g., "A" for the "other" sheet, "B" for the current sheet) if your data lives in different columns.
• The LCase function ensures the check works for any case variation (like "CREATE", "create", or "Create").

This macro builds a new "Batch records" sheet, copies the integer values from "35.s" Column A to "Batch records" Column B, and adds a boolean column to flag rows where "35.s" Column B is "CREATE".

Sub CreateBatchRecordsSheet()
    Dim wsSource As Worksheet
    Dim wsBatch As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Reference the source sheet "35.s"
    On Error Resume Next
    Set wsSource = ThisWorkbook.Worksheets("35.s")
    On Error GoTo 0
    
    ' Exit if source sheet doesn't exist
    If wsSource Is Nothing Then
        MsgBox "Source sheet '35.s' wasn't found!", vbExclamation
        Exit Sub
    End If
    
    ' Create or reference the "Batch records" sheet
    On Error Resume Next
    Set wsBatch = ThisWorkbook.Worksheets("Batch records")
    On Error GoTo 0
    
    ' Create the sheet if it doesn't exist; clear data if it does
    If wsBatch Is Nothing Then
        Set wsBatch = ThisWorkbook.Worksheets.Add(After:=wsSource)
        wsBatch.Name = "Batch records"
    Else
        wsBatch.Cells.Clear
    End If
    
    ' Get last row of data in "35.s" Column A (use 414 if you strictly want only A1-A414)
    lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row
    ' lastRow = 414 ' Uncomment this line if you need exactly rows 1-414
    
    ' Add headers to "Batch records"
    wsBatch.Cells(1, "A").Value = "Is CREATE?"
    wsBatch.Cells(1, "B").Value = "Source ID"
    
    ' Format headers for readability (optional)
    With wsBatch.Range("A1:B1")
        .Font.Bold = True
        .HorizontalAlignment = xlCenter
    End With
    
    ' Populate data into "Batch records"
    For i = 2 To lastRow ' Start at 2 if "35.s" has a header row; use 1 if no header
        ' Copy "35.s" Column A to "Batch records" Column B
        wsBatch.Cells(i, "B").Value = wsSource.Cells(i, "A").Value
        
        ' Set boolean flag based on "35.s" Column B
        If LCase(wsSource.Cells(i, "B").Value) = "create" Then
            wsBatch.Cells(i, "A").Value = True
        Else
            wsBatch.Cells(i, "A").Value = False
        End If
    Next i
    
    ' Auto-fit columns to make data easier to read
    wsBatch.Columns("A:B").AutoFit
    
    MsgBox "Batch records sheet is ready!", vbInformation
End Sub

Notes: #

• If "35.s" has a header row, keep the loop starting at i = 2; if there's no header, change it to i = 1.
• The code checks if "35.s" exists first to avoid errors, and handles both new and existing "Batch records" sheets.
• The boolean column (Is CREATE?) will show True for rows where "35.s" Column B is "CREATE" (any case), and False otherwise.

内容的提问来源于stack exchange，提问作者C. OBrien