我如何更好地编写代码以使其看起来更整洁 VBA

Question

我正在尝试弄清楚如何更好地编码。我试图让我的代码更整洁，但我只是不知道该怎么做。

Private Sub Test()
'md = 30
'
'If mbi.Value = False Then
'
'    Sheet1.Cells(14, "C").Value = Sheet1.Cells(36, "t").Value * md * N1.Value
'    Sheet1.Cells(14, "D").Value = Sheet1.Cells(36, "u").Value * md * N1.Value
'    Sheet1.Cells(14, "E").Value = Sheet1.Cells(36, "v").Value * md * N1.Value
'    Sheet1.Cells(14, "F").Value = Sheet1.Cells(36, "w").Value * md * N1.Value
'    Sheet1.Cells(14, "G").Value = Sheet1.Cells(36, "x").Value * md * N1.Value

这是我尝试过的，但我一直有错误

Private Sub test() 
md = 30

If Sheet1.mbi.Value = True Then 
With Sheet1
Dim rng As Range
Dim rng2 As Range
Dim x As Long
Set rng = .Range("C14:G14")
Set rng2 = .Range("t36:x36")
For x = 1 To rng.Cells.Count

rng.Cells(1).Value = (rng2.Cells(1).Value * md * Sheet1.N1.Value)
End With
End If
End Sub

Answer 1

您当前的代码无法编译，因为您缺少Next。要编写一段更整洁的代码，它可能看起来像：

Sub test()
Dim x As Long, md As Long: md = 30
Dim rng As Range, rng2 As Range
With Sheet1
If .mbi.Value = True Then
Set rng = .Range("C14:G14")
Set rng2 = .Range("T36:X36")
For x = 1 To rng.Cells.Count
rng.Cells(x).Value = (rng2.Cells(x).Value * md * .N1.Value)
Next x
End If
End With
End Sub

_{注意：我还编辑了您的rng.Cells(1).Value = (rng2.Cells(1).Value * md * Sheet1.N1.Value)，因为我认为您想实际使用x变量。}