Search through workbook and update

[danweeks20]

Hi all,

I have a spreadsheet that consists of a data entry sheet that copies the data line into the applicable sheet, i.e. if the date is 08/09/2013 it will copy the data into the September sheet.

I am trying to find a code, or help to code a macro that looks up the reference number (entered into a cell), finds the data amongst the workbook and allows updates a particular cell.

For example:

Reference Number: (Number is entered into cell)
Update with: (Drop down list of possible options)

The macro would then search for the data based on the reference number cell, and updates with the option selected in the cell below.
If the data is not found, it would return an error to the user, otherwise confirm it has been done.

The workbook sheets are;
Entry
January, February, March etc.

The cell to be updated is found in column J within each one of the month worksheets.

Thanks in advance,

Dan

 

[Teeroy]

Try the follow. It will only find the first occurrence of the Reference (I'm assuming it's a unique identifier) and update the value in column "J".

Sub UpdateReferenceData()
    Dim FindReference As String, UpdatedValue As String
    Dim Rng As Range
    FindReference = Sheets("Entry").Range("B1") 'assumed position
    UpdatedValue = Sheets("Entry").Range("B2") 'assumed position
    If Trim(FindReference) = "" Then Exit Sub
    For Each sht In Sheets
        If sht.Name <> "Entry" Then
            With sht.Range("A:A") 'assumed Reference in Column A
                Set Rng = .Find(What:=FindReference, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Rng.Offset(0, 9) = UpdatedValue 'update 9 columns to the right
                End If
            End With
        End If
    Next
End Sub

 

[puckhawg78]

Private Sub SaveWorkbookChanges()

'First you want to turn off screen updating so you don't see workbooks opening and closing, plus your code runs faster.

Application.ScreenUpdating = False

'Open the workbook you want to reference data from. Assuming the data is in a separate workbook.

On Error Resume Next
Workbooks("Workbook1.xlsx").Activate
If Err <> 0 Then Err.Clear: Workbooks.Open ("C:\Workbook1.xlsx") 'Or wherever your workbook is located

' Assuming your sheet you're looking up data in is called January
With Sheets("January").Activate

'What  this is going to do is look in column J of the separate workbook for  the value you are referencing from in your workbook you have open  already in the worksheet named January and is going to be looking at the  value in cell "J1".
Set c = .Columns("$J:$J").Find(ThisWorkbook.Sheets("January").Range("J1").Value, LookIn:=xlValues)

     If C Is Nothing Then

MsgBox "Nothing found.", Title:="No Data Found", Buttons:=vbOKOnly

'You will want to close the workbook you opened to look for the data
Workbooks("Workbook1xlsx").Close SaveChanges:=False

Exit Sub

     Else

Run the rest of your code here

     End If

'You will want to turn screen updating back on when you are done.
Application.ScreenUpdating = True

End Sub

I'm not really sure what you are trying to do with your changes so I said "Run the rest of your code here" but what I've given you should find the value you are looking for and that cell will become the "ActiveCell" in the separate workbook. The cell you are referencing must match exactly though or it's going to return the message box. I'm a bit confused with what you are actually trying to do so I apologize if I wasn't any help at all. It's unclear to me if the value you are looking for is in the same workbook or a separate workbook or if it's just in another worksheet in the same workbook.

Hope this gets you started...

 

[danweeks20]

Hi guys,

This worked like a dream! There is only the one workbook so the first code is great for what I am looking for and can be manipulated as I progress through.
Is there a way to give the user a confirmation or error depending on whether the update was successful?

Thanks, really appreciate it

Dan

 

[danweeks20]

Hi guys,

This worked like a dream! There is only the one workbook so the first code is great for what I am looking for and can be manipulated as I progress through.
Is there a way to give the user a confirmation or error depending on whether the update was successful?

Thanks, really appreciate it

Dan

Hi Guys,

Thanks again, I have worked out why my code was not working for the message boxes, which I have now rectified and is working.

Cheers