Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014

    Unanswered: Find and update in different column

    Hello everybody.
    I'm struggling with setting up my table and automatically update it through commands. I have two pages of excel 2007 file. Sheet1 - I'm entering the current date in A9, and entering product No (1-3 digit) in A16.
    My Sheet2 has table with 8 columns. Column A with Product Numbers. Column F with dates that I need to update.
    I want to run search from Sheet1 for exact match on column A on Sheet2 (referencing on A16 sheet1), then go horizontally to Column F, and update the cell with the date for this product (referencing on A9 sheet1).
    I have been trying to record macro using find feature, but so far with no luck. Looks like when I copy cell A16 and paste it to Find feature, it gets stuck, and even if reenter different number in A16, it still runs as previous entry. I'm not very proficient with VBA coding, and just know the basic.
    Any help would really help.
    Thank you

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you are more likely to get a response if you provide a ZIPped copy of your spreadsheet stripped of any sensitive information
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Sorry for that. I'm attaching my file.
    Attached Files Attached Files

  4. #4
    Join Date
    Sep 2014
    try using Match to return the Row number.

    Add a button to sheet one and attach this macro for a starting point.

    Sub UpdatePurchaseDate_click()
    ' UpdatePurchaseDate_click Macro
    ' copy the Date Value in Sheet1!A9 to the Last Purchase Date in Sheet2 in coloumn F and the row returned by the MATCH function.
        Dim UpdateRange As String
        UpdateRange = "F" & Application.WorksheetFunction.Match(Sheets("Sheet1").Range("A16").Value, Sheets("Sheet2").Range("A:A"))
        Sheets("Sheet2").Range(UpdateRange).Value = Sheets("Sheet1").Range("A9").Value
        MsgBox "Updated.", , "Update Last Purchase Date"
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts