Batch Edit Cells Using Macros

Question

I’m trying to create a simple macro where the user can batch edit cells by entering a reference number.

Here is the idea. Suppose I want to track shipments for different items on my inventory. There are 4 columns on my spreadsheet (Item#, Product Description, Order Date, Delivery Date) and hundreds of rows. All this data is on sheet1. On sheet2, I had set up the action panel, where the user can input the item#, select which action to fill in (order date or delivery date) and input the date.

While I record the macro, I am having trouble with getting to the destination cell where I want the information to be automatically be filled in. For example, suppose I want to fill in the delivery date for item 235. How do I tell excel to go to that cell? (Column of Delivery Date, Row of Item 235). Given that I want batch record cells, how do I repeat the above for every item?

IMPORTANT NOTE: I am a beginner when it comes to Macros and I barely understand VBA code. So please don’t involve code language.

Answer 1

Let's say that you enter your sets of 4 values in cells in rows 4 to 7, starting in column B with your item numbers in row 4.  And you want to find the item# in column A of Sheet1.  Here is one way to do that:

Sub TestMacro()

    Dim rngC As Range

    Dim rngF As Range

    Dim r As Long

    With Worksheets("Sheet2")

        Set rngF = .Range(.Range("B4"), .Cells(4, .Columns.Count).End(xlToLeft))

    End With

    For Each rngC In rngF

        r = Worksheets("Sheet1").Range("A:A").Find(rngC.Value).Row

        MsgBox "I found """ & rngC.Value & """ in row " & r

        Worksheets("Sheet1").Cells(r, "B").Value = rngC.Offset(1, 0).Value

        Worksheets("Sheet1").Cells(r, "C").Value = rngC.Offset(2, 0).Value

        Worksheets("Sheet1").Cells(r, "D").Value = rngC.Offset(3, 0).Value

    Next rngC

End Sub