Using VLookup In VBA 1

[jhabey01]

Hello and thanks for looking:

I have the following
1) 15 or so excel files in a folder like the attachment BostonSPS. The only thing different is the name of the file and the number of rows can month to month. This is important because I need to have the code look to the last row (and it is not always the same row each month) of data and fill in the plandescription
They are located here: C:\Users\John\Company2) I have an excel file PlanDescLookup here: C:\Users\John\PlanDescLookUp
I want to use vba to fill in the PlanDescription in each of the groups located C:\Users\John\Company

Here is what I have so far:
Sub DEhic_Step1()

' Workbooks.Open ("C:\Users\John\PlanDescLookUp\.xlsx") ' Opens the PlanDescriptions look up table

Dim fPath As String
Dim i As Long
Dim Answer As String
Dim oFSO As Object, oFolder As Object, oFile As Object

Answer = "CurrentMonth"
fPath = "C:\Users\John\Company\"
Application.ScreenUpdating = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(fPath)

For Each oFile In oFolder.Files
With Workbooks.Open(oFile.Path) ' opens the 1st file in the folder that I want to update with the plan description
Sheets(Answer).Activate

With .Sheets("CurrentMonth").Activate 'CurrentMonth worksheet is now active


'This is where I think the v lookup code goes. I need to make sure that the formula gets populated in for each row that has data.
' I also want to copy that row and paste as values to remove the formula.



End With
.Save
.Close
i = i + 1
End With
Next

'turn screen back on
Application.ScreenUpdating = True
'Give feedback
MsgBox "All done." & vbNewLine & "Number of files changed: " & i, vbOKOnly, "Run complete"

End Sub

 

[SkipVought]

Hi,

The attached workbook contains NOTHING???

Did you intend to include some relevant data in this workbook?

Does the Lookup Table in Answers sheet in ALL workbooks begin in the same cell? And if so, What cell?

This is how and where the VLOOKUP would be used

'
    Dim rng As Range, xl As Application
    Set xl = Application
    For Each oFile In oFolder.Files
        With Workbooks.Open(oFile.Path) 
            
            Set rng = .Sheets("CurrentMonth").UsedRange.CurrentRegion.CurrentRegion
            
