database - 为什么这段代码有时有效，有时却无效？

Question

我从我的电气仓库中得到了这个宏，它可以为标记电路制作视觉辅助工具，我想识别它们，但有时它会起作用(仅在前两个标签中，然后，它在某些单元格中缺少信息，这是代码:

Public Sub VisualAids()
Dim DB_Range, StartVisuals As range
Dim Origen As range
Set DB_Range = Worksheets("DBCircuits").range("B2:K573")
Set Origen = Worksheets("visuales").range("C2")
Dim i, j, TotalLabels, LabelsCounter As Integer
Dim Labels As Integer
Dim CircuitName, Location, Color1, Color2, DailyReq, StdPack As String
TotalLabels = Application.WorksheetFunction.Sum(Worksheets("DBCircuits").range("K2:K573"))

For i = 1 To TotalLabels
CircuitName = DB_Range.Cells(i, 1)
Location = DB_Range.Cells(i, 8)
Color1 = DB_Range.Cells(i, 6)
Color2 = DB_Range.Cells(i, 7)
DailyReq = DB_Range.Cells(i, 3)
StdPack = DB_Range.Cells(i, 4)
Labels = DB_Range.Cells(i, 10)

For j = 1 To Labels
Origen.Offset(0, 1) = CircuitName
Origen.Offset(1, 1) = Location
Origen.Offset(3, 1) = DailyReq
Origen.Offset(4, 1) = StdPack

Select Case Color1
Case "0"
Origen.Offset(2, 3).Interior.Color = RGB(0, 0, 0)
Origen.Offset(2, 5).Interior.Color = RGB(0, 0, 0)
Case "1"
Origen.Offset(2, 3).Interior.Color = RGB(153, 102, 51)
Origen.Offset(2, 5).Interior.Color = RGB(153, 102, 51)
Case "2"
Origen.Offset(2, 3).Interior.Color = RGB(255, 0, 0)
Origen.Offset(2, 5).Interior.Color = RGB(255, 0, 0)
Case "3"
Origen.Offset(2, 3).Interior.Color = RGB(255, 102, 0)
Origen.Offset(2, 5).Interior.Color = RGB(255, 102, 0)
Case "4"
Origen.Offset(2, 3).Interior.Color = RGB(255, 255, 0)
Origen.Offset(2, 5).Interior.Color = RGB(255, 255, 0)
Case "5"
Origen.Offset(2, 3).Interior.Color = RGB(0, 255, 0)
Origen.Offset(2, 5).Interior.Color = RGB(0, 255, 0)
Case "6"
Origen.Offset(2, 3).Interior.Color = RGB(0, 176, 240)
Origen.Offset(2, 5).Interior.Color = RGB(0, 176, 240)
Case "7"
Origen.Offset(2, 3).Interior.Color = RGB(0, 176, 240)
Origen.Offset(2, 5).Interior.Color = RGB(0, 176, 240)
Case "8"
Origen.Offset(2, 3).Interior.Color = RGB(128, 128, 128)
Origen.Offset(2, 5).Interior.Color = RGB(128, 128, 128)
Case "9"
Origen.Offset(2, 3).Interior.Color = RGB(255, 255, 255)
Origen.Offset(2, 5).Interior.Color = RGB(255, 255, 255)
Case Else
Origen.Offset(2, 3).Value = "-"
Origen.Offset(2, 5).Value = "-"
End Select

Select Case Color2
Case "0"
Origen.Offset(2, 4).Interior.Color = RGB(0, 0, 0)
Case "1"
Origen.Offset(2, 4).Interior.Color = RGB(153, 102, 51)
Case "2"
Origen.Offset(2, 4).Interior.Color = RGB(255, 0, 0)
Case "3"
Origen.Offset(2, 4).Interior.Color = RGB(255, 102, 0)
Case "4"
Origen.Offset(2, 4).Interior.Color = RGB(255, 255, 0)
Case "5"
Origen.Offset(2, 4).Interior.Color = RGB(0, 255, 0)
Case "6"
Origen.Offset(2, 4).Interior.Color = RGB(0, 176, 240)
Case "7"
Origen.Offset(2, 4).Interior.Color = RGB(0, 176, 240)
Case "8"
Origen.Offset(2, 4).Interior.Color = RGB(128, 128, 128)
Case "9"
Origen.Offset(2, 4).Interior.Color = RGB(255, 255, 255)
Case Else
Origen.Offset(2, 4).Value = "-"
End Select
LabelsCounter = LabelsCounter + 1

If LabelsCounter Mod 2 = 0 Then
Origen.Offset(6, 1) = "Back visual"
'the next one would be down-left
Set Origen = Origen.Offset(11, -9)
Else
Origen.Offset(6, 1) = "Front visual"
'the next one would be right
Set Origen = Origen.Offset(0, 9)
End If

Next j
Next i
End Sub

用这个我假装填充这个单元格(视觉工作表)

DB_Range 如下(DBCircuits 工作表):

关键是有时电路名称没有添加到视觉效果中，也没有添加位置，你能帮我吗？我仍在努力寻找问题

Answer 1

尝试在这些行之后添加 .Value:Origen.Offset(0, 1) 所以它是 Origen.Offset(0, 1).Value =

所以你会:

Origen.Offset(0, 1).Value = CircuitName
Origen.Offset(1, 1).Value = Location
Origen.Offset(3, 1).Value = DailyReq
Origen.Offset(4, 1).Value = StdPack

(这适用于您拥有的单个或合并的单元格。)