Function result differs from same code for subroutine

2 Feb 2006 10:27 AM

dave-mac

this is mad... am trying to write a function to return the first cell
in a filtered list...
but while developing it I came across this...

these two pieces of code are exactly the same, except one is in a
function...

the subroutine returns all my filtered cells, the function returns
everything!!!!

any clues whats happening???

Function fRow() As String
a = 0
For Each c In
Sheets(1).AutoFilter.Range.SpecialCells(xlCellTypeVisible)
a = a + 1
'If a = 2 Then fRow = c.Value
Debug.Print c.Value
Next
End Function
'------------------------------------
Sub fRow2()
a = 0
For Each c In
Sheets(1).AutoFilter.Range.SpecialCells(xlCellTypeVisible)
a = a + 1
'If a = 2 Then fRow = c.Value
Debug.Print c.Value
Next
End Sub

2 Feb 2006 8:18 PM

Ken Halter

<dave-***@ntlworld.com> wrote in message
Show quoteHide quote

news:1138876048.367459.239250@g14g2000cwa.googlegroups.com...
> this is mad... am trying to write a function to return the first cell
> in a filtered list...
> but while developing it I came across this...
>
> these two pieces of code are exactly the same, except one is in a
> function...
>
> the subroutine returns all my filtered cells, the function returns
> everything!!!!
>
> any clues whats happening???
>
> Function fRow() As String
> a = 0
> For Each c In
> Sheets(1).AutoFilter.Range.SpecialCells(xlCellTypeVisible)
> a = a + 1
> 'If a = 2 Then fRow = c.Value
> Debug.Print c.Value
> Next
> End Function
> '------------------------------------
> Sub fRow2()
> a = 0
> For Each c In
> Sheets(1).AutoFilter.Range.SpecialCells(xlCellTypeVisible)
> a = a + 1
> 'If a = 2 Then fRow = c.Value
> Debug.Print c.Value
> Next
> End Sub

Are you sure you don't mean "Returns Nothing"? The code above won't return
anything at all because the "fRow = " line is commented out.

If item 2 is all you're concerned with, use something like....

> Sheets(1).AutoFilter.Range.SpecialCells(xlCellTypeVisible)
> a = a + 1
> If a = 2 Then

      fRow = c.Value 'set the function return
      Exit For 'bail out of the loop and return to the caller
   End If

--
Ken Halter - MS-MVP-VB - Please keep all discussions in the groups..
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Freeware 4 color Gradient Frame? http://www.vbsight.com/GradFrameCTL.htm

3 Feb 2006 7:49 AM

dave-mac

Ken,

Thanks for your input, I dont think I was clear enough...

Am in the middle of developing this but I happend on this problem as I
was testing what c.value would return.

the debug.print statement returns completly different results. While
in the subroutine it does indeed debug.print the values of my filtered
cells, with the function it debug.prints everything!!

Am just lost as to why it would return different results.

at the end of the day i need the function will return the second
SpecialCells(xlCellTypeVisible) like the subroutine does, but rignt now
the 'function' isnt behaving!

hope i made this clearer, its doing my head in. :\