PDA

View Full Version : Solved: how fix it - vlookup in visible range (not invisible)



danovkos
06-30-2011, 06:39 AM
Hi all,
i try to search only in visible values (if is value hidden - with autofilter - for me does not exist)

i have this, but this doesnt works and with debugging it return
for value
OKed = error 2042.
but it return this error when is value hidden and also when it doesnt. :(


Set kriesRange = Worksheets("Krieš").Range("a2:a68")
Set VisibleRange2 = kriesRange.SpecialCells(xlCellTypeVisible)
OKed = Application.VLookup(2, VisibleRange2, 1, 0)



any suggestions?

thx a lot

Rayman
06-30-2011, 02:29 PM
Hi all,
i try to search only in visible values (if is value hidden - with autofilter - for me does not exist)

i have this, but this doesnt works and with debugging it return
for value
OKed = error 2042.
but it return this error when is value hidden and also when it doesnt. :(


Set kriesRange = Worksheets("Krieš").Range("a2:a68")
Set VisibleRange2 = kriesRange.SpecialCells(xlCellTypeVisible)
OKed = Application.VLookup(2, VisibleRange2, 1, 0)



any suggestions?

thx a lot

Try this:
Sub Test()
Dim kriesRange As Range
Dim celkries
Dim OKed
Set kriesRange = Worksheets("Kries").Range("a2:a68")
For Each celkries In kriesRange
If celkries.Rows.Hidden = False Then
OKed = celkries.Offset(0, 1)
End If
Exit For
Next
End Sub

Rayman
06-30-2011, 02:58 PM
More coplete code:

Rayman
06-30-2011, 02:58 PM
Sub Test()
Dim kriesRange As Range
Dim celkries
Dim OKed
Set kriesRange = Worksheets("Kries").Range("A2:A68")
For Each celkries In kriesRange
If Not celkries Is Nothing And celkries.Rows.Hidden = False And celkries = 2 Then
OKed = celkries.Offset(0, 1)
End If
Exit For
Next
End Sub

danovkos
06-30-2011, 10:51 PM
thx for your help
ill try it

:hi: :clap:
thx a lot