Results 1 to 10 of 10

20050714, 17:09 #1
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Accessing a select statement from a cell (Excel 20
You could alos consider using the choose function.
Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20050714, 17:25 #2
 Join Date
 Nov 2002
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Accessing a select statement from a cell (Excel 20
Thanks but I don't understand how your reply is in reference to my question.

20050714, 19:16 #3
 Join Date
 Nov 2002
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Accessing a select statement from a cell (Excel 20
edited by <!profile=unkamunka>unkamunka<!/profile> to prevent long formula distorting browser size
I have a number of cells in an Excel sheet that performs the following long If condition shown below looking up data in different tables on worksheets and this would be easier to maintain in a select statement. Right now the if condition is define in each cell but I am having a mental block about how to set up a marco containing a select statement doing the same thing and access it from the cell. How do I do this?
Thanks
=IF(E17=2000,VLOOKUP(MATCH(G17,Tab0),Table2000,(I1 7+3)),IF(E17=2001,VLOOKUP(MATCH(G17,Tab01),Table20 01,(I17+3)), _
IF(E17=2002,VLOOKUP(MATCH(G17,TAB2),Table2002,(I17 +3)), IF(E17=2003, IF(C17="T", VLOOKUP(MATCH(G17,Tab03T),Table2003T,(I17+3)), VLOOKUP(MATCH(G17,Tab03),Table2003,(I17+3))),IF(E1 7=2004, IF(C17="T", VLOOKUP(MATCH(G17,Tab04T),Table2004T,(I17+3)), _
VLOOKUP(MATCH(G17,Tab04),Table2004,(I17+3))),0) ))))

20050714, 19:40 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Accessing a select statement from a cell (Excel 20
Do you mean this?
Public Function Bassett(rngCell As Range)
Dim rngTab As Range
Dim rngTable As Range
Select Case rngCell.Value
Case 2000
Set rngTab = Range("Tab0")
Case 2001
Set rngTab = Range("Tab01")
Case 2002
Set rngTab = Range("Tab2")
Case 2004
If rngCell.Offset(0, 2) = "T" Then
Set rngTab = Range("Tab03T")
Else
Set rngTab = Range("Tab03")
End If
Case 2004
If rngCell.Offset(0, 2) = "T" Then
Set rngTab = Range("Tab04T")
Else
Set rngTab = Range("Tab04")
End If
End Select
Set rngTable = Range("Table" & rngCell.Value)
Bassett = Application.WorksheetFunction.VLookup( _
Application.WorksheetFunction.Match( _
rngCell.Offset(0, 2), rngTab), rngTable, rngCell.Offset(0, 4) + 3, 0)
End Function
Use as
=Bassett(E17)

20050714, 22:33 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Accessing a select statement from a cell (Excel 20
=CHOOSE(ARG, RETURN1, RETURN2, ETC.) is useful in some situations as a way around nested IFs. In your case Jan Karel saw that your conditions run in increments of 1 from a value of 2000 though 2004. Look up CHOOSE in the Help, the start of the function
=CHOOSE(E71999,
will return 1 for an E17 value of 2000, 2 for 2001, etc, and then you plug the respective functions in each return. This is untested because I didn't recreate your range names *, but when you get the parens right it should work:
=CHOOSE(E71999,VLOOKUP(MATCH(G17,Tab0),Table2000,(I17+3)),VL OOKUP(MATCH(G17,Tab01),Table2001,(I17+3)), _
,VLOOKUP(MATCH(G17,TAB2),Table2002,(I17+3)),IF(C17 ="T", VLOOKUP(MATCH(G17,Tab03T),Table2003T,(I17+3)), VLOOKUP(MATCH(G17,Tab03),Table2003,(I17+3)),IF(C17 ="T", VLOOKUP(MATCH(G17,Tab04T),Table2004T,(I17+3)), _
VLOOKUP(MATCH(G17,Tab04),Table2004,(I17+3)),0))
HTH.
* which is why it's useful to post a censored sample spreadsheet for a complex problem.John ... I float in liquid gardens
UTC 7ąDS

20050725, 13:21 #6
 Join Date
 Nov 2002
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Accessing a select statement from a cell (Excel 20
LOL...I think so. Thanks and I will let you know if this works.

20050802, 18:46 #7
 Join Date
 Nov 2002
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Accessing a select statement from a cell (Excel 20
I am getting an error dialog when typing to use the Application.WorksheetFunction.Match function, see the attachment.

20050802, 18:53 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Accessing a select statement from a cell (Excel 20
We'd have to see the workbook to know why it happens.

20050802, 18:56 #9
 Join Date
 Nov 2002
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Accessing a select statement from a cell (Excel 20
Yea I understand and that is a problem to do as the worksheet has to do with CPA tasks plus its size is 552kb.

20050802, 19:04 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Accessing a select statement from a cell (Excel 20
<P ID="edit" class=small>(Edited by sdckapr on 02Aug05 16:04. added PS)</P>If a match is not found you will get this error. Excel "ignores" the error and reports the error in the cell. You will have to trap the error in VB
Steve
PS
Something like this:
<pre>Dim lRow As Long
Dim vValue
Dim rList As Range
lRow = 0
On Error Resume Next
lRow = Application.WorksheetFunction.Match(vValue, rList, 0)
On Error GoTo 0
If lRow = 0 Then
MsgBox vValue & " is not found"
Else
'Your code here for a using the matched row
End If</pre>