Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with code
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) "Steve" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with code
It is copying and pasting everything from the other worksheets. Instead of
jus the rows that meet the criteria. "Bob Phillips" wrote: 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) "Steve" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code Problem | Excel Programming | |||
code problem | Excel Programming | |||
vba code problem | Excel Programming | |||
vba code problem | Excel Programming | |||
Code Problem ! | Excel Programming |