'            x = xl.VLookup(LookupValue, rng, ColNUM, False)
            
            [highlight #FCE94F].Save      '[b]Why are you saving this file?[/b][/highlight]
            .Close
            i = i + 1
        End With
    Next
    Set xl = Nothing
    Set rng = Nothing

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Ok I uploaded the two files. Hopefully they were uploaded. One is called Group and the other is called Plan Description. So I would have 15 or so files like the Group file, just named differently.
The reason I put "Save" is because once the file is updated with the Plan Description I want to save that file.

 

[SkipVought]

You have only ONE workbook named PlanDescription.xlsx, with one sheet named Sheet1 with a single table:
[pre]
Plan Description
First My First Plan
Second My Second Plan
Third Starting to get old
Fourth Very fast
Fifth Ok, I'm done

[/pre]

Where is your VBA code? BTW, the workbook containing your VBA code should be named .xlsm. An .xlsx cannot store VBA.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

I guess I can only attach one file to a post. I don't know. Anyway the code is in my original post. I have an excel file in another location which I run the code and i just click the button so it runs the code. I did not want to put the code in with the files with all the companies (or in my world a company is called group) nor did I want to put the code in with the plan descriptions.
Thanks Skip.

 

[SkipVought]

j, there is no sheet named CurrentMonth???

    Answer = "CurrentMonth"
'......
    For Each oFile In oFolder.Files
        With Workbooks.Open(oFile.Path) 
            Sheets([b]Answer[/b]).Activate

Can we get on board with the workbooks and data that you are ACTUALLY using, PLEASE?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

OMG someone should slap me, now. Well, I can't really send actual data but the worksheet with the data in the group file should be named currentmonth. Sorry

 

[SkipVought]

is there a reason for 15 Group workbooks?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Yes, each workbook contains current month data related to that company and eventually gets sent to that company, thus 15 workbooks one for each company or group.

 

[SkipVought]

It would sure make your life easier if ALL your company data were in ONE TABLE and you would generate a workbook for the current month for each group. As it stands, you have non-normalized data and apparently no consolidated source.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Understood, but the data from each company unfortunately is obtained one company at a time I can't get my hands on it any other way. I take what I am given and try to implement my solution around that. I don't mind writing code to get around lifes imperfections and this is what I came up against and I am trying to work with what I got and the solution I thought of should work, I just needed help in writing some of the code, which by the way I am thankful for your contribution.

 

[jhabey01]

All I want the code to do is open up one of the companies workbooks go to the plan description column do a v look up on plan and return the plan description to column I (PlanDescription) for all the rows that have data copy and paste column I as values so as to not have a formula in that cell save the workbook and do the same for the next file and all the files in that forlder.

Thats it nothing more.
I don't really care about why its this way, just working with what I got and I ain't about to fight city hall.

I think I have good start with the code I wrote i just need to finish it off with the some coding that will look that plan value up in the other file.
Thanks

 

[SkipVought]

I'd want to make it a better solution for ME and my company. I did stuff like this for 20+ years. Processing a bunch of remote files is much more complex than processing a single table. OL.

Option Explicit

Sub DEhic_Step1()
    
    Dim fPath As String
    Dim i As Long
    Dim oFSO As Object, oFolder As Object, oFile As Object
    
    Dim r As Range, xl As Application, ws As Worksheet
    
    Set xl = Application

     Set ws = Workbooks.Open("C:\Users\John\PlanDescLookUp.xlsx").Sheets(1) ' Opens the PlanDescriptions look up table
    
    fPath = "C:\Users\John\Company\"

    Application.ScreenUpdating = False

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(fPath)

    For Each oFile In oFolder.Files
        With Workbooks.Open(oFile.Path) ' opens the 1st file in the folder that I want to update with the plan description
            With .Sheets("CurrentMonth") 
                xl.DisplayAlerts = False
                .UsedRange.CreateNames True, False, False, False
                xl.DisplayAlerts = True
                
                For Each r In .Range("Plan")
                    .Cells(r.Row, .Range("PlanDescription").Column).Value = xl.Index(ws.[Description], xl.Match(r.Value, ws.[Plan], 0), 1)
                Next
                
            End With
            xl.DisplayAlerts = False
            .Save
            xl.DisplayAlerts = True
            
            .Close
            i = i + 1
        End With
    Next
    
    Set xl = Nothing
    Set ws = Nothing
    
    'turn screen back on
    Application.ScreenUpdating = True
    'Give feedback
    MsgBox "All done." & vbNewLine & "Number of files changed: " & i, vbOKOnly, "Run complete"

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

I am getting an:
Application Defined or object Defined error at the following line:
.Cells(r.Row, .Range("PlanDescription").Column).Value = xl.Index(ws.[Description], xl.Match(r.Value, ws.[Plan], 0), 1)

trying to diagnose this morning.

 

[SkipVought]

Oh, yes...

I make extensive use of Named Ranges (and Structured Table features, although not here), so I named the ranges in your lookup table based on the heading names via Formulas > Defined Names > Create from Selection... Using the names in TOP Row. Be sure to SELECT the entire table before executing this process.

Notice that I use .CreateNames in the Group workbook, to create references for the Plan and PlanDescription.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Not sure what you mean by: Be sure to SELECT the entire table before executing this process.

 

[SkipVought]

In order to name the ranges in your lookup table, you must first SELECT the entire table and then use the process I outlined. This is a ONE TIME TASK.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jhabey01]

Thanks it works! I appreciate your help.

 

[Andrzejek]

It works" – great.
Please click on “Great Post!” link in appropriate post to award a star for help received.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[jhabey01]

When I run the above code provided by Skip, it is returning a #NAME?
any thoughts as how to resolve. ( The code should look up a value and return its description of that value.)