VBA用不同的文本替換多個范圍-有解無憂

我對 VBA 代碼不是很熟悉，希望有人能幫助我處理這段代碼。

我試圖用 3 個不同的值替換 3 個不同的范圍。

我得到的當前代碼似乎只執行第一行，最后兩行被忽略

Private Sub CommandButton3_Click()
Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
Range("C2, F2, F21") = "Select 1"
Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
End Sub

任何幫助或指導將不勝感激！提前致謝

這是整個代碼；

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    If Me.Range("B56").Value = 0 Then
    Dim xsheet As Worksheet
    Set xsheet = Sheets("Heroes_list")
        
            If xsheet.Name <> "Definitions" And xsheet.Name <> "fx" And xsheet.Name <> "Needs" Then
                xsheet.Range("A2:AC2").Copy
                xIntR = xsheet.UsedRange.Rows.Count
                xsheet.Cells(xIntR   1, 1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                MsgBox "Saved Successfully!"
            End If

        Application.ScreenUpdating = True
        
Else
    If Me.Range("B56").Value = 1 Then _
        
    MsgBox "Not allowed, please revise costs, conflicts and all fields are filled correctly."
     End If

        Application.ScreenUpdating = True
 End If

        Application.ScreenUpdating = True
End Sub


    


Private Sub CommandButton2_Click()

Application.DisplayAlerts = False
Worksheets("Heroes_list").Copy '  golly im good
With ActiveWorkbook
    ChDir "C:\Users\Evane\Documents\Calix\Photoshop_excel_imports\"
    .SaveAs Filename:="C:\Users\Evane\Documents\Calix\Photoshop_excel_imports\hero" & ".txt", FileFormat:=xlText, CreateBackup:=False
    .Close False
End With
Application.DisplayAlerts = True
MsgBox "Exported successfully!"

End Sub

Private Sub Worksheet_Calculate()
If Me.Range("B52").Value = 1 Then _
MsgBox "The current effect combination for this PRIMARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
End

If Me.Range("B54").Value = 1 Then _
MsgBox "The current effect combination for this SECONDARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
End


If Me.Range("A49").Value > 1 Then _
MsgBox "Element conflict! The same element cannot be listed under both weakness and resistance!"
End
End Sub

Public Sub PasteasValue()
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("b56") = 1 Then Sheets("Hero_creation").CommandButton1.BackColor = RGB(255, 0, 0) 'Red!
If Range("b56") = 1 Then Sheets("Hero_creation").CommandButton1.Font.Strikethrough = True  'strikethrough text
If Range("b56") = 0 Then Sheets("Hero_creation").CommandButton1.BackColor = RGB(0, 255, 0) 'Green!
If Range("b56") = 0 Then Sheets("Hero_creation").CommandButton1.Font.Strikethrough = False 'No strik through text
End Sub

'New Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.address = "$B$2" Or Target.address = "$F$1" Or Target.address = "$F$20" Or Target.address = "$C$2" Or Target.address = "$F$2" Or Target.address = "$F$3" Or Target.address = "$F$4" Or Target.address = "$F$5" Or Target.address = "$F$21" Or Target.address = "$F$22" Or Target.address = "$F$23" Or Target.address = "$F$24" Or Target.address = "$C$3" Or Target.address = "$C$4" Or Target.address = "$C$5" Or Target.address = "$C$6" Or Target.address = "$C$7" Or Target.address = "$C$8" Or Target.address = "$C$9" Or Target.address = "$C$10" Then
        If Target.Value = "" Then  '  case a cell was emptied
         Let Application.EnableEvents = False
         Let Target.Value = "Enter Text Here..."
         Let Application.EnableEvents = True

        Else  '  case a text was entered
            With Target.Font
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            End With
        End If
    Else
    ' Target is Not a cell to be acted on
    End If
End Sub

Private Sub CommandButton3_Click()
    Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
    Range("C2, F2, F21") = "Select 1"
    Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
    End Sub

uj5u.com熱心網友回復：

在您的Worksheet_Calculate事件中，您If在Ends中缺少以End陳述句結尾的s ，這會立即停止整個程序

之后我也洗掉_了Then。

完整代碼如下：

Private Sub Worksheet_Calculate()
    If Me.Range("B52").Value = 1 Then
        MsgBox "The current effect combination for this PRIMARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
    End If
    
    If Me.Range("B54").Value = 1 Then
        MsgBox "The current effect combination for this SECONDARY ATTACK is invalid,Please ensure that if negative effects are listed, the list starts with atleast 1 negative status effect or that the effect list starts at line 1."
    End If
        
    If Me.Range("A49").Value > 1 Then
        MsgBox "Element conflict! The same element cannot be listed under both weakness and resistance!"
    End If
End Sub

我假設Worksheet_Calculate被觸發是因為您有來自受更改影響的其他單元格的公式，因此如果您不想觸發它，請操作Application.EnableEvents屬性：

Private Sub CommandButton3_Click()
    Application.EnableEvents = False
    Range("B2, F1, F3:f5, F20, F22:F24") = "Enter Text Here..."
    Range("C2, F2, F21") = "Select 1"
    Range("C3:C10, B13:B19, B22:B25, A29:D33,F7:F16, F26:F35").ClearContents
    Application.EnableEvents = True
End Sub

uj5u.com熱心網友回復：

嘗試使用： Range().Value = ""

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/339738.html

標籤：擅长 vba

上一篇：在檢查數字是否>0之前洗掉數字的第一個字符，結合間接函式

下一篇：（VBA到VB.NET代碼修改）使用VB.NET將特定單元格的值寫入作業簿