excel - Excel VBA中是否有任何方法可以在代码中使用光标而不是列号动态更改行号？

Question

尊敬的 VBA/宏专家，

我的任务是根据第一列条形码或第二列 SKU 代码获取数据。它将从主表中获取产品详细信息和销售价格的详细信息，然后将显示一个输入框提示输入数量，一旦我输入数量，它将通过将销售价格和数量相乘来计算金额。我创建了一个快捷键来执行代码。

下面附上我的列的图像参考，当我单击运行我的代码时，它会执行所有操作并再次从新行开始。但我的要求是，当我单击快捷键时，它应该跳转到应该应用公式的列，在这种情况下，是 C 列和 D 列。我无法找到正确的方法，当我输入快捷键时，它应该从列开始C 和 D 以及光标应在下一行第一列结束。因为现在我在输入条形码或 SKU 代码后手动移动光标，然后将光标移动到 C 以应用快捷键。但它应该动态应用于同一列的所有行。

我的代码：

Sub EasyTool()
'
' EasyTool for Platina Sales
'
' Keyboard Shortcut: Ctrl+q

'
  'This will apply vlookup formula in column C and D

    ActiveCell.Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-2]),VLOOKUP(RC[-1],master_data!R1C2:R1000C3,2,FALSE),VLOOKUP(sale!RC[-2],master_data!R2C1:R1000C3,3,FALSE))"
    ActiveCell.Offset(0, 1).Range("A1").Select

    ActiveCell.FormulaR1C1 = _
        "=IF(ISBLANK(RC[-3]),VLOOKUP(RC[-2],master_data!R2C2:R1000C7,6,FALSE),VLOOKUP(RC[-3],master_data!R1C1:R1000C7,7,FALSE))"

    ActiveCell.Offset(0, 1).Range("A1").Select

   'This is will immediatly prompt input box to enter quantity after the above operation

    myValue = InputBox("Enter Quantity")
    ActiveCell.Value = myValue

   'This will move cursor to the next column for calculation

    ActiveCell.Offset(0, 1).Range("A1").Select

    'This will calculate the Value of Quantity * Selling price

    myMultivalue = "=RC[-2]*RC[-1]"
    ActiveCell.Value = myMultivalue

    'This will move cursor to the next row, first column

    ActiveCell.Offset(1, -5).Range("A1").Select


End Sub

标签： excelvba

Answer 1

我会从 ActiveCell 中确定行，然后使用列号来填充行，而不是移动 activecell。例如

Option Explicit
Sub EasyTool2()

    ' Product Details
    Const v1 = "VLOOKUP(RC[-1],master_data!R1C2:R1000C3,2,FALSE)"
    Const v2 = "VLOOKUP(sale!RC[-2],master_data!R2C1:R1000C3,3,FALSE)"
    ' Prices
    Const v3 = "VLOOKUP(RC[-2],master_data!R2C2:R1000C7,6,FALSE)"
    Const v4 = "VLOOKUP(RC[-3],master_data!R1C1:R1000C7,7,FALSE)"

    Dim ws As Worksheet
    Dim EAN As String, SKU As String, iRow As Long

    Set ws = ActiveSheet
    iRow = ActiveCell.Row

    If ActiveCell.Parent.Name = "master_data" Then
        MsgBox "ActiveSheet must not be master_data", vbCritical
        Exit Sub
    End If

    ' Quantity col E
    ws.Cells(iRow, 5).Value = InputBox("Enter Quantity")

    EAN = ws.Cells(iRow, 1)
    SKU = ws.Cells(iRow, 2)
    If Len(EAN) > 0 Or Len(SKU) > 0 Then
        ' product details col C
        ws.Cells(iRow, 3).FormulaR1C1 = "=IF(ISBLANK(RC[-2])," & v1 & "," & v2 & ")"
        ' selling price col D
        ws.Cells(iRow, 4).FormulaR1C1 = "=IF(ISBLANK(RC[-3])," & v3 & "," & v4 & ")"
        ' total F
        ws.Cells(iRow, 6).FormulaR1C1 = "=RC[-2]*RC[-1]"
    Else
        MsgBox "Column A and B both blank", vbCritical
        Exit Sub
    End If

    ' move to next row
    ws.Cells(iRow + 1, 1).Select

End Sub