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]

It's been so long, but according to the code I posted, there is a PlanDescription and a Description; two DIFFERENT ranges that were named. And I can't remember where the Description range was, but its a LOOKUP, where a PLAN lookup value is used to return a DESCRIPTION from the DESCRIPTION range. This would have been a SEPARATE LOOKUP TABLE.

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

 

[SkipVought]

Did some serching

PlanDescription workbook should have a table like this lookup table with Named Ranges...
[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]

At least, it should be in your workbook!


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

 

[jhabey01]

Attached is the look up table I would be using. I seem to be missing the named ranges part. I have two name ranges . Are they named correctly?

http://www.tek-tips.com/

 

[SkipVought]

1) SELECT the entire table.

2) Formulas > Defined Names

3) Create from Selection

4) Create names from values in TOP row

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

 

[jhabey01]

Ok I did that. I just wanted to provide the file I was using to see if what I have is correct. Now instead of a name error I am getting a #N/A error

 

[SkipVought]

Well, did YOU enter PLAN text into your lookup file to replace my goofy test text, along with the appropriate DESCRIPTIONS for each PLAN???

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

 

[jhabey01]

Now that would downright silly of me not to do that. Yes I did. I just sent mocked up data. So the Named Ranges that I uploaded would be correct? It has got to be close to working guess I just need to keep toying with it.

 

[SkipVought]

Well if you're looking for a plan that's NOT in the lookup table, you get NA returned.

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

 

[jhabey01]

Thanks for reply back I stepped away from this for a couple of days and started looking at it again this afternoon and within a half hour I had working.

 

[jhabey01]

I have another little twist on the above code from Oct 20. I want to concatenate to columns to make that the look up value. The two colums are column A which is labelled ClientName and Column H which is labelled Plan. Instead of the look up value being Col H .
Client Name Plan
Greenway Tracan LookUp value would be GreenwayTracan

The lookup table is all reconfigured and will be looking up GreenwayTracan to update the description

Thanks,
John

 

[SkipVought]

Ampersand (&) is the concatenation operator.

So give it a whirl and if you still need help, post your modified code and we'll take a look.

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