Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set rng1 not working
I have:
Sub RepStats() On Error Resume Next Dim rng1 As range Set myrng = Worksheets("Rep Page").range("A:A") For Each c In myrng If c < "" Then Sheets("Rep Page").Select em = c.Value Sheets("Sheet1").Select Selection.AutoFilter Field:=2, Criteria1:=em With ActiveSheet.AutoFilter.range On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" Else Set rng = ActiveSheet.AutoFilter.range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").range("B2") End If ActiveSheet.ShowAllData Sheets("Rep Page").Select End If Next End Sub The line: On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 Is not working. Can you help me. There is data that is filtered but it wont copy, it just goes to: If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set rng1 not working
David,
Worksheets doesn't have an offset property: Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) Try just this, since you use the With construction: Set rng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) HTH, Bernie MS Excel MVP "David A." wrote in message ... I have: Sub RepStats() On Error Resume Next Dim rng1 As range Set myrng = Worksheets("Rep Page").range("A:A") For Each c In myrng If c < "" Then Sheets("Rep Page").Select em = c.Value Sheets("Sheet1").Select Selection.AutoFilter Field:=2, Criteria1:=em With ActiveSheet.AutoFilter.range On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" Else Set rng = ActiveSheet.AutoFilter.range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").range("B2") End If ActiveSheet.ShowAllData Sheets("Rep Page").Select End If Next End Sub The line: On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 Is not working. Can you help me. There is data that is filtered but it wont copy, it just goes to: If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set rng1 not working
That worked. One more thing, How do I get it to paste in the cell next to the
"em" value? "Bernie Deitrick" wrote: David, Worksheets doesn't have an offset property: Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) Try just this, since you use the With construction: Set rng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) HTH, Bernie MS Excel MVP "David A." wrote in message ... I have: Sub RepStats() On Error Resume Next Dim rng1 As range Set myrng = Worksheets("Rep Page").range("A:A") For Each c In myrng If c < "" Then Sheets("Rep Page").Select em = c.Value Sheets("Sheet1").Select Selection.AutoFilter Field:=2, Criteria1:=em With ActiveSheet.AutoFilter.range On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" Else Set rng = ActiveSheet.AutoFilter.range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").range("B2") End If ActiveSheet.ShowAllData Sheets("Rep Page").Select End If Next End Sub The line: On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 Is not working. Can you help me. There is data that is filtered but it wont copy, it just goes to: If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set rng1 not working
David,
Try: rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").Range(C.Address).Offset(0, 1) Although you are switching from rng1 to rng - not sure what exactly you want to do... HTH, Bernie MS Excel MVP "David A." wrote in message ... That worked. One more thing, How do I get it to paste in the cell next to the "em" value? "Bernie Deitrick" wrote: David, Worksheets doesn't have an offset property: Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) Try just this, since you use the With construction: Set rng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) HTH, Bernie MS Excel MVP "David A." wrote in message ... I have: Sub RepStats() On Error Resume Next Dim rng1 As range Set myrng = Worksheets("Rep Page").range("A:A") For Each c In myrng If c < "" Then Sheets("Rep Page").Select em = c.Value Sheets("Sheet1").Select Selection.AutoFilter Field:=2, Criteria1:=em With ActiveSheet.AutoFilter.range On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" Else Set rng = ActiveSheet.AutoFilter.range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").range("B2") End If ActiveSheet.ShowAllData Sheets("Rep Page").Select End If Next End Sub The line: On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 Is not working. Can you help me. There is data that is filtered but it wont copy, it just goes to: If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Set rng1 not working
THANK YOU!!!!!! Man I was killing myself trying to figure that out......
"Bernie Deitrick" wrote: David, Try: rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").Range(C.Address).Offset(0, 1) Although you are switching from rng1 to rng - not sure what exactly you want to do... HTH, Bernie MS Excel MVP "David A." wrote in message ... That worked. One more thing, How do I get it to paste in the cell next to the "em" value? "Bernie Deitrick" wrote: David, Worksheets doesn't have an offset property: Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) Try just this, since you use the With construction: Set rng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) HTH, Bernie MS Excel MVP "David A." wrote in message ... I have: Sub RepStats() On Error Resume Next Dim rng1 As range Set myrng = Worksheets("Rep Page").range("A:A") For Each c In myrng If c < "" Then Sheets("Rep Page").Select em = c.Value Sheets("Sheet1").Select Selection.AutoFilter Field:=2, Criteria1:=em With ActiveSheet.AutoFilter.range On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 End With If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" Else Set rng = ActiveSheet.AutoFilter.range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ Destination:=Worksheets("Rep Page").range("B2") End If ActiveSheet.ShowAllData Sheets("Rep Page").Select End If Next End Sub The line: On Error Resume Next Set rng1 = Worksheets("Sheet1").Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible) On Error GoTo 0 Is not working. Can you help me. There is data that is filtered but it wont copy, it just goes to: If rng1 Is Nothing Then MsgBox em + " **No Data Found For This Rep**" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF not working | Excel Worksheet Functions | |||
working | Excel Worksheet Functions | |||
Why is this not working? | Excel Discussion (Misc queries) | |||
F4 Key not working.... | Excel Discussion (Misc queries) | |||
Working out UK tax | New Users to Excel |