Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

Hallo,

somewere in the middle of my macro is the next part

Set C2 = Sheets("Spelers").Range("J2:J65000").Find(SPELER, LookIn:=xlValues)

If C2 Is Nothing Then
MsgBox ("lege cel")
Else
Range(C2.Address).Offset(0, -9).Value = ""

End If

THe function works oke BUT

If i Hide Collum "J" this part of the macro does't work anymore.. is the
correct?

And does anybody know a reason for this.. and a solution?

thanks for all the help.


Swingleft

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

hi Swingleft,

replace
Range(C2.Address).Offset(0, -9).Value = ""
by
Cells(C2.Row, 1) = ""

--
isabelle

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

Hi Isabele,

what you wrote is a good help for the macro but it didn't solve the
problem..:-(

The problem is that when i hide the Collum "J"
the "Set C2..." line doesn't work
so the macro returns

"lege regel"

when i unhide the collum "J" the macro works fine..

so my question is..

is there a way to use the line

"Set C2 = Sheets("Spelers").Range("J2:J65000").Find(SPELER,
LookIn:=xlValues)"

with a hidden Collum.

gr.

Swingleft



"Swingleft" schreef in bericht
bel.net...

Hallo,

somewere in the middle of my macro is the next part

Set C2 = Sheets("Spelers").Range("J2:J65000").Find(SPELER, LookIn:=xlValues)

If C2 Is Nothing Then
MsgBox ("lege cel")
Else
Range(C2.Address).Offset(0, -9).Value = ""

End If

THe function works oke BUT

If i Hide Collum "J" this part of the macro does't work anymore.. is the
correct?

And does anybody know a reason for this.. and a solution?

thanks for all the help.


Swingleft

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

Try LookIn:=xlFormulas

ps. This line:

Range(C2.Address).Offset(0, -9).Value = ""

Works against either the activesheet or the sheet that owns the code (if the
code is in a sheet module).

If you want to change the cell on Spelers, you could use:

C2.Offset(0, -9).Value = ""





On 09/24/2011 17:02, Swingleft wrote:
Hallo,

somewere in the middle of my macro is the next part

Set C2 = Sheets("Spelers").Range("J2:J65000").Find(SPELER, LookIn:=xlValues)

If C2 Is Nothing Then
MsgBox ("lege cel")
Else
Range(C2.Address).Offset(0, -9).Value = ""

End If

THe function works oke BUT

If i Hide Collum "J" this part of the macro does't work anymore.. is the
correct?

And does anybody know a reason for this.. and a solution?

thanks for all the help.


Swingleft

--
Dave Peterson

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

you can use the MATCH function, if it's whole value of a cell that is searched

With Sheets("Spelers")
nLine = .Range("J" & Application.Match(SPELER, .Range("J1:J65000"), 0))
.Cells(nLine, 1) = ""
End With

--
isabelle

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

also you can temporarily unhide the column,

Application.ScreenUpdating = False
With Sheets("Spelers")
..Range("J:J").EntireColumn.Hidden = False
Set c2 = .Range("J2:J65000").Find(SPELER, LookIn:=xlValues)
..Range("J:J").EntireColumn.Hidden = True
End With
Application.ScreenUpdating = False

--
isabelle



Le 2011-09-25 09:15, isabelle a écrit :


you can use the MATCH function, if it's whole value of a cell that is searched

With Sheets("Spelers")
nLine = .Range("J" & Application.Match(SPELER, .Range("J1:J65000"), 0))
.Cells(nLine, 1) = ""
End With

Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
 

sorry, you have to put the second "Application.ScreenUpdating" to True

--
isabelle