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]

"I get an error msg..."

On what statement? Hit the DEBUG button.

Skip,

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

 

[SkipVought]

"I get an error msg..."

What message and on what statement? Hit the DEBUG button.

Skip,

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

 

[ItIsHardToProgram]

Right, my bad, it says the specified functino does not exist, on =Ylookup(argument,argument,argument)

Ty though, the value() solution is the best for our situation.

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

 

[SkipVought]

"the specified functino does not exist"

Where do you have your Function code?

Skip,

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

 

[ItIsHardToProgram]

In the vbase console, Tolls, macro, add macro (name)

Edit macro --> typed in my sub.

close the Vbase console.

Typed =ylookup(argument,argument,argument) and it does not recognize ylookup.

Thanks for everything skip,

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

 

[SkipVought]

"vbase console" HUH!

Please close vbase console!!!!!

Select your sheet.

alt+F11 toggles between the activesheet and the VB Editor INSIDE OF EXCEL.

This is where the action is!

In the VB Project (ctl+R) insert a MODULE

Copy your Function code in this module.

Save the WORKBOOK.

Use your function like any other spreadhseet function.

Skip,

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

 

[ItIsHardToProgram]

Alright thank you, that should do it, I was using the excel VB editor, just misnamed it. Ill figure it out tomorow morning.

You've been of great help today, Thank you very much skip.

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

 

[SkipVought]

Glad to help.

Skip,

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