Auto Data Search and Entry

**Snipeston** · 03-04-2010, 12:59 AM

I have a complex search and data entry based on that search.

Probably will be easier to look at example sheet to follow along.

I would like a program or macro to automatically do the following:

1. (Find VIN) - Take value in Optimizer Column Cx and search in Daily Purchases Column Ex for the exact match.
2. (Enter BSRP) - Once Optimizer Column Cx value found in Daily Purchases Column Ex, I would like the value from Optimizer Jx to be entered into Daily Purchases in Column A, four rows down from where the value from Optimizer Column Cx was found.
3. (Enter Location/Destination) - Similar to above, but value from Optimizer Column Hx
to be entered into Dalily Purchase Column J, six rows down from where the value from Optimizer Column Cx was found.

So, I would like it to start with Row 2 on the Optimizer sheet and automatically go down the list, repeating the above with each VIN. The number of rows will vary each day the report runs, but should never exceed row 200.

Any help would be greatly appreciated.

**JBeaucaire** · 03-04-2010, 01:13 AM

Don't need a macro, just a formula already in the Daily Purchases cells. These will draw in the matching data from the Optimizer sheet as soon as you enter a VIN.

**Snipeston** · 03-04-2010, 10:55 AM

That might do it. Let me know if this changes anything:

Both sheets come pre filled out in different orders from external sources. How can I copy the data into this template and keep the formatting, etc. When I try to copy the data in, it comes up with an error regarding merged cells must be the same width??? The pages are identical, so I do not understand the error.

Can this process be automated to some extent to help my staff that aren't so Excel savvy?

**JBeaucaire** · 03-04-2010, 02:03 PM

This is why I never design a sheet with merged cells, creates all sorts of havoc.

I can't offer suggestions on a work process I don't fully understand. You'll have to resolve the issue of getting the data into the same workbook.

This macro will insert the formulas for you into the Daily Purchases sheet.

Code:

Option Explicit

Sub AddFormulas()
Dim LR As Long, FR As Long, i As Long
    
    With Sheets("Daily Purchases")
        FR = Cells.Find(What:="BSRP", After:=.Range("A1"), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Row
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        
        For i = FR To LR Step 9
            .Range("A" & i).FormulaR1C1 = _
                "=INDEX(Optimizer!C10, MATCH(R[-4]C5, Optimizer!C3, 0))"
            .Range("J" & i + 2).FormulaR1C1 = _
                "=INDEX(Optimizer!C8, MATCH(R[-6]C5, Optimizer!C3, 0))"
        Next i
    
    End With

End Sub
Sub Macro11()
'
' Macro11 Macro
' Macro recorded 3/4/2010 by JB
'

'
    Cells.Find(What:="BSRP", After:=Range("A1"), LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range("A13:M13").Select
    Application.Run "'Purchase Report-1.xls'!Macro11"
    Range("P4").Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Selection.End(xlToLeft).Select
    Range("A6:B6").Select
    Cells.Find(What:="BSRP", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Range("A9:B9").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Cells.Find(What:="BSRP", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
End Sub

Thread: Auto Data Search and Entry

Thread Tools

Display

Auto Data Search and Entry

Re: Auto Data Search and Entry

Re: Auto Data Search and Entry

Re: Auto Data Search and Entry

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions