Please evaluate my new function

I am sure somebody already came up with a solution to do a vlookup
towards the left, but I wanted to try and build my very own function,
which I dubbed VSearch. This is the first function I design and I seek
your opinion on the code, as well as suggestions to improve on it.

The function requires three arguments, just like vlookup:
  Var_search represents what is looked up (either a value or the
reference to a cell)
  Rng_where represents the range where the value defined above will be
searched
  Int_column_offset represents the number of columns between the range
Rng_where and the range      that contains the value to be returned by
the function.

Now here is the code. What do you guys think?



Function VSearch(Var_search_what As Variant, Rng_where As Range,
Int_column_offset As Integer) As Variant

Dim Int_row_position As Integer
Dim Rng As Range
Dim Bln_Record_Found As Boolean
Dim Var_Searched_Value As Variant

'Checks whether the user input a value or a reference to another cell
If VarType(Var_search_what) = 0 Then
        Var_Searched_Value = Range(Var_search_what).Value
Else:   Var_Searched_Value = Var_search_what
End If

'Find location of matching record in target range
Bln_Record_Found = False

For Each Rng In Rng_where
    If Rng.Value = Var_Searched_Value Then
        Int_row_position = Rng.Row - Rng_where.Cells(1, 1).Row + 1
        Bln_Record_Found = True
    End If
    If Bln_Record_Found = True Then Exit For
Next


If Bln_Record_Found = True Then
    VSearch = Rng_where.Offset(0,
Int_column_offset).Cells(Int_row_position, 1).Value
Else: VSearch = "Not found"
End If

End Function

0
lcazarre (6)
8/15/2006 12:25:22 PM
excel 39879 articles. 2 followers. Follow

3 Replies
337 Views

Similar Articles

[PageSpeed] 47

Why not use Find to check if value exists rather than looping?

-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Caezar" <lcazarre@gmail.com> wrote in message
news:1155644722.545544.171560@b28g2000cwb.googlegroups.com...
> I am sure somebody already came up with a solution to do a vlookup
> towards the left, but I wanted to try and build my very own function,
> which I dubbed VSearch. This is the first function I design and I seek
> your opinion on the code, as well as suggestions to improve on it.
>
> The function requires three arguments, just like vlookup:
>   Var_search represents what is looked up (either a value or the
> reference to a cell)
>   Rng_where represents the range where the value defined above will be
> searched
>   Int_column_offset represents the number of columns between the range
> Rng_where and the range      that contains the value to be returned by
> the function.
>
> Now here is the code. What do you guys think?
>
>
>
> Function VSearch(Var_search_what As Variant, Rng_where As Range,
> Int_column_offset As Integer) As Variant
>
> Dim Int_row_position As Integer
> Dim Rng As Range
> Dim Bln_Record_Found As Boolean
> Dim Var_Searched_Value As Variant
>
> 'Checks whether the user input a value or a reference to another cell
> If VarType(Var_search_what) = 0 Then
>         Var_Searched_Value = Range(Var_search_what).Value
> Else:   Var_Searched_Value = Var_search_what
> End If
>
> 'Find location of matching record in target range
> Bln_Record_Found = False
>
> For Each Rng In Rng_where
>     If Rng.Value = Var_Searched_Value Then
>         Int_row_position = Rng.Row - Rng_where.Cells(1, 1).Row + 1
>         Bln_Record_Found = True
>     End If
>     If Bln_Record_Found = True Then Exit For
> Next
>
>
> If Bln_Record_Found = True Then
>     VSearch = Rng_where.Offset(0,
> Int_column_offset).Cells(Int_row_position, 1).Value
> Else: VSearch = "Not found"
> End If
>
> End Function
>


0
bob.NGs1 (1661)
8/15/2006 1:03:46 PM
How would you do that?

0
lcazarre (6)
8/15/2006 1:16:17 PM
It's described well in VBA help


-- 
 HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Caezar" <lcazarre@gmail.com> wrote in message
news:1155647777.467058.191640@74g2000cwt.googlegroups.com...
> How would you do that?
>


0
bob.NGs1 (1661)
8/16/2006 4:22:44 PM
Reply:

Similar Artilces:

Help with Regexp, please
Hi, The regular expression (\d{15,16}) matches a substring in a cell. I want to extract the remaining part of the cell ie. from the character after the matched substring till the end of the string in the cell using a regular expression. Is it possible to do this? Thanks in advance for the help. Regards, Raj Raj, with abcd15,16xyz in a cell the code below abstracts abcxyz. Maybe you can use this as a basis to develop your code Function GetSubstring(S As String) As String Dim re As Object Set re = CreateObject("vbscript.regexp") re.Pattern = &quo...

Lookup Function #4
In Column A, I have the following listed down the column: Initial Independent 2 3 4 5 etc etc.... Now in Column B next to these, I have a corresponding monetary value. Now, My lookup function works for the numbers but it doesn't work for the words 'initial' or 'independent'. Any ideas why? The numbers bring back the corresponding value perfectly but if I choose 'initial' it bring back a wrong value. Here's my function: =N5*24*LOOKUP(C2,A2:A13,B2:B13) From Excel's help for LOOKUP: The values in lookup_vector must be placed in ascending order: ...,...

PLEASE PLEASE HELP
I Have an emergency... I deleted a macro !!! I have 60 rows with checkboxes on cells A1 trough A60 that automatically as checked were inserting current dates on the cell next to it. for some reason a disaster happened and the macro was deleted or messed up. I tried searching for help in these groups, but none so far. I found one, but it is not a macro besides when I use that function all the other previous dates keep changing when other checkboxes are checked on in different days of the week. If you can please guide me to the right answer I will appreciated. Hope I made sense with my explana...

Sum Function Help Please
I have created two Option Groups, setting up values against the radio buttons. The output from both boxes works fine (thanks to the Wizard), however when I'm creating a formula on the report, I'm unable to add the two returned values together. If I type in =([Q1]+[Q2]) then I get both numbers concatenated together (ie returned value from Q1 is 1 and returned value from Q2 is 2, the answer I get is 12 !!!!.) Interestingly if I enter =([Q1]-[Q2]) then I get the correct answer. Any help would be greatly appreciated here. I could be wrong, but it seems like access i...

New user #4
How do start using the e-mail account. Go to your ISP's web page and look at their information about setting up = an email account in Outlook. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. =20 After furious head scratching, Jesus Ramon asked: | How do start using the e-mail account. What is the ISP's web page? How do i find it? What does ISP stand for? "Milly Staples [MVP - Outlook]" wrote: > Go to your ISP's web page and look...

2nd request for help -PLEASE PLEASE PLEASE
went through a windows update and was asked to reboot which I did. I had Outlook open and closed it before rebooting - it was opened to contacts. when I tried to access Outlook I got an error message saying "your personal address book could not be opened. the file is either not accessible or not a personal address book. choose retry to access your personal address book, or choose cancel to not open your personal address book with this application." I have no other application to open this address book with! my contact info is detrumental to me as well as my Calendar ...

How do I move my appointments from and old pst file to a new one?
My Outlook pst file has been growing and the solution to decreased the size is to create a new pst file. All emails are now moved. All contacts have been moved too. The problem is moving the appointments from one pst file to another. Help? Thank you all in advance. Jo However you did it for emails and contacts, for appointments it works the same way. You could use the File/Archive, or move some items manually. In order to do the latter, switch to a table view. -- Best regards Michael Bauer - MVP Outlook Category Manager - Easily share your categories: SAM - Automati...

Text Box / Cell Evaluation Not Consistant
Whenever I comapre the value of a Reference Cell to the value of a TextBox LinkCell using an IF statement, I get conflicting results. If I compare two alphanumeric values the return is TRUE, whereas two numbers return a FALSE. I used the Evaluate Formula function and noticed that the value of the LinkCell is enclosed in quotes so I assume LinkCells are alway Text, but when I format the Reference Cell as Text, I get the same FALSE return. Any suggestions on why this might be happening? A textbox always returns text. Something like this (where A1 is linked cell, B1 numeric) ....... ? =IF...

Can't SEND SSL email out!! Please Help... it HURTZ!
OKay... being a bit silly about he hurting part. I have a strange email issue with some of my computers at home that I use for work. We have 4 computers. 2 of them work FINE, the other 2 have issues. I know the email accounts are working correctly because if I run them on the 2 *good* computers they work like they should. Configurations: (GOOD Notebooks) 1 - HP AMD-XP notebook with WinXP-Home SP1, Office 2000 2 - Sony P4 Notebook with WinXP-Home SP1, Office 2000 (Bad systems) 3 - Dell P3 notebook with WinXP-Pro SP1, Office 2002 4 - Compaq AMD-XP with WinXP-Home SP2, Office 2000 ALl ma...

In Word How do I switch off auto date insertion please
How can I disable? ...

Copy HIDDEN columns to a new workbook
Hi there! Is there any code which you can help me with to copy HIDDEN COLUMNS to a new workbook. I'm using below codes: (COLUMNS HIDDEN are columns J-K-L) Sub Copy_With_AutoFilter1() 'Note: This macro use the function LastRow Dim My_Range As Range Dim CalcMode As Long Dim ViewMode As Long Dim FilterCriteria As String Dim CCount As Long Dim WSNew As Worksheet Dim sheetName As String Dim rng As Range Dim WS As Worksheet Set My_Range = Worksheets("Sheet1").Range("A1:BN" & LastRow(Worksheets("...

I need help with SQL please
I have a form with a group box on it. The code for it is: 20 Select Case grpPlan.Value Case 1 30 strMon = "WorkRec" 40 Case 2 50 strMon = "IntRec" 60 Case 3 70 strMon = "FinalRec" 80 End Select After I select the one I want then the SQL is build based on the selection and a report is generated. strWhere = "EmpSuper.WorkRec=""" & strMon & _ """ or EmpSuper.IntRec=""" & strMon & _ &quo...

Migrating DPM2007 32 bit to a new DPM2007 64 bit
Hello, As I am expanding the number of system and type of Data application I am protecting (currently protecting exchange and would like to add sharepoint). to be able to protect more data and more systems I need to migrate my exisiting 32 bit to 64 bit. Currently the system is 32 bit W2k3 OS and DPM 2007 32bit. Built a new system with new name OS W2K8 R2 and DPM 2007 64 bit I am planning to just shutdown the old system detach the LUN's from it an re-introduce them to the new system and restore the Database. than re-attach the protected agent to the new system Would that w...

LNK2005, DllMain, new, delete, and sscanf
I am running into an issue with the linker error 2005 in relation to DllMain, and the new & delete operators. I came across the following KB article at MSDN's site: http://support.microsoft.com/default.aspx?scid=kb;en-us;148652 This worked for one of the project to which I am importing some code - not a DLL though, it only had problems with new & delete. I went to import the code into a DLL project and then DllMain became an issue. I have tried to wits end to find a solution for this, and the only one I can find is the KB article, but the resolutions that I can figure ou...

Do I need to build a new platform (creat new nk.bin)
Hi every one, I am new here and I will be happy to get youre help. I am not so good in platform building but I must now do it. We are Working with ICOP board vx86-6042a (http://www.dmp.com.tw/tech/ icop_cd/DataSheet/Half-Size/Vx86-6042.pdf) we have a platform that is ok for us so we copy it to any new computer. We are using COM1 only, now we need COM2 too but it is not working, what is interesting is that I am going to COM1 and plugging in my rs232 to COM2 as it is on the link picture. I have a few Questions 1. Is this disorder with the come's a problem in board or in platform ...

Please Please Please help
Hi I have a Worksheet with option buttons and I need the users to be able to click on the options but not be able to drag or move them. I don't want the users to be able to click into or type anything in any of the cells - only these options buttons???? How is it done. I have tried unlocking all cells and then locking the individual ones, but the user can still drag the option button around and they could do this by mistake thus upsetting the design of my sheet! I have tried protecting the sheet for Objects - but then I can't click on the option at all ....Please someone help...

suddenly cannot write new message. server cannot find send/receiv
all of a sudden i cannot write new message. a warnige comes up informing me send/receive cannot be found. Have i deleted something inadvertantly? ...

VB
I have a new computer at home and have moved (copied) a lot of my Excel stuff from my work computer to my new computer. Things run relatively smoothly, but there are a couple of things I don't understand. 1) If I open VB from a spreadsheet - View, Toolbars, Visual Basic, VB Editor - and open a module, the screen is blank - the module shows nothing in it. If I go to Tools, Macros, and select a particular Macro, it pulls up the module and shows the code. 2) I am using the following to re-size the VB code windows: Dim cmpComponent As VBIDE.VBComponent Dim cmpComponents As ...

conditional formatting help please 07-19-07
Hello, I am having trouble using the conditional formatting. I want the background of the "End Date" to turn red when the "Start Date" is entered and then for the red background to go away after an "End Date" is entered. I used the expression ("Start Time" Is Not Null) and that made my background red but I can't seem to figure out how to get the red background go away. I tried a couple of diffrent expression like setting another condition ("End Time" Is Not Null) but it didn't work. Can some please help me find the right express...

modify macro
Hi everyone I'm VBA dumb so please bear with me. I have a macro that inserts a row at the selection and then I want it to copy the cell below the selection and paste into the new row's cell. Here's what's in the macro now Selection.EntireRow.Inser Range("D23").Selec Selection.AutoFill Destination:=Range("D22:D23"), Type:=xlFillCop Range("D22:D23").Selec When I run the macro, a row is inserted above the cursor's location (no matter where it's placed) but the AutoFill is always to D23. My question is how do I get the cod...

HELP
Previously using outlook with network as workgroup. Went to main office and joined domain.. got new persona. How do I import my mail from the other persona? I exported to a .pst and logged into the domain, I tried to import but it says I do not have permission to import. Any ideas? Make sure the PST file is located on a drive where you have read/write/modify/exclusive access rights. "Mike Gregg" <mgregg@emamarketing.com> wrote in message news:efmVNaWGHHA.4652@TK2MSFTNGP04.phx.gbl... > Previously using outlook with network as workgroup. > > Went to main off...

Simple formula
Hi, I'm a new to Excel so please excuse my ignorance in advance. I have data in Column C that I need to multiply with Column D. I know (for example) I can get a result by placing the formula: =sum(C3*D3) in cell E3. But, I have very long columns of data and I don't want to type in =sum(C4*D4)in cell E4, =sum(C5*D5) in cell E5 etc. My question is; can I use a wildcard for the column number and then just paste this formula in each E cell? Thanks On Fri, 22 Jul 2005 14:02:51 +0100, PigPOg <simon@capella.co.uk> wrote: >Hi, I'm a new to Excel so please excuse my ignoranc...

OWA problem, Please help is really urgent.
The page cannot be found The page you are looking for might have been removed, had its name changed, or is temporarily unavailable. -------------------------------------------------------------------------- Please try the following: a.. If you typed the page address in the Address bar, make sure that it is spelled correctly. b.. Open the server home page, and then look for links to the information you want. c.. Click the back button to try another link. HTTP 404 - File not found Internet Information Services ---------------------------...

Help: Win2k3 SP1 Continually Breaks Exchange 2003 (This is crazy and long, but please please read)
I am in desperate need of help. I have been through 2 different support calls with Microsoft, and their fixes keep on breaking. Someone here must have some kind of experience with this. Here is the scenario (simplified the best I can): Domain A (root.company.com) Domain B (child.company.com) There is a 2-way trust between these domains. Exchange was installed in Domain B. In DomainA, I created a user account named "SuperAdministrator". SuperAdministrator is a member of ROOT\Adminisrators, ROOT\Domain Admins, ROOT\Enterprise Admins, ROOT\Schema Admins, and all the Exchange group...

Adding new customized Reports do not work
I installed the Microsoft Report Manager on the CRM server. I then installed Crystal Reports 9.2.2 on my client machine. I had to create a couple of ODBC connections from my machine to the CRM server. I then wrote a custom report and then copied it to the CRM server's C: drive TS'd into the CRM server, went into the Report Manager and added the report. Then logged into the CRM system from my client machine and tried to view the report and it tells me "Failed to load database information. File <filename.rpt>" Did I publish the report wrong or does it have anything ...