PDA

View Full Version : Vlookup if I don't know the name of the workbook



Djblois
04-03-2007, 10:33 AM
I am using code to do a v-lookup but I can't figure out how to get it to work if I don't know the name of my Table Array. This is what I tested:

Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],'Active'C1:C4,4,FALSE)"

FinalRow is set as the finalrow - I use it in other places so I know that isn't the problem

Active is the table Array

Set Active = ActiveSheet

Bob Phillips
04-03-2007, 10:45 AM
If yhou don't know the array name I don't see how you could possibly use it.

mdmackillop
04-03-2007, 10:46 AM
If Active is the activesheet, I don't see that it is necessary. Omitting it though creates the formula =VLOOKUP(A2,$A:$D,4,FALSE) in column D, which is looking at itself and writing data to itself; Circles within circles.

Djblois
04-03-2007, 10:48 AM
No I set it to the activesheet after I open it then I switch to another sheet right after.

mdmackillop
04-03-2007, 10:52 AM
I'm confused.

Djblois
04-03-2007, 11:33 AM
sorry. let me try to explain:

1) I open the Open dialog with vba
2) then I set the activesheet of the workbook that the user opened to Active
3) Then I switch back to the original sheet my add-in was working on
4) THen I do a v-look up on the original sheet with active being the Table Array

Here is the relevant code:

set Detail = Activesheet
Application.Dialogs(xlDialogOpen).Show
Set WB(1) = ActiveWorkbook
Set Active = ActiveSheet
Detail.Activate
finalRow = Cells(Rows.Count, "A").End(xlUp).Offset(-1, 0).Row
Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],'Active'C1:C4,4,FALSE)"

Djblois
04-03-2007, 02:05 PM
THis one I am having a lot of trouble with

mdmackillop
04-03-2007, 02:27 PM
My solution to these complicated formulae is to make use of the macro recorder, which does the hard work. You then just need to swap out the bits you don't want for your variables.

'From recorded macro
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],[Workers.xls]Sheet1!C1:C4,4,FALSE)"
'after update
Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],[" & WB1.Name & "]" & _
Active.Name & "!C1:C4,4,FALSE)"

Djblois
04-05-2007, 07:48 AM
Thank you but it isn't working for me :(

Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],[" & WB(1).Name & "]" & _
Detail.Name & "!C1:C4,4,FALSE)"

it is giving me an application defined or object defined erro

mdmackillop
04-05-2007, 08:46 AM
Have you tried outputting the name values to the Immediate window (Debug.Print) to check you have a sensible result?

Djblois
04-05-2007, 08:54 AM
Where do I put the debut.print? I tried in the code befor I get the error message and I tried typing it into the immediate window

mdmackillop
04-05-2007, 10:13 AM
Insert it before you use the variables in your code. If you don't see your workbook and sheet names, the following code will not run.
WB(1) seems to me an unusual name for a Workbook variable. Are you sure you have it right?


Debug.Print WB(1).Name
Debug.Print Detail.Name

Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],[" & WB(1).Name & "]" & _
Detail.Name & "!C1:C4,4,FALSE)"

Djblois
04-05-2007, 01:57 PM
Here:

Public WB(2) As workbook

I will run that test tomorrow

Bob Phillips
04-05-2007, 03:41 PM
That declaration is meaningless. You can declare an variant which you use as an afrray later, you cannot declare an array element.

Djblois
04-06-2007, 06:34 AM
Xld,

I am sorry but I don't understand what you are saying? please explain.

Djblois
04-06-2007, 07:55 AM
I just tested it and they came out as the correct Workbook and Sheet name

mdmackillop
04-06-2007, 09:05 AM
Look for discprepancies between

Debug.Print "=VLOOKUP(RC[-3],[" & WB(1).Name & "]" & _
Detail.Name & "!C1:C4,4,FALSE)"

and a lookup formula entered normally to do the same thing. I don't know what else to suggest.

Djblois
04-06-2007, 11:33 AM
I will provide sample data on monday

That smart indent looks nice but I can't install at work.

The size of the task will change from 10 rows up to 50,000 (potentially)

Djblois
04-09-2007, 12:29 PM
Here is some test books

Djblois
04-09-2007, 12:31 PM
Here is the second book:

First open the Dept15 2005 - Cleaned
Then run the code StartSummary to open the other file

mdmackillop
04-09-2007, 02:01 PM
My suggestion in Post 17 would have shown up the disrepancy, namely missing apostrophes.
Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],'[" & WB(1).Name & "]" & _
Detail.Name & "'!C1:C4,4,FALSE)"

In stepping through your code, there is a lot of redundancy. You format cells, then delete them.
Your use of WB(1) etc. as a variable name is potentially very confusing, as it looks like an array index. What's wrong with WB1.
You set sheet variables, but don't use them. You rely on the default of the activesheet rather than use e.g. WS1.Cells, WS1.Range etc. which is clear and specific.

Sub BeginSummary()

'Adds Date and profile to Print Heading
Dim cellObject As Range
Set WB(1) = ActiveWorkbook
Set Detail = WB(1).ActiveSheet

'This gets deleted
For Each cellObject In Range("B1:B7")
cellObject.Formula = WorksheetFunction.Proper(cellObject.Formula)
Next

'Delete Grand Total
On Error Resume Next
Cells.Find(What:="Grand Total").EntireRow.Delete

'Clean up Report
Range("S:Z").Delete Shift:=xlToLeft
On Error Resume Next
Application.DisplayAlerts = False
Rows("1:8").Delete Shift:=xlUp
Columns("A:A").Value = Columns("A:A").Value
'What is selected?
Selection.SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete

'What is this meant to do?
Range("A1").Select
Selection.End(xlDown).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete

finalRow = Cells(Rows.Count, "A").End(xlUp).Row


Range("B:H, L:L, P:P, R:R").Delete Shift:=xlToLeft
Application.DisplayAlerts = True

End Sub

Djblois
04-09-2007, 02:07 PM
Mdmack,

That isn't all the code I chopped it up for just the pieces that are needed for this problem. Some of the things you mentioned I do for particular reasons based on the program. I will look at your suggestions

geekgirlau
04-09-2007, 05:23 PM
Quick suggestion: if you are using FormulaR1C1, you need to make sure all your references are in this format, so C1:C4 is incorrect. Also, it looks like you're trying to capture a value from the 4th column, when the array only extends from C1 to C4.


Range("D2:D" & finalRow).FormulaR1C1 = "=VLOOKUP(RC[-3],[" & WB(1).Name & "]" & _
Detail.Name & "!C1:C4,4,FALSE)"

mdmackillop
04-09-2007, 05:27 PM
Hi GG
C1:C4 is correct here; it means Columns A: D

geekgirlau
04-09-2007, 06:02 PM
:doh: okay, more caffeine over here, pronto!

Djblois
04-10-2007, 08:58 AM
That works perfectly, but now I am trying to do one last thing with this. I want to compare the two unformated sheet (Detail) to the Formated Sheet (Active). If the item is in Active and Detail then I want to put the word delete in the "F" column so I can delete it after with a filter. When I type the formula in manually, it works but with the code it won't work. Here is the code:

Range("F2:F" & finalRow).FormulaR1C1 = "=IF(VLOOKUP(RC[-5],'[" & WB(2).Name & "]" & _
Active.Name & "'!C1:C2,2,FALSE),""Delete"")"

It keeps saying "Object variable or With Block Variable not set" I used the same one that worked before, all I did was change the second vlookup into "Delete" and I removed the isNA:

Range("J3:J" & finalRow).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-9],'[" & WB(1).Name & "]" & _
Detail.Name & "'!C1:C5,5,FALSE)),""0"",VLOOKUP(RC[-9],'[" & WB(1).Name & "]" & _
Detail.Name & "'!C1:C5,5,FALSE))"

mdmackillop
04-10-2007, 10:11 AM
Your code is writing this code into Sheet1, column F of Cleaned.xls (I've entered it after the other VLookup code)
=IF(VLOOKUP(A3,Sheet1!$A:$B,2,FALSE),"Delete")
which looks doomed to fail. What does your manually entered code look like.
Try removing the = sign from the start of your formula and write it to one cell. ie
Range("F2:F2").FormulaR1C1 = "IF(VLOOKUP(RC[-5],'[" & WB(2).Name & "]" & _
Active.Name & "'!C1:C2,2,FALSE),""Delete"")"
You'll see the text form of it. Does it make sense?

Djblois
04-10-2007, 11:38 AM
mdmackillop,

I switch tabs first to the other spreadsheet before I run the Vlookup. I added more code to it.

mdmackillop
04-10-2007, 11:55 AM
Why not add the sheet reference to your code. There shouldn't be any need to switch just to add the formulae.

Djblois
04-10-2007, 12:16 PM
ok but then I need to copy what ever I don't delete? is this possible with the sheet reference? I don't think that would fix the error that I am having though

mdmackillop
04-10-2007, 12:48 PM
So here's your function if written in the other sheet. As you can see, it's an incomplete If function
=IF(VLOOKUP(A3,'[Dept15 2005 - Cleaned.xls]Sheet1'!$A:$B,2,FALSE),"Delete")

Djblois
04-11-2007, 08:43 AM
What do you mean it is incomplete? I first used:

Range("D3:D" & finalRow).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-3],'[" & WB(2).Name & "]" & _
Active.Name & "'!C1:C3,3,FALSE)),"""","Delete""

and that didn't work either. but I also thought the ISNA was not needed.

Djblois
04-11-2007, 01:21 PM
I already tried typing it in as a formula while the macro recorder is on and copied it over and I only changed the relevant parts that I needed to. However, it still doesn't work. :(

mdmackillop
04-11-2007, 02:16 PM
You need to get a True/False in the first part of the If (ISNA provided this before) followed by your two results
=IF(VLOOKUP(A3,'[Dept15 2005 -Cleaned.xls]Sheet1'!$A:$B,2,FALSE)= Something,"Delete","Something else")

Djblois
04-12-2007, 06:37 AM
I tried with the ISNA in it the first time and it still didn't work. I added it back and it still is giving me the same error. I will add it to the test workbook and repost.

Range("F2:F" & finalRow).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC[-5],'[" & WB(2).Name & "]" & _
Active.Name & "'!C1:C2,2,FALSE)),"""",""Delete"")"

Djblois
04-12-2007, 06:42 AM
Open this one first and run the code to open the other book

The second book is the same as the one in post#20

mdmackillop
04-12-2007, 02:11 PM
You have duplicate procedures. Can you sort this out and repost?
All I can do is repeat the steps I have previously stated to trace the errors. There is nothing new to be coded. How thoroughly have you checked the written formulae?

Djblois
04-13-2007, 06:53 AM
The formula works if I type it in. Of coursw without the dynamic references. I will check the duplicate procedures.

Djblois
04-13-2007, 06:56 AM
Here sorry about that error. My mistake. Here it is fixed:

Djblois
04-16-2007, 05:58 AM
Bump

mdmackillop
04-16-2007, 12:42 PM
That version appears to be working with the Dep15 2006 file posted earlier.