创建宏以将UPC E条形码转换为UPC A

Question

我的问题是，在第一个循环oldupc仍然被引用到A1而不是off设置为A2之后，我创建了msgboxes来跟踪循环中的进度。我需要对每个单元格进行偏移吗？

子ConvertUPCE()

Dim OldUPC As String
Dim NewUPC As String
Dim K As Double, i As Double
Dim L As Integer

OldUPC = ActiveCell.Offset(0, -2).Value
NewUPC = ActiveCell.Offset(0, 1).Value
K = Cells(Rows.Count, "A").End(xlUp).Row
L = ActiveCell.Value
    MsgBox (K)
'MsgBox OldUPC
'MsgBox NewUPC
'MsgBox L

Range("c1").Select

 For i = 1 To 11

 If (L >= 0 And L <= 3) Then
      ActiveCell.Value2 = "'" & Mid(OldUPC, 1, 1) & Mid(OldUPC, 2, 2) & Mid(OldUPC, 7, 1) & "0000" & Mid(OldUPC, 4, 3)
      ActiveCell.Offset(1, 0).Select


       ElseIf (L = 4) Then
         ActiveCell.Value2 = "'" & Mid(OldUPC, 1, 1) & Mid(OldUPC, 2, 3) & "00000" & Mid(OldUPC, 5, 2)

       ElseIf (L = 5) Then
         ActiveCell.Value2 = "'" & Mid(OldUPC, 1, 1) & Mid(OldUPC, 2, 4) & "00000" & Mid(OldUPC, 6, 1)
         ActiveCell.Offset(1, 0).Select

       ElseIf (L >= 6 And L <= 9) Then
         ActiveCell.Value2 = "'" & Mid(OldUPC, 1, 1) & Mid(OldUPC, 2, 5) & "0000" & Mid(OldUPC, 7, 1)
         ActiveCell.Offset(1, 0).Select


         Else: ActiveCell.Value = "Not Vailid Check Digit"

      End If

      'MsgBox OldUPC
      MsgBox OldUPC

 Next i

Answer 1

我建议不要使用ActiveCell，而是使用迭代整数作为单元格行。

如下所示：Cells(i, 3).value2 = ...，其中i是行号，3是列号(因为您在C列中)。

然后，您可以使用Cells(i,3).Offset(1,0).select进行偏移。

这也会使代码运行得更快，因为在操作数据之前不会选择每个单元格。

提示:如果您想迭代行和列，您可以执行以下操作，而不必担心选择了什么以及下一步是什么：

For RowNum = 1 to 11
    For ColNum = 1 to 11
        Cells(RowNum, ColNum).value = ""
    Next ColNum
Next RowNum

-So这将遍历11行中的每行11列！