Is there any way in Excel VBA to dynamically change rows number with cursor in the code but not the column number?

Question

Dear VBA/Macro Experts,

I have a task to fetch the data based on either first column barcode or second column SKU code. It will fetch details from Master Sheet for PRODUCT DETAILS and Selling Price and then an input box prompt will display to enter quantity, once I enter quantity it will calculate Amount by multiplying selling price and Quantity. I created a shortcut key to execute the code.

Below attached Image reference of my columns and when I click to run my code it does everything and starts from a fresh row again. But my requirement is when I click shortcut key it should jump to the column where formula should apply, in this case, column C and D. I'm not able to get the right way where when I enter shortcut key it should starts from column C and D and the cursor should end at in the next row first column. Because right now I'm manually moving my cursor after entering my barcode or SKU code and then moving my cursor to C to apply shortcut key. But it should be dynamically applied to all rows of the same column.

my code:

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

In what range do you try doing that? In what column do you intend putting of that formulas? — FaneDuru, Mar 03 '20 at 13:02
As mentioned in the code, Formula starts from Column C and then next goest to Column D to apply another vlookup formula. Remaming working properly for me. When i press shortuct key again my cursor should be at column c again to apply the formula. but can i do something where when i press shortcut key it starts from Column C and then goes to Column D (dynamically) without bothering which row I'm working on. — Techsearch, Mar 03 '20 at 13:04
Which range that initial `ActiveCell` must be? In "C2"? Firstly, no need to select anything... `ActiveCell.Offset(0, 1).Range("A1").Select` is a strange construction. So, you would like to have formulas in column C:C and D:D, from the second row to what row? — FaneDuru, Mar 03 '20 at 13:08
Initially, it should start from Column A(only for the first time) but from the very next time it should start from Column C on the event. — Techsearch, Mar 03 '20 at 13:09
Please, try to focus on what you need to accomplish. How to do that, we will see after understanding of your need. I must confess I cannot understand from your code what is to be done... — FaneDuru, Mar 03 '20 at 13:11
"start from Column C on the event" - so you want this code run when something happens? You should read https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — SJR, Mar 03 '20 at 13:28

score 1 · Accepted Answer · answered Mar 03 '20 at 18:52

I would determine the row from the ActiveCell and then use column numbers to fill the row rather than moving the activecell along. For example

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

Is there any way in Excel VBA to dynamically change rows number with cursor in the code but not the column number?

1 Answers1