Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
hi Swingleft,
replace Range(C2.Address).Offset(0, -9).Value = "" by Cells(C2.Row, 1) = "" -- isabelle |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find(SPELER, LookIn:=xlValues) problem with hidden colloms!!!
sorry, you have to put the second "Application.ScreenUpdating" to True
-- isabelle |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.LookIn problem 2007 | Excel Programming | |||
How to test for valid 'Paste:=xlValues' before attempt. | Excel Programming | |||
Problem with setting a R / W property, FileSearch, LookIn | Excel Programming | |||
Complie Error with xlValues | Excel Programming | |||
How do I restore the LookIn, LookAt, SearchOrder in FIND | Excel Programming |