Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
I am trying to search the same column in each sheet in a workbook fo values within a range with the following, but get error 424 at line -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(befo=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 Nex -- Peter Rum ----------------------------------------------------------------------- Peter Rump's Profile: http://www.excelforum.com/member.php...fo&userid=2627 View this thread: http://www.excelforum.com/showthread.php?threadid=39567 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
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(befo=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" wrote in message ... 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(befo=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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
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 ------------------------------------------------------------------------ Peter Rump's Profile: http://www.excelforum.com/member.php...o&userid=26277 View this thread: http://www.excelforum.com/showthread...hreadid=395674 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
Hi Bob I think I have solved my problem. It seems to be that LowVal and HiVa expected integers whereas I was using dates. How should I Dim thes Values to work on dates. Temporarily I have reformatted the column s that it works. Pete -- Peter Rum ----------------------------------------------------------------------- Peter Rump's Profile: http://www.excelforum.com/member.php...fo&userid=2627 View this thread: http://www.excelforum.com/showthread.php?threadid=39567 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error
Bob I have tried that and it works fine. Many thanks for your help Pete -- Peter Rum ----------------------------------------------------------------------- Peter Rump's Profile: http://www.excelforum.com/member.php...fo&userid=2627 View this thread: http://www.excelforum.com/showthread.php?threadid=39567 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error: 424 Object required | Excel Programming | |||
Error 424 - Object Required | Excel Programming | |||
424 Object required error | Excel Programming | |||
Object Required Error | Excel Programming | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |