Find and Make Active

[twinpowr]

Hi, i have found this code here which i am trying to adjust to my needs

Sub finddate()
Dim cl As Range
Dim lastrow As Long
lastrow = Worksheets("Filter_CSM").Range("A65536").End(xlUp).Row
for each cl in Range("h2:H" & lastrow).Find(What:=DateValue(<>""))
If Not cl Is Nothing Then cl.Select
loop
End Sub

What i am trying to do is not to find a specific value, but find the cells in col H that have a date value (any date) in them and then i would want to use offset to add the value of offset(0,4) to a variable. i have tried different methods. The code above is the first part of what i am trying to do. I would really appreciate some help with this.

Thanks

Ally.

 

[MickG]

This code looks for "Dates in column "H" if found, the value in Column "L" is placed in an Array "Ray" and Results are displayed in column "A".

[COLOR="Navy"]Sub[/COLOR] MG03Mar03
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("H1"), Range("H" & rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] IsDate(Dn) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        ray(c) = Dn.Offset(, 4)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
Range("A1").Resize(c) = Application.Transpose(ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[twinpowr]

Thanks Mick

Thats Great. This puts me on the right path. its weird when i look at your code, i can
understand what is going on, but remembering how to do to, well that is something else entirely.

just a quick question. if i now want to search for this offset number in another sheet can i use something like this.

Set Rng = .Find(What:=Ray(c), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

 

[MickG]

Have a look at the "Find Method" in Vb Help, you will need to "Set" your Selected worksheet/Range as shown in the code, and if you want to find all values of Ray(c) then you will have to loop through the array.

Mick

 

[twinpowr]

Thanks Again Mick,

I really aprreciate your help!!

Ally.