Object required error

[Peter Rump]

I am trying to search the same column in each sheet in a workbook for values within a range with the following, but get error 424 at line 2 -what have I done wrong?

Dim sh As Worksheet
Dim cell As Range
Dim LowVal As Long
Dim HiVal As Long
Dim i As Integer
Dim j As Integer
Set sh = Worksheets.Add(before:=Worksheets(1))

LowVal = 1 'these values are amended as necessary
HiVal = 10
j = 1
For i = 2 To ActiveWorkbook.Sheets.Count
For Each cell In Intersect(Sheets(i).[J:J],Sheets(i).UsedRange)
With Cell
If IsNumeric(.Value) Then
If .Value >= LowVal And .Value <= HiVal Then
sh.Cells(j,"A").Value =Sheets(i).Name
sh.Cells(j,"B").Value = .Address(False,False)
j+j+1
End If
End If
End With
Next
Next

[Bob Phillips]

Try this

Dim sh As Worksheet
Dim cell As Range
Dim LowVal As Long
Dim HiVal As Long
Dim i As Integer
Dim j As Integer
Dim rng As Range
Set sh = Worksheets.Add(before:=Worksheets(1))

LowVal = 1 'these values are amended as necessary
HiVal = 10
j = 1
For i = 2 To ActiveWorkbook.Sheets.Count
Set rng = Intersect(Sheets(i).Columns("J:J"), Sheets(i).UsedRange)
If Not rng Is Nothing Then
For Each cell In rng
With cell
If IsNumeric(.Value) Then
If .Value >= LowVal And .Value <= HiVal Then
sh.Cells(j, "A").Value = Sheets(i).Name
sh.Cells(j, "B").Value = .Address(False, False)
j = j + 1
End If
End If
End With
Next
End If
Next


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rump" <[email protected]> wrote
in message news:[email protected]...
>
> I am trying to search the same column in each sheet in a workbook for
> values within a range with the following, but get error 424 at line 2
> -what have I done wrong?
>
> Dim sh As Worksheet
> Dim cell As Range
> Dim LowVal As Long
> Dim HiVal As Long
> Dim i As Integer
> Dim j As Integer
> Set sh = Worksheets.Add(before:=Worksheets(1))
>
> LowVal = 1 'these values are amended as necessary
> HiVal = 10
> j = 1
> For i = 2 To ActiveWorkbook.Sheets.Count
> For Each cell In Intersect(Sheets(i).[J:J],Sheets(i).UsedRange)
> With Cell
> If IsNumeric(.Value) Then
> If .Value >= LowVal And .Value <= HiVal Then
> sh.Cells(j,"A").Value =Sheets(i).Name
> sh.Cells(j,"B").Value = .Address(False,False)
> j+j+1
> End If
> End If
> End With
> Next
> Next
>
>
> --
> Peter Rump
> ------------------------------------------------------------------------
> Peter Rump's Profile:
http://www.excelforum.com/member.php...o&userid=26277
> View this thread: http://www.excelforum.com/showthread...hreadid=395674
>

[Peter Rump]

Hi Bob

Thanks for that - it gets me past the error message but selects the empty cells rather than the ones with values in them. How do I correct that?

As you can probably tell by now I am very much a VBA beginner!

Peter

[Peter Rump]

Hi Bob

I think I have solved my problem. It seems to be that LowVal and HiVal expected integers whereas I was using dates. How should I Dim these Values to work on dates. Temporarily I have reformatted the column so that it works.

Peter

[Bob Phillips]

Peter,

Try

Dim LowVal As Date
Dim HiVal As Date

If you had defined them As Long it would probably have worked as well.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rump" <[email protected]> wrote
in message news:[email protected]...
>
> Hi Bob
>
> I think I have solved my problem. It seems to be that LowVal and HiVal
> expected integers whereas I was using dates. How should I Dim these
> Values to work on dates. Temporarily I have reformatted the column so
> that it works.
>
> Peter
>
>
> --
> Peter Rump
> ------------------------------------------------------------------------
> Peter Rump's Profile:
http://www.excelforum.com/member.php...o&userid=26277
> View this thread: http://www.excelforum.com/showthread...hreadid=395674
>

[Peter Rump]

Bob

I have tried that and it works fine. Many thanks for your help

Peter