Excel IF..Then..Else VBA routine

[zan2006]

Hi,

I have an excel file with 2 sheets, being sheet A and B. On a cell in sheet B I wish to have a multiple If-Then-Else routine of more than 7, which I know Excel only supports up to 7 or 8. The current statement I have is:

=IF('A'!H4="",'A'!K4,IF('A'!H5="",'A'!K5,IF('A'!H6="",'A'!K6,IF('A'!H7="",'A'!K7,IF('A'!H8="",'A'!K8,IF('A'!H9="",'A'!K9,IF('A'!H10="",'A'!K10,IF('A'!H11="",'A'!K11))))))))

As statements takes value from Sheet A into a cell in sheet B, I wish to put this in a Function routine. As I am not a VBA guru, for those of you out there who are, are you able to advise me how best to write this as a function.

 

[Fenrirshowl]

Try

Function FindNull(TargetRange As Range, ResultRange As Range)
Dim i As Long
Dim x
i = 0
For Each x In TargetRange
    i = i + 1
        If x = "" Then
            FindNull = ResultRange(i)
        Exit Function
    End If
Next x
End Function

Fen

 

[Fenrirshowl]

Perhaps a bit more explanation is required -
Given your function is effectively a "IF Hx is blank then return Ax" the above function runs through the TargetRange (H1:H11) and, when it finds a blank cell, returns the corresponding entry in the ResultRange (A1:A11).

 

[zan2006]

Hi,

I have found the perfect solution based on the following Excel functions.

=INDEX('A'!K4:K11, MATCH(" ", 'A'!H4:H11, 0))

 

[Fenrirshowl]

Zan

I have to admit that using match was my first thought but if the cell is blank/empty and you are looking for a space, the function shouldn't work.

I also found problems with searching for "", just leaving the criteria blank or referencing a blank cell as the criteria (in Excel 2000). All of these variations resulted in #N/A.

If your match function works, please can you advise which version of excel you are using?

Thanks

Fen

 

[zan2006]

Fen,

I am using Microsoft Office XP (Excel XP), and you are absolutely right that when searching for a "" there is a problem. What I had to do is make the cells have a space " " and so search for " " instead using:

=INDEX('A'!K4:K11, MATCH(" ", 'A'!H4:H11, 0))

This works magic!

 

[Fenrirshowl]

Index / Match (and CountIf in case of several matching records)combinations are my preferred method to return information. I can't remember the last time I used a VLookup (as most of the information I deal with is not sorted & sorting the data would cause problems elsewhere).

 

[AnotherHiggins]

Fen,

At the risk of taking this thread off topic, I just wanted to take a second to defend the VLookUP. If you set the fourth argument as FALSE (or zero), you don't need to have the table sorted. The final argument defaults to TRUE if omitted.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Fenrirshowl]

Cheers John

Just out of interest, which of the following uses less resources?

1) 4 VLookup functions returning 4 different pieces of information from different columns

2) 1 Match function and 4 Index functions returning the same information.

Generally I deal with a lot of records and will need to reference a lot of different spreadsheets so efficiency is a requirement.

My thought is that (1) requires 4 sets of evaluations (to find the correct record) and 4 data returns and (2) requires 1 evaluations and 4 data returns, so 2 would be more efficient. Is that the correct thought process in this instance?

Fen