Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move values
What can be wrong?
Sub copy() Dim rng As Range, cell As Range Dim rw As Long Dim cLastRow As Long On Error Resume Next Set rng = Range("G3:G4510").SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 rw = 0 If Not rng Is Nothing Then For Each cell In rng If cell.Text = "#N/A" Then rw = rw + 1 End If Sheets("Sheet1").Select cLastRow = Cells(Rows.Count, "C").End(xlUp).Row Range("C" & cLastRow).Offset(1, 0).Select Sheets("Sheet1").Cells(rw, "C").Value = Sheets("Sheet2").Cells(cell.Row, "A").Value Next End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move values
I don't see a reason for this portion:
Sheets("Sheet1").Select cLastRow = Cells(Rows.Count, "C").End(xlUp).Row Range("C" & cLastRow).Offset(1, 0).Select (I'd comment it out and see if made any difference.) And you only add to the rw variable when you have an error that evaluates to #N/A. What about #value!, #ref!, etc? But I'm not sure what you're really asking for. You may want to be more explicit with what you want and what isn't working. Geir wrote: What can be wrong? Sub copy() Dim rng As Range, cell As Range Dim rw As Long Dim cLastRow As Long On Error Resume Next Set rng = Range("G3:G4510").SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 rw = 0 If Not rng Is Nothing Then For Each cell In rng If cell.Text = "#N/A" Then rw = rw + 1 End If Sheets("Sheet1").Select cLastRow = Cells(Rows.Count, "C").End(xlUp).Row Range("C" & cLastRow).Offset(1, 0).Select Sheets("Sheet1").Cells(rw, "C").Value = Sheets("Sheet2").Cells(cell.Row, "A").Value Next End If End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move Y axis values from right to left | Charts and Charting in Excel | |||
find and move values <0 | Excel Worksheet Functions | |||
Move the Y axis below negative values | Charts and Charting in Excel | |||
Move column values w/o formula | Excel Worksheet Functions | |||
identify and move specific values | Excel Programming |