Vlookup with Named ranges

**oakman** · 01-14-2009, 12:30 PM

Greetings and thank you in advance for any help provided. I am having problems getting the code below to follow my intentions. I have 2 files. I have a temporary file that holds all the data that I need. I have a destination file that needs to have data copied into it. In the temp file, I have data for several dates and product types. What I intend to do is to do a vlookup in VBA to look for the date and the product type in the temp file and copy the appropriate data to the destination file. I have numerous named ranges both in my temp file and my destination file. For the code below, I wanted to make a loop to find the date in the temp file that is listed in the destination file. Once this is done, I wanted to find the product type in the temp file that is listed in the destination file. If both conditions exist, then copy a certain range from the temp file to the destination file. My intention is evident in the code, but I don’t think that I am putting in the correct “code format”. Can any one help?
Thank you

Public Sub MacroOption2()

    Dim sh As Worksheet
    Dim Day As String
    Dim rFoundIt As Range
    Dim iLoop As Integer

Application.ScreenUpdating = False

    ThisFile = ThisWorkbook.Name
    PathName1 = Sheets("SUMMARY").Range("TempPath1").Value
    Filename1 = Sheets("SUMMARY").Range("TempFile1").Value
    DestPath1 = Sheets("SUMMARY").Range("DestPath1").Value
    DestFile1 = Sheets("SUMMARY").Range("DestFile1").Value
    Date1 = Sheets("BGE Template Options").Range("Date1").Value
    ProductPRL = Sheets("GE Template Options").Range("ProductPL").Value
    ProductPRX = Sheets("GE Template Options").Range("ProductPX").Value
    
    Day = CStr(Sheets("SUMMARY").Range("DayValGE").Value)

 Workbooks.Open Filename:=PathName1 & Filename1
    With Sheets("BE").Range("Data")
        'Set variable to start search from
            Set rFoundIt = Cells(1, 1)
                'Loop no more times than the date occurs
                    For iLoop = 1 To WorksheetFuntion.CountIf_
                        Sheets("BE").Range ("Data"), .Range("Date1").Value
                        
        'Reset variable to found occurence of date to start next loop search
        'next loop search will start AFTER this Set cell
        Set rFountIt = .Find(What:= .Range("Date1").Value, After:=rFountIt,_
        LookIn:=xlValues,LookAt:=xlWhole,SearchOrder:=xlByRows,_
        SearchDirection:=xlNext,MatchCase:=False)
        
        'Check for product type after finding correct date
        If rFoundIt.Offset(1, 0).Value = .Range("ProductPL").Value Then
            Range("E5:E28").Select
                Selection.Copy
            
        Windows(ThisFile).Activate
        Set sh = Worksheets(Day)
            sh.Range("H7:H30").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
       
       Windows(Filename1).Activate
       
    Next iLoop
    End With
                
    Windows(Filename1).Activate
        ActiveWorkbook.Save
        ActiveWindow.Close
End Sub

**Leith Ross** · 01-14-2009, 02:29 PM

Hello Oakman,

I made quite a few changes to your macro. The string variable "Day" has been changed to "D" because Day is a VBA keyword. The loop has been changed to search for all occurances of "Date1" and then perform the copy and paste between the workbooks. The code is more condensed and easier to read. Make a copy of your workbooks before you run the macro. I believe I understood what you wanted to do, but better to error on the safe side.

Public Sub MacroOption2()

    Dim FirstAddx As String
    Dim sh As Worksheet
    Dim D As String
    Dim rFoundIt As Range
    Dim iLoop As Integer

      Application.ScreenUpdating = False

      ThisFile = ThisWorkbook.Name
    
      With Sheets("SUMMARY")
        PathName1 = .Range("TempPath1").Value
        Filename1 = .Range("TempFile1").Value
        DestPath1 = .Range("DestPath1").Value
        DestFile1 = .Range("DestFile1").Value
        D = .Range("DayValGE").Text
      End With
    
      With Sheets("BGE Template Options")
        Date1 = .Range("Date1").Value
        ProductPRL = .Range("ProductPL").Value
        ProductPRX = .Range("ProductPX").Value
      End With
    

    Workbooks.Open FileName:=PathName1 & Filename1
     With Sheets("BE").Range("Data")
        Set rFoundIt = .Find(What:=.Range("Date1"), _
                       After:=.Cells(1, 1), _
                       LookAt:=xlWhole, _
                       LookIn:=xlValues, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
        If Not rFoundIt Is Nothing Then
           FirstAddx = rFoundIt.Address
              Do
               'Check for product type after finding correct date
                If rFoundIt.Offset(1, 0).Value = .Range("ProductPL").Value Then
                   .Range("E5:E28").Copy
                     ThisWorkbook.Sheets(D).Range("H7:H30").PasteSpecial _
                       Paste:=xlPasteValues, Operation:=xlNone, _
                       SkipBlanks:=False, Transpose:=False
                End If
                Set rFoundIt = .Find(rFoundIt)
              Loop While rFoundIt.Address <> FirstAddx And Not rFoundIt Is Nothing
        End If
     End With
     
    Application.ScreeenUpdating = True
    ActiveWorkbook.Save
    ActiveWindow.Close
    
End Sub

Sincerely,
Leith Ross

**oakman** · 01-14-2009, 03:05 PM

Leith, I appreciate your help. Thank you. However, I have run into a problem running the code. I got a Run-time error '1004':Application-defined or object-defined error and the following part of the code is highlited in yellow:

Set rFoundIt = .Find(What:=.Range("Date1"), _
                       After:=.Cells(1, 1), _
                       LookAt:=xlWhole, _
                       LookIn:=xlValues, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)

Could this error be because, I do not have a named range of "Date1" in the temp file?
What I figured is that the macro would look at the value of that named range in the destination file and go and find that value in the temp file.
Also, the dates in the temp files are formulas, does this matter?

**Leith Ross** · 01-14-2009, 03:11 PM

Hello Oakman,

There are quite a few possibilities why this is happening. It would be easier and faster for me to troubleshoot the error if you could post the workbook.

Sincerely,
Leith Ross

**oakman** · 01-14-2009, 04:16 PM

Leith, I am trying to attach the 2 files that I would be using. Please note that they have been edited since they are work files. I have just left the bare bones info necessary. Please know that I would be glad to answer any questions in regards to the files if they do not make sense. I really appreciate your willingness to help. Thank you

**oakman** · 01-14-2009, 04:20 PM

Leith, I keep getting a Database error when trying to attach the files. Is there any way that I can help by answering any questions that you might have?
I will keep on trying to make the attachements, but I have already tried a few times.

**Leith Ross** · 01-14-2009, 04:25 PM

Hello Oakman,

The forum servers are being updated You can email the file to me at LeithRoss@Gmail.com. I'll post the changes and hopefully the updated workbook.

Sincerely,
Leith Ross