Opening a workbook from different location + Vlookup

the underscore is something added just before I uploaded the files. Sorry for confusion if it causes one.

Maybe:

Please Login or Register  to view this content.

Thanks for the response John. It is still giving same error. Run Time Error 424 : Object Required. It isn't pointing to any line in the code. Is there any way to find out which line is stopping from going ahead?


I did close the "With" statement before I ran the code

i tried doing F8 and goes upto the line
"set wb2=Activeworkbook"

My mistake try:

Please Login or Register  to view this content.

Actually I had fixed that too.

It was still the same error .Were u able to try it on any of the attachment excel files?

Works for me on the attached files?

Please Login or Register  to view this content.

I missed an End With though.

Originally Posted by JOHN H. DAVIS

Works for me on the attached files?

.

error.JPG

attached is the error message I get

does having in a shared network drive create problems?

Originally Posted by JOHN H. DAVIS

Works for me on the attached files?

I missed an End With though.

Hi John,

I almost ... almost got it to running.. 1 problem....

Please Login or Register  to view this content.

Problem is (see screenshot below)

• the first value and the last value gets the correct Lookedup answers... the others get messed up

error2.JPG

Originally Posted by bakerman2

Try this.

Worked well. Appreciate your time and effort.

added a reputation star.

Thanks again

Originally Posted by bakerman2

Try this.


With book1.Sheets("Sheet1")
sn = .Range("B6", .Range("B" & .Rows.Count).End(xlUp)).Resize(, 2)
End With

this part helped a lot... i am assuming it includes every cell with a value in column B into its range . I was eventually getting to this only !

Thanks again

subbby

This doesn't open A_.xlsm.

Please Login or Register  to view this content.

Welcome..

just one question...

What does this line do

Please Login or Register  to view this content.

Its matching what it found on Column B of book1 and comparing it with .Columns (2) . Little confused there.

can you explain, if its not too much to ask

In the program I am trying to use this , it looks from C29 instead of B6 . So I am working on modifying it slightly

Thanks

See my post in #15

Originally Posted by jindon

subbby

This doesn't open A_.xlsm.

Please Login or Register  to view this content.

this too worked like a charm.. thanks Jindon...

...

However, the actual place I am going to use this code will be comparing values in C29 onwards and not B6. I will attempt to change this code and see if that works

Thanks again

Originally Posted by jindon

See my post in #15

this worked like a charm

I sued the following code

Please Login or Register  to view this content.

U can see I have made some changes to the folder location ... and the file name and sheet name...

Now it does ask for location to look for the comparing excel sheet. can we automate this ?

OK, I was to post the same...

While populating, it seems to clean the cell value. Is there away I can stop it ?

I used the following code for checking the value in two different excel files.

While i select the second one, it delestes all the values

Please Login or Register  to view this content.

OK how do you want it?

If cell matched to Drawing List_A.xlsm, skip that row for Drawing_Table_B?

See this screenshot. This is how I may have the numbers.

If its number with "A" in it, it looks in Drawing List_A.xlsm and if it has "B" in the number, it must look in Drawing List_B.xlsm (a different excel sheet)

When I tried the above code it did copy fill the details of numbers with "A" in them and when I showed where Drawing List_B.xlsm was, it deleted what it filled previously


These new excel files are anew development as I had to make modifications somewhere else in a program that I have been using.



Drawing_Table_A is a sheet in Drawing List_A.xlsm
Drawing_Table_B is a sheet in Drawing List_B.xlsm

database.JPG

Originally Posted by jindon

OK how do you want it?

If cell matched to Drawing List_A.xlsm, skip that row for Drawing_Table_B?

and in addition to Post # 22

I used this following code that works like a charm...... But

problem arises when I move the values to C29 onwards instead of B6

Please Login or Register  to view this content.

Do you mean like this?

Please Login or Register  to view this content.

Originally Posted by jindon

Do you mean like this?

hello jindon.
close enough but its asking to locate the source file for every row ..... is there a way to automate it ?

OOps
Can you just delete the line of

Please Login or Register  to view this content.

I just put that line for the testing purpose...

i did remove it ... and it still is giving the same issue

Originally Posted by jindon

OOps
Can you just delete the line of

Please Login or Register  to view this content.

I just put that line for the testing purpose...

worked worked worked.....

my folder location was

"Z:\Support\Engineering\Drawing Packages\Drawing Lists\"

I changed that in ur code...

Then workbook name or sheet name might be wrong...

It is working here.

P.S
OK, I just saw your last post.

I must appreciate everyone's patience here. John, Bakkerman and Jindon..

thanks a lot all.

Thanks to Jindon and Bakkerman for helping.

Appreciate your patience Jindon. Its admirable.

You are welcome and thanks for the rep.

can i use this code on another excel sheet

identical folder location, sheet name and filename ! no changes whatsoever... would it work ?

Yes, of course.
Check all the cell reference in the formula and if they are all correct, it means no matched data found...

I tried it Jindon.

its identical in the spreadsheet and it says Applications or Object not defined...

Originally Posted by subbby

I tried it Jindon.

its identical in the spreadsheet and it says Applications or Object not defined...

It sometimes happens when the formula is not correct.

Like the files I posted, it should work as it is, if the conditions are all the same.

Ok thank you for your response.

its still doesn't return any value

See the screenshot below. It does get to the formula part , but doesn't copy the values .

error4.JPG

I think it is a mapped network drive...
Can you just try with unc? it should begin with \\

yes my Z drive is a network drive....

Should I change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And how did it go?

i did that and it kept asking for folder location for each row...

it worked as if

Please Login or Register  to view this content.

was uncommented

Also Jindon,

the original file worked well with

Please Login or Register  to view this content.

it worked like a piece of cake .. see the screenshot below where the results came up
error5.JPG

Yes, generally it should work...

Does the workbook in question have same name as master workbook?

No Jindon, they are different

See this screenshot below

error6.JPG

however, their sheet names are different

see screenshot below
error7.JPG

If I remember () is the problem...

Can you just try

Please Login or Register  to view this content.

Originally Posted by jindon

If I remember () is the problem...

Can you just try

Hello Jindon,

where do I add that ? Do i just paste it ?

I did make it spit the formula by using the MsgBox... is that correct ?

Drawing List_B is the filename and Drawing_Table_B is the worksheet name.... is that big box-bracket correct for filename ?



error8.JPG

??
You have

Please Login or Register  to view this content.

But isn't it

Please Login or Register  to view this content.

?

See Post # 25

. I do have the code to read two more sheets and hence I added the ones in bold below

Its same as how u instructed in Post # 25

MasterFile_A = "Drawing List_A.xlsm"
MasterFile_B = "Drawing List_B.xlsm"
MasterFile_D = "Drawing List_D.xlsm"
MasterFile_E = "Drawing List_E.xlsm"

wsNameA = "Drawing_Table_A"
wsNameB = "Drawing_Table_B"
wsNameD = "Drawing_Table_D"
wsNameE = "Drawing_Table_E"



"Sheet2 (4)" is activeworkbook's active sheet name... similar to "Sheet1" in your zip files

OK, when the dialog box appears and you select the file, can you look at the formula in formula bar?

Originally Posted by jindon

OK, when the dialog box appears and you select the file, can you look at the formula in formula bar?

How do I check the formula ?

this is what i got


error9.JPG

If you just comment out

Please Login or Register  to view this content.

the formula should remain in the cell.

Originally Posted by jindon

If you just comment out

Please Login or Register  to view this content.

the formula should remain in the cell.

That didn't work either..


I have attached the file herewith if that helps

Formula in D29

=IFERROR(VLOOKUP($C$29,'C:\Users\Owner\Desktop\Drawing Lists\[Drawing List_B.xlsm]Drawing_Table_B'!B:C,2,FALSE),"")

Is this correct?

Originally Posted by jindon

Formula in D29

=IFERROR(VLOOKUP($C$29,'C:\Users\Owner\Desktop\Drawing Lists\[Drawing List_B.xlsm]Drawing_Table_B'!B:C,2,FALSE),"")

Is this correct?

Highlighted in red.. what does it stand for ?

I am assuming it looks in Column B, returns the value from column C ?
Not sure what does the number "2" and False do there

I need you to run the actual file with actual path(newrowk) that you are now using and want to see the actual formula in the cells, not mine.
So that we can see the correct formula, if it is returning correct value.

see VLookUp function
https://support.office.com/en-us/art...8-93a18ad188a1

how do i see the formula ?

The only way I can do is , by using Msgbox as I did in Post # 48

So I wrote

Please Login or Register  to view this content.

and got the one as shown in screenshot below

and going by the explanation given on link https://support.office.com/en-us/art...8-93a18ad188a1 just above examples and comapring with the screenshot below

• YES, the path is correct, the Workbook it is looking at = is correct,
• The worksheet it is loot in the workbook = is correct
• the column array is correct (B:B) ;
• the column index is correct (the return value is in column C and its 2nd from Column B)

error11.JPG

And I noticed one problem... I ran a F8 step by step.. Even thought Value at C29 is 114A5 (which is Like "*A*") it went to next look.... If you see the above screenshot , it is comparing C30 value for the second If statement where it looked for "*B*" .. actually value in C30 is 117A47

See another screenshot,

its looking for C29 value inc condition for "*B*" which it is not supposed ... C29 value is 114A45..it should be seeing in Drawing List_A.xlsm filename and on Drawing_table_A sheeterror12.JPG

I even tried this

Please Login or Register  to view this content.

to force it to recognize the "A"... the code just doesn't go into that loop...

Do you think an Object has to be set for the range or something like that.. asking layman questions as I am not well versed with Macros

Pls advise

FOUND THE ERROR

I must confess I am mad. I must apologize for wasting your time, Jindon. I am sincerely sorry.

I had pasted the code in the wrong SHEET.. pasted in Sheet1 instead of Sheet1 (4) . See screenshot below


Learned from my mistake

Again.. thanks for your patience...the code worked well with no issues

Appreciate it






erro12.JPG

OK, glad that soled the issue.

jindon,

the values this code will see are only numbers...

example 4891, 103221, 78789

The above code has to look at "Drawing List_SK.xlsm" 's "Drawing_Table_SK" worksheet when it sees a cell value with only NUMBERS... not alphanumeric

Originally Posted by jindon

subbby

Try

Please Login or Register  to view this content.

Jindon,

thanks for your code... This is the same code we were working on Drawing List _A and B....



The following is the code I tried

Please Login or Register  to view this content.

OK, can you post the code that you are running?

Originally Posted by jindon

OK, can you post the code that you are running?

this is the entire code..marked in RED is what and where I am stuck .

Please Login or Register  to view this content.

marked in BLUE is something that teh code doesnt recognize when it sees vales like P36E14, P5E14, P9E4..etc

I am even trying "ISNUMBER method

Please Login or Register  to view this content.

This is tricky.. any idea why ?

see screenshot below



error13.JPG

Try
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

Originally Posted by jindon

Try
1)

Please Login or Register  to view this content.

2)

Please Login or Register  to view this content.

Doesn't work on SK and PE.. doesn't do anything

Works only on A, B, D and E

Is the data copied from web or any other software?

no its from the Z drive only ... its an excel sheet on FilePath = "Z:\Support\Engineering\Drawing Packages\Drawing Lists\"

marked in BLUE is something that teh code doesnt recognize when it sees vales like P36E14, P5E14, P9E4..etc

It should
This should return True.

Please Login or Register  to view this content.

Originally Posted by jindon

Is the data copied from web or any other software?

I re tried.. it works for SK now... but now P E

let me show u a screenshot of what values of I see on my column C


error14.JPG

Error15.JPG


This all checks out to be fine with


error16.JPG

If you look at the file name, Isn't it

Please Login or Register  to view this content.

?

Originally Posted by jindon

If you look at the file name, Isn't it

Please Login or Register  to view this content.

?

Sorry.. I should have noticed that... I did fix it and still it didnt recognize

Please Login or Register  to view this content.

I even tried this

Please Login or Register  to view this content.

Still doesn't do anything

And I ran the above as a separate code and it worked fine... it just isn't working when its in the big code

Then I need to see your workbook, not a picture.

Jindon,

thanks for your patience. I have solved this one. since it worked as a separate code. I called this in the main code.

Separate code is

Please Login or Register  to view this content.

I called it in the main code by using the following

Please Login or Register  to view this content.

and all works fine. Workbook has been saved and I ran trial runs and all works fine...


Thanks Jindon... appreciate it

So you got it to work finally, that's a good news.

Originally Posted by jindon

So you got it to work finally, that's a good news.

Yes Sir. Thanks to you and your patience.

Last thing that I should tell you is that when square brackets, [ ] or apostrophe, ' used in file path or book name, it should be escaped by a double quote.

So better not use them to avoid too much hustle... just for your information.

Originally Posted by jindon

Last thing that I should tell you is that when square brackets, [ ] or apostrophe, ' used in file path or book name, it should be escaped by a double quote.

So better not use them to avoid too much hustle... just for your information.

Yes, I agree. I will surely keep that in mind and your code in this thread is like a dictionary for me. I am going to be referring this for a lot many examples . Learnt a big deal ... calling a file in a different folder using those brackets and apostrophe's ...

Thanks again Jindon