Extracting data - vlookups with closed Sheets

Hi,

See below ... since topic is quite frequent ...

http://www.excelforum.com/2257101-post4.html

HTH

Thank you for that but I cannot find the link from your post.

Libby

Hi,

Below is the latest link ...
http://xcell05.free.fr/english/index.html

HTH

Hi Libby

You may want to try the attached files, but firstly you will need to change the path of the MAT and CAP files within the code.

Also for this to work you need to make sure that the headings in the two files are identical. I have made a note in the IF file regarding the column headers I had to change in the MAT and CAP files.

Also the previous macro was based on there being information in the first column of IF Sheet1, but I have now changed this to be dependent on the CAP/Client No as I was unable to populate the Project Manager name.

Please let me know how you get on.

Morning,

Thanks you very much.

It is stopping at the bit in code that says

Workbooks.Open CAP

Also how do I add other columns to copy.

I will need quite a few more but I presume I can simple add these.

You are an absolute star by the way.

Thanks
Libby

Morning Libby,

You are welcome.

It is probably stopping at that code because you need to change the following code

    'You need to change this to the relevant path for the CAP and MAT files
    
    CAP = "C:\temp\CAP Sample.xls"
    MAT = "C:\temp\03-01 MATS agreements sample.xls"

or copy the sample files to the C:\temp directory.

Currently the code expects the CAP file to be in the C:\temp directory with filename CAP Sample.xls and the MAP file to be in the same directory with the sample filename.

I don't know if you intend for the user to go and locate the CAP and MAT files themselves. If that is the case then you would probably want the code above to be

        
    CAP = Application.GetOpenFilename(,,"Please locate the CAP file to be used")
    MAT = Application.GetOpenFilename(,,"Please locate the MAT file to be used")

For the additional columns as long as the two files have the same column headings, you should be able to add as many as you need. At the moment, I think the only limitation is that the code relies on the IF file having more columns (with data) than the other two files. If it is likely that this would not always be the case, please let me know.

Regards

Sorry my fault, wrong path name.

Its not retrieving anything now. I think its me. I have changed the path and it is not debugging but it is not retrieving.

Have I done something.

Sub UpdateData()
    Dim wsIf1 As Worksheet:  Dim wsIf2 As Worksheet
    Dim wsCap As Worksheet: Dim wsMAT As Worksheet
    Dim wbCap As Workbook: Dim wbMAT As Workbook
    Dim cIf1(40) As Variant: Dim cCap(40) As Variant: Dim cMAT(40) As Variant
    Dim rIf1 As Range: Dim rMAT As Range: Dim rCap As Range
    
    Set wsIf1 = Worksheets("Sheet1")
    Set wsIf2 = Worksheets("Sheet2")
    
    'You need to change this to the relevant path for the CAP and MAT files
    
    CAP = "D:\Documents and Settings\Olivia-Trimble\Desktop\CAP sample.xls"
    MAT = "D:\Documents and Settings\Olivia-Trimble\Desktop\03-01 MATS agreements sample.xls"
    
    Application.ScreenUpdating = False
    
    Workbooks.Open CAP
    Set wbCap = ActiveWorkbook
    Set wsCap = ActiveSheet
    ActiveWindow.Visible = False
    
    Workbooks.Open MAT
    Set wbMAT = ActiveWorkbook
    Set wsMAT = ActiveSheet
    ActiveWindow.Visible = False
    
    If1Col = wsIf1.UsedRange.Columns.Count
    
    'Determine the column matches between the two sheets (IF and CAP)
    a = 1
    For x = 1 To If1Col
        For y = 1 To If1Col
            If wsCap.Cells(1, x) = wsIf1.Cells(5, y) Then
                cCap(a) = wsCap.Cells(1, x).Column
                cIf1(a) = wsIf1.Cells(1, y).Column
                a = a + 1
                Exit For
            End If
        Next y
    Next x

    'Update Sheet1 of the IF workbook with the matching data from CAP workbook
    For Each rIf1 In wsIf1.Range("D5:D" & wsIf1.UsedRange.Rows.Count)
        If rIf1.Value <> "" Then
            For Each rCap In wsCap.Range("C2:C" & wsCap.UsedRange.Rows.Count)
                If rCap = rIf1 Then
                    For z = 1 To a - 1
                        wsIf1.Cells(rIf1.Row, cIf1(z)) = wsCap.Cells(rCap.Row, cCap(z))
                    Next z
                    Exit For
                End If
            Next rCap
        End If
    Next rIf1
    
    'Determine the column matches between the two sheets (IF and MAT)
    a = 1
    For x = 1 To If1Col
        For y = 1 To If1Col
            If wsIf1.Cells(5, y) <> "" Then
                If wsMAT.Cells(1, x) = wsIf1.Cells(5, y) Then
                    cMAT(a) = wsMAT.Cells(1, x).Column
                    cIf1(a) = wsIf1.Cells(1, y).Column
                    a = a + 1
                    Exit For
                End If
            End If
        Next y
    Next x

    'Update Sheet1 of the IF workbook with the matching data from MAT workbook
    For Each rIf1 In wsIf1.Range("D5:D" & wsIf1.UsedRange.Rows.Count)
        If rIf1.Value <> "" Then
            For Each rMAT In wsMAT.Range("A2:A" & wsMAT.UsedRange.Rows.Count)
                If rMAT = rIf1 Then
                    For z = 1 To a - 1
                        wsIf1.Cells(rIf1.Row, cIf1(z)) = wsMAT.Cells(rMAT.Row, cMAT(z))
                    Next z
                    Exit For
                End If
            Next rMAT
        End If
    Next rIf1
    wbCap.Close False
    wbMAT.Close False
    UpdateSheet
End Sub

Libby

Thats better if we can locate the file but its not retrieving anything.

It did once then not again. I'm sure it is me.

Libby

Let me see what I am doing as I think it because I have selected a file with wrong column headers.

I'm so chuffed. Would have taken me ages as only new to VBA. Trying to learn.

Thanks
Libby

Sorry, I presume this will return things no matter how many rows there are?

Its me! I know it. It is asking me which file etc then it is opening but not retrieving. I checked the headings were the same but still not retrieving. Could it be because I am on Excel 2003?
Libby

Is it based on some data already being in the CAP column?

That's odd, as it works with the sample files I have here. Also I'm working with Excel 2003, so that shouldn't be the problem.

Are you still using the sample files or are you using your actual files at the moment, because if that is th case, you may need to amend the worksheets it refers to for the IF files.

Thats what it was. I deleted the CAP. Worked it out eventually.

You are an absolute star.

Thanks you so much.

Libby

Yes it is based on there being data in the CAP column.

Hi there,

Me again. Is there anyway it can be speeded up. I have 40, 000 rows and its crashing. i think to be fair its going to be that way because of the level of data but just thought I would check.

Libby

Hi Libby

I have made an amendment to the code, as I've done something similiar in other post.

Hopefully the change should result in a better run time.

Please try it and let me know how you get on.

Regards

You star. Thank you very much.

I am trying to learn but I don't seem to be doing very well.

All your help is very much appreciated.

Thanks agian

Hi there,

Sorry for the delay. I have had a lot of problems. I have pasted the code into the word doc. I added 2 columns in with SLFF start period and SLFF end period in next to the project end date too.

Hopefully you may be able to see what I have done.

Thank you.

Libby

Hi Libby

One thing I did find was that you are using an older version of the code, so the data was being determined first then the columns were populated, as a result you had gone back to the situation we had earlier where you had to run the code three times before it had all the results you wanted.

I have change the code and attached it to a separate file on its own with a command bar button. This means you now have to locate the IF file, like you do for the MATs and CAPs file.

Please let me know if you have any problems.

Regards

Hi Libby

Please find attached the revised file with the errant link to the 'xla' file removed.

Regards

Here's the screena shot for the error
Thanks again
Libby

Hi Libby

I don't think I will be messing with add-ins for a while.

I have now attached a newly created file, which reassigns the macro to the button on open. The new button is now just called Update.

This should work now.

Regards

Hi again.

I'm so sorry I seem to keep messing up your code.

I've simply added this to the blank sheet that I would use really it is only to show you the additions to the code.

If I can get these additions in and it works that will be fab. It seems to be corrupting and not doing anything now.

It did work though.
Sorry

Hi Libby

I think I have resolved all the anomalies. I have attached the file (IF master file) and the code file, these two work with the last CAP and MAT files.

Please let me know how you get on.

Hi there,

Its still not bringing across project renewal and the compliancy now.

I had a version that worked perfectly and my computer corrupted it so maybe its me.

Thank you so much

Hi Libby

The code was working on the number of columns populated in the IF file which previously had more columns that either the MAT or CAP file, but this is no longer the case. So I've made an adjustment to pick up 20 more columns than the number of columns in the IF file.

And the reason why the 'AW' column has no values is that is being determined based on values in column 'O' (Code closed for time) which has no values in it. I have amended this to look at column 'P' (commission) as I think that is most probable column which should be evaluated.

I have amended the code for the columns which are not being picked up, but you would probably need to close down excel, so that you are sure you are using the current version.

Please let me know if it works.

Kind Regards

Thank you. I have got it to work. I think I had so many versions of this that you are right I think I had the wrong versions open.

Thanks so much for all your help.