Populate History Sheet from Custom function Variables (vlookup)

[exc4libur]

This macro is the solved one "Credits to Nimrod and JB"
Short description: It vlookups from your data wb and pastes the results value on the lastcol without you having to input formulas in the worksheet!
Neat! n1.

Haleluijah...

Please Login or Register  to view this content.

[nimrod]

Could you please explain what you mean by "5 formula" and what each of these formula are suppposed to do ??

[exc4libur]

...........

[nimrod]

OK.... good
So I need a little more detail .. could you give me a concrete example

List the name of the 5 sheets AND the formula you like to see in each .. OK ?

By-The-Way ... Are the sheet names constant ? Or will they vary .. on how does the formula vary ?

[nimrod]

In your code you do some very expensive ,"time wise" , activities. Placing formula's in cells, haveing all formulas continuing to re-calc with each change in sheet , then a copy and a paste .. are all very time expensive.
In the following solution I just use the Vlookup formula within the confines of the code BUT never place it in the cell. The only thing placed in cell is the resulting value. Hence no formula re-calcs and no copy and pastes ... in other words I've cut out the middle men

ALSO: This solution allows us to pass variables to the Vlookup formula ... in my example I vary the "Return Column" based on the Source sheet i.e. If the Current Source sheet is named "Week1" then the Column to return in the C2:C6 range would be C3 etc etc.

Without concrete examples from you ... this is about as far as I can go ...

Please Login or Register  to view this content.

[exc4libur]

Sorry Nim I didn`t answer you asap! My mom was almost shooting me to eat dinner at the table hahaha..

So, I attached 2 example workbooks!
=> Book1 is the table which I extract data
=> Book2 is where I will store the vlookup data

No sheets have names..
I have inserted the vlookup formulas in every sheet in Book2 with the vlookups for each.

[nimrod]

From a quick look at your attached books I'd say my last code posting is close to what you want ... you just need to change the SheetNames and the Columns to return in the case statement

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[exc4libur]

Hell YEAH!!!!!!!!!!! Woooohooo ive kept all day on this!! hahaha thanks NIM THANK YOU!!

[nimrod]

Cool ... I'm glad it all worked out for you .... are my changes in the code making sense to you ??

[exc4libur]

Cool ... I'm glad it all worked out for you .... are my changes in the code making sense to you ??

One more detail please!! how to change the sheetnames from the extraction book? like we are only using " [book1.xls]sheet1! ", what if i need it to get from "sheet2!" ?

[nimrod]

Do you mean the Vlookup Source sheet "EmpLns_I" ??

[exc4libur]

Do you mean the Vlookup Source sheet "EmpLns_I" ??

Oh yea, sorry I was using the example! But yes that it, what if I had to use "EmpLns_II"

[nimrod]

That depends ... is this sheet always going to be varied / or not ? And what is the name dependent on ?

[exc4libur]

Please Login or Register  to view this content.

Actually I had something more simple in mind, now that we can filldown the vlookup results referencing by sheetname, could we instead of basing ourself by column why not base it by formula? For instance, someone might want to use index mid small instead of vlookup and place results, or average etc.. different formulas according to the sheet and then just placing the result 'like you said - cutting the middleman hahaha'

So the code would be like:

Please Login or Register  to view this content.

[nimrod]

Can you see the pattern of my changes ?
1) Add var to case statement
2) Add var to function call
3) Change section of Vloop from Constant to Var ... with the Var surrounded by """ & Var & """

Please Login or Register  to view this content.

[exc4libur]

I thought of that yes! But what about this idea below??
Sorry to take your time nim

Please Login or Register  to view this content.

Actually I had something more simple in mind, now that we can filldown the vlookup results referencing by sheetname, could we instead of basing ourself by column why not base it by formula? For instance, someone might want to use index mid small instead of vlookup and place results, or average etc.. different formulas according to the sheet and then just placing the result 'like you said - cutting the middleman hahaha'

So the code would be like:

Please Login or Register  to view this content.

[nimrod]

If you want my opinion I would say that this method goes against best practices. You should code so that changes and updates only occur in one place and one line. With you sol'n a change in the Vlookup formula means having to change 5 seperate lines of a hard to read formula. With a change in mine the formula probably doens't need to be changed AND if it does you only have to change it in once. Errrors in coding increase and time for maintenance increase dramatically when you have duplication of code. i.e. the Vlookup formula 5 times instead of once. In my workplace , as in most others, the coding would be returned for refactoring. Sorry

ALSO: Having the "formula" in it's own function allows for very granular error trapping . In fact if this was Java Code the entire thing would be in 1/2 dozen methods and classes.

ALOS: By keeping the formula function seperate means that you're generating re-usable code ... do you think I just wrote that function ? I've been using it for years ... just Tweaking it ... but I wouldn't be able to do that if it's imbedded in the main procedure.

[exc4libur]

If you want my opinion I would say that this method goes against best practices. You should code so that changes and updates only occur in one place and one line. With you sol'n a change in the Vlookup formula means having to change 5 seperate lines of a hard to read formula. With a change in mine the formula probably doens't need to be changed AND if it does you only have to change it in once. Errrors in coding increase and time for maintenance increase dramatically when you have duplication of code. i.e. the Vlookup formula 5 times instead of once. In my workplace , as in most others, the coding would be returned for refactoring. Sorry

ALSO: Having the "formula" in it's own function allows for very granular error trapping .

Uhnnnnnnnnnnnnnnnn I see ok, doesnt hurt gathering all data worksheets to one workbook and changing the vsheet according to the lookup hahaha! Well Nim thank you very much man, ive been all day trying to crack this! Thanks a million man!!

Best Rgds!!!!!

[nimrod]

Cool dude ... it's been fun ... cheers.

ALOS: By keeping the formula function seperate means that you're generating re-usable code ... do you think I just wrote that function ? I've been using it for years ... just Tweaking it ... but I wouldn't be able to do that if it's imbedded in the main procedure.

[exc4libur]

Cool dude ... it's been fun ... cheers.

ALOS: By keeping the formula function seperate means that you're generating re-usable code ... do you think I just wrote that function ? I've been using it for years ... just Tweaking it ... but I wouldn't be able to do that if it's imbedded in the main procedure.

Im starting to use functions now! hahahaha God its a time saver and veeeeery useful!

Cheers!

[nimrod]

Not to mention you're starting to create a library of reusable functions. If they're written well you'll be surprised how little code you need to write .. for example here's my function for retrieving values from closed books ... do you see how each section of the formula is an input to the function... I don't have to read my code ... I just have to provide the required inputs

Please Login or Register  to view this content.

[nimrod]

Another thing to note in this is the use of ExecuteExcel4Macro to run any formula on remote/closed books . This will work with any formula AND you don't need to put the formula in a cell ... you can assign the results to a variable in your VBA Code...

Please Login or Register  to view this content.

[exc4libur]

Yeap i see, what is the difference between private function and only function????

[nimrod]

"Private" functions and "Private" Subs can only be seen by other functions and subs within the same module. While Public or plain Functions and Subs are available to other open books and modules. Plus private's cannot be seen by the user. This way you can prevent functions being called by people/procedures that shouldn't call them.

[exc4libur]

"Private" functions and "Private" Subs can only be seen by other functions and subs within the same module. While Public or plain Functions and Subs are available to other open books and modules. Plus private's cannot be seen by the user. This way you can prevent functions being called by people/procedures that shouldn't call them.

Ohh i c thats a real problem! Thanks for the tip hahaha

One more thing,

ExecuteExcel4Macro("VLOOKUP(""" & LookUpValue & """,[Book1.xls]""" & VSheet & """!C1:C6,""" & ReturnColmn & """,FALSE)")

I am not managing to run the procedure correctly, whats wrong? I checked all spaces and """, but it doesnt run correctly.

[nimrod]

Here's an example of the use of public and private functions...

Do you see how the Public functions hide , and do , some of the grunt work for the procedures that call them .. while the core changes would still only occur in one place .. the private function. This is a set of functions that allow me to find the entire bottom of a sheet ... no matter what the version is .

Please Login or Register  to view this content.

[exc4libur]

How did you learn these things? Im 20 yrs old and in less than 3 months ive seen a whole new world omfg, like excel is MAGIC! hahaha

[nimrod]

I've been coding since 1974 and I have all my code reviewed by other programmers... I just do the excel stuff as a way to relax and unwind

[exc4libur]

I've been coding since 1974 and I have all my code reviewed by other programmers... I just do the excel stuff as a way to relax and unwind

Hahaha holy **** 1974!

[nimrod]

See how my function is being used in this code ? ... the old method had 65536 hardcoded as the bottom of the sheet. The ThisBook_EntireSheetRowsCount function is more flexable ... it adjusts the bottom of the sheet based on the version your in.

Please Login or Register  to view this content.

[nimrod]

Hahaha holy **** 1974!

lol ... yep ... but don't let the age fool you .. I still snowboard 30-40 days a year and Mtn Bike in the summer ... not to mention IPSC ...

.... ok ... I've got to go dude ... cheers

[exc4libur]

See how my function is being used in this code ? ... the old method had 65536 hardcoded as the bottom of the sheet. The ThisBook_EntireSheetRowsCount function is more flexable ... it adjusts the bottom of the sheet based on the version your in.

Please Login or Register  to view this content.

Yes!! hahah i tweaked ur code a lil aswell hahah

Please Login or Register  to view this content.

But its not working, its only returning blank! Any ideas?

[nimrod]

Sorry ... I really really have to go ... I'm sure we'll run into each other again... cheers

[exc4libur]

Sorry ... I really really have to go ... I'm sure we'll run into each other again... cheers

Oh well! Best regards eh! Thanks for everything, genius from 74 hahaha

[nimrod]

Where I am I don't have access to excel but .... I think that your full path is the problem it should end with "\"

Fullpath = "C:\Users\exc4libur\Desktop\"

[JBeaucaire]

Where I am I don't have access to excel but .... I think that your full path is the problem it should end with "\"

Fullpath = "C:\Users\exc4libur\Desktop\"

Yes, always always always! I comment all my "fPath" variables with that reminder...

Please Login or Register  to view this content.

[nimrod]

JB is right ... the issue of the "\" can be a problem ... soooo let's improve the function to automatically take care of the issue by adding " If Not Right(FullPath, 1) = "\" Then FullPath = FullPath & "\"" to the function. Now since this is a function we don't have to run around to all the places its used ... we just make the single change in one place Now anytime the "\" is forgotten it will be automatically added.

Please Login or Register  to view this content.

[exc4libur]

Thanks god i solved it lol

Please Login or Register  to view this content.

[exc4libur]

Thanks for the help guys!

[JBeaucaire]

Last suggested tweak, your row/col thinking is very xl2003, which is fine, but your code may not work as expected on more recent version. You can improve that with a simple tweak, too:

Please Login or Register  to view this content.

That will function the same in all versions where the bottom row and furthest column is different.

[Vbadgeri]

Hi,

Thanks Nimrod for the post.
I did some experiments and have modified according to my requirements. However the function works only for strings and fails when numbers are involved, how do we overcome that ?

Thanks,
Vijay