Problem with code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a workbook with 9 worksheets and 2 charts. I am o.k. with exluding
the worksheets and charts. The problem with code come in the line

"If Abs(cell.Offset(0, i).Value) > cell.Parent.Cells(7, i + 1).Value Then".

You guys are pretty smart guys and can probably see what the code does.
But in short, it copies rows from the required worksheets that meet a
criteria, based on row 7 on the "Greater than" workheet, and then pasts them
in the Greater than sheet starting at row 9. Are you guys able to figure out
why this code will not work anymore?

Option Explicit

Sub PostGreaterThan()

Dim shGreat As Worksheet
Dim wsh As Worksheet
Dim cell As Range
Dim rID As Range
Dim i As Long
Dim rDest As Range

Set shGreat = ThisWorkbook.Worksheets("greater than")
shGreat.Range("A9:IV65536").Clear

For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> shGreat.Name And wsh.Name <> "Company & Fleet Totals"
Then
Set rID = wsh.Range("a9", wsh.Range("A" &
wsh.Rows.Count).End(xlUp))

For Each cell In rID.Cells
If Not IsEmpty(cell) Then
For i = 4 To 11
If Abs(cell.Offset(0, i).Value) >
cell.Parent.Cells(7, i + 1).Value Then
Set rDest = shGreat.Range("A" &
shGreat.Rows.Count).End(xlUp).Offset(1, 0)
cell.EntireRow.Copy rDest
Exit For
End If
Next i
End If
Next cell
End If
Next wsh

Thanks,
Steve
 
How exactly is it not working? Are you getting Type Mismatch for text in the
cells?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
It is copying and pasting everything from the other worksheets. Instead of
jus the rows that meet the criteria.
 
I think Bob's point was for you to go look at the worksheet and actually check
what are in those two cells.

If you have Text in either/both of those cells, your abs() function will blow up
nicely with a, guess what, run time error of 13.
 
The two cells that are in this line:

If Abs(cell.Offset(0, i).Value) > cell.Parent.Cells(7, i + 1).Value

If you're not sure where they are when it blows up, you could add:

debug.print cell.offset(0,i).address & "--" & cell.offset(0,i).value
debug.print cell.parent.cells(7,i+1).address _
& "--" & .cell.parent.cells(7,i+1).value
debug.print "============" 'just a separator

Right before your "if" statement.

The next time it blows up, take a look at the immediate window (ctrl-g within
the VBE).
 
Back
Top