Excel macro - VlookUp 1

[ItIsHardToProgram]

Hello everyone,

I am currently working on a excel macro for my company and I just want to validate that this macro will work before implementing it since the files we are using are rather large.

This is what the macro should do:

Go look in a file, using an account number, at a specific column for a long.

Put the specific value in a temporary variable

Test if everything was performed correctly if not, send a msg indicating where the error was found.

Round the value.

Put the value in the active cell.

The macro I came with that I call YlookUp is the following.

Private sub YlookUp(GLaccount as intger, location as string, ActColumn as integer, TempVal as long) return TempVal

TempVal = VlookU(Glaccount,location,ActColumn,0)

If TempVal = null then
TempVal as string = ERROR
Msgbox("You have encountered an error with value " + 'GLaccount' + " " + location)
End if
Round(TempVal,3)
End Sub.


My problem is the following. I am not sure how to put the information in the excel cell so that the cell = TempVal.
I was thinking that something like this might work but im prety certain it wont.

[Highlight]=YlookUp(Bud09!$A18,'S:\Comptabilite\RPR - Budget 2009\FI\[11050_VillaStGeorges_B09.xls]B09 12 mois'!$A:$O,5)[/highlight]

I am very happy for any opinion on how I perform this "lookup" and on how to optimize it. If there is an error or if it is perfect (wich is impossible) Please still state a response. I will not implement this without a second opinion.

Thank you very much for your time.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

Hi,

Several problems.

1. A function returns a value to itself...

Function MyFunction()
   MyFunction = "Hello"
End Function

2. Calling a Spreadsheet Function requires an application reference

  MyValue = [b]Application.[/b]VLOOKUP(Arg1, Arg2, Arg3, Arg4)

3. the second argument in the VLOOKUP function is a range. See the modified code...

Private Sub YlookUp( _
    GLaccount As intger, _
    location As String, _
    ActColumn As Integer)

           YlookUp = Application.VLookup(GLaccount, Range(location), ActColumn, 0)

           If IsError(YlookUp) Then
                YlookUp = "Error"
                MsgBox ("You have encountered an error with value " & _
                    GLaccount & " " & _
                    location)
           End If
End Sub

tempValue serves no purpose. Also, I would not use a MsgBox in the function. "ERROR" is enough indication of a problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Thank you for the fast response, considering this issue, would you consider a function like this to be faster than a direct vlookup, considering the following line implemented by my boss? or would you consider this an unecessery update.

This is the line currently used:


=SI(ESTERREUR(RECHERCHEV(Prev08!$A1679,'S:\Comptabilite\RPR - Budget 2009\FI\[11250_Cascade_B09.xls]B09 12 mois'!$A:$O,5,0)),0,ARRONDI(RECHERCHEV(Prev08!$A1679,'S:\Comptabilite\RPR - Budget 2009\FI\[11250_Cascade_B09.xls]B09 12 mois'!$A:$O,5,0),0))

ESTERRUR is french for if there is an error, SI is french for if.

Would you consider the adjustment time saving? Since his line uses vlookup twice, once for the test and a 2nd time for inserting the value.

Thank you very much for your imputs Skip

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

But why even use a function to call a function???

Why not just...

  x = Application.VLookup(GLaccount, Range(location), ActColumn, 0)

  If IsError(x) Then
    'envoke error routine
  Else
    'use x in process
  End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I might use on a sheet...
[tt]
=IF(ISNA(MATCH(LookupVal,LookupRng,0)),error expression,vlookup expression)
[/tt]
since I believe that MATCH processes a bit faster than VLOOKUP.

Is the function calling a function faster? I don't know. Run a test on doing 60,000 SHEET formulas and your function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

I am not sure I understand how I would apply that in Excel without using a macro? maybe you can enlighten me on that?

You would apply this directly in the Excel spreadsheet cell?

Thanks alot skip

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Oh ok, posted too fast, thank you, I was thinking on running tests any way, but the data we have is on 2 linked sheets with 12*2600 vlookups * 2, so you can imagine my concern calling it twice on the same cell.

Thank you very much you were of great help

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Before closing this thread I have 2 questions.

First of all, Ive tried declaring the function as you stated in excel spreadsheet like this.

=Round(YlookUp(Prev08!$A1679, 'S:\Comptabilite\RPR - Budget 2009\FI\[11250_Cascade_B09.xls]B09 12 mois'!$A:$O,3),3)

Unfortunatly excel says that the function does not exist, what could be the cause of that?

Secondly, you have stated the following:

=IF(ISNA(MATCH(LookupVal,LookupRng,0)),error expression,vlookup expression)


Thank you again for your patience skip, very appreciated.

Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

There was a line missing,

You have stated the following

=IF(ISNA(MATCH(LookupVal,LookupRng,0)),error expression,vlookup expression)

Wouldnt I have to put MATCH(lookupVal,lookupRng,0) in the Vlookup expression returning to the inital problem of looking in the file twice?

Thank you very much, sorry for the spam.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

"Unfortunatly excel says that the function does not exist, what could be the cause of that?"

Your function must reside in a MODULE Code Window, and not in an Excel Object (ThisWorkbook, or any Worksheet) Code Window.

"Wouldnt I have to put MATCH(lookupVal,lookupRng,0) in the Vlookup expression returning to the inital problem of looking in the file twice?"

Yes you are accessing the file twice, once with what I believe is SLIGHTLY faster, the MATCH, and the second time with VLOOKUP. That's why I suggested a test to see if your function, which calls the lookup file ONCE, would be faster.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok I see, But the MATCH function returns me errors on cells where the VLOOKUP function did not, Why is that??? I noticed the column where to look was not mentioned, could that be the problem???

How would I call the macro considering it doesnt reside in excel, following the idea that I have:

=Round(YlookUp(Prev08!$A1679, 'S:\Comptabilite\RPR - Budget 2009\FI\[11250_Cascade_B09.xls]B09 12 mois'!$A:$O,3),3)

Is that possible in any way????

Thank you very much Skip,

Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

The Lookup Range for the MATCH function should be the FIRST COLUMN ONLY of the VLOOKUP function lookup range.


"How would I call the macro considering it doesnt reside in excel..."

It resides in a module in this Excel Workbook. It IS in the workbook file!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Sorry for the dumb statements...

I still can't figure out how to work out the macro like I want to...

Secondly the match function should be the first column only of the function lookup range...

So basicaly it should be soemthing like

'S:\Comptabilite\RPR - Budget 2009\FI\[11050_VillaStGeorges_B09.xls]A08 12 mois'!$A

But that returns an error, I definatly am doing something wrong....

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

[tt]
=MATCH(lookupValue, 'S:\Comptabilite\RPR - Budget 2009\FI\[11050_VillaStGeorges_B09.xls]A08 12 mois'!$A:$A,0)
[/tt]
when constructing the references in a function, it is best to ACTUALLY SELECT the range reference, rather than entering it manually. Use the F4 key and toggle thru the relative/absloute reference notations as desired.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Thank you for the pointer, indeed it works, hard to tell if its faster, I would like to run the test but I don't seem to be able to make my macrok fit in my worksheet, I understand the macro is part of the worksheet itself (I think?)

Would that mean that I would have to loop all the cells changing their values? or can I increment the macro in cells. I am not sure I fuly understand the logic of it all...

Thank you for helping me Skip, I at least got the 2nd part working.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Oh, I forgot to mention something very important in the start.

the logic of using a macro was that sometimes the type of value that the lookup would return was not a number (integer, long, etc....) Hence it would not pass correctly, that is why I wanted to do this through a vbase function so that when information passes through, it is stored in a long variable, making it a number what ever the format of the cell.

Do you understand my logic?

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

"...but I don't seem to be able to make my macrok fit in my worksheet..."

1. Your Function code belongs in a MODULE in the Workbooks VB Project (ctl+R in the VB Editor)

"Would that mean that I would have to loop all the cells changing their values? or can I increment the macro in cells. I am not sure I fuly understand the logic of it all"

2. Use your function like you would any other spreadsheet function.

"the logic of using a macro was that sometimes the type of value that the lookup would return was not a number (integer, long, etc....) Hence it would not pass correctly, that is why I wanted to do this through a vbase function so that when information passes through, it is stored in a long variable, making it a number what ever the format of the cell."

I do not understand. I can have a VLOOKUP return a number or a string. There is no way of knowing the SOURCE FORMAT using a lookup.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

What I mean is, if for example the lookup returned is a string it is still a number, simply on the wrong format. Having a macro that stores that string in a long for example, will change the format of the information returned. Hence the string will be a long, enabling to skip having to change the format of information in the accounting program.

Is my thinking wrong and the value will just not fit in the long function? Using Value[Vlookup(etc...) would maybe work?

The file sheets refered to in the function are taking information from an accounting program.

If the function is used like any other spreadsheet function, it means i should be able to call Ylookup(argument,argument,argument) in a cell?

This turned out to be a much more complicated issue than I initialy thought, sry for the missunderstandings.

Thank you for your patience skip,

Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

[tt]
=VALUE(VLOOKUP(D2,D2:E15,2,FALSE))
[/tt]
will convert a string that is all unmeric to a number.

"If the function is used like any other spreadsheet function, it means i should be able to call Ylookup(argument,argument,argument) in a cell?"

YES!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Heh! you seem to be relativly losing patience, I am very sorry to have taken so much of your time...

When I typed the following function

Public Function YlookUp( _
    GLaccount As intger, _
    location As String, _
    ActColumn As Integer)

           YlookUp = Application.VLookup(GLaccount, Range(location), ActColumn, 0)

           If IsError(YlookUp) Then
                YlookUp = "Error"
                MsgBox ("You have encountered an error with value " & _
                    GLaccount & " " & _
                    location)
           End If
End function

I get an error msg

This is officialy my last question since Value will probably be the easiest and fastest way.

Thank you very much, I would add you a 2nd star just for your patience with me.

Thanks skip,

Cordialy,

Julien Roy

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.