lookup function??

A

Atishoo

i have a range which contains a series of values some are numbers some are
text and some are spaces. I want to create a copy of this list further up the
sheet but without the spaces.

I have tried using the lookup function and the lookup function with match
but always have trouble returning both numbers and text.

So I want cell C3 to return the first non empty cells value in range
C50:C150 and C4 to return the second non empty cell in the same range and so
on to a total of 15 values (c18).

any ideas ??
 
M

Mike H

Hi,

A UDF perhaps. Alt + F11 to open VB editor. Right click 'This workbook' and
paste the code below in on the right.

Call the code with this in C3

=getrange(C50:C150,ROW(A1))

Drag down.

Function GetRange(rng As Range, instance As Integer)
For Each c In rng
If c.Value <> "" Then
instfound = instfound + 1
If instfound = instance Then GetRange = c.Value
End If
Next
If GetRange = "" Then GetRange = "Error"
End Function


Mike
 
A

Atishoo

seems logical but not working! not sure why??

Mike H said:
Hi,

A UDF perhaps. Alt + F11 to open VB editor. Right click 'This workbook' and
paste the code below in on the right.

Call the code with this in C3

=getrange(C50:C150,ROW(A1))

Drag down.

Function GetRange(rng As Range, instance As Integer)
For Each c In rng
If c.Value <> "" Then
instfound = instfound + 1
If instfound = instance Then GetRange = c.Value
End If
Next
If GetRange = "" Then GetRange = "Error"
End Function


Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top