Thanks for the code SA, and thanks, DP, for helping me to notice what I
should have noticed before I submitted my second post. Both macros are
working great!!
Regards,
Ryan---
--
RyGuy
"Dave Peterson" wrote:
> You could use:
>
> If rr.Text = "#N/A" Then
>
> or to test for any error in that cell:
>
> If iserror(rr.Value) Then
>
>
>
> ryguy7272 wrote:
> >
> > For both macros i get:
> > I get a run time error '13'
> > Type Mismatch
> >
> > Also, I need to find the used cell in Column A, because this list will
> > shrink grow each time data is pulled to create a report.
> >
> > Any other ideas?
> >
> > Thanks,
> > Ryan---
> >
> > --
> > RyGuy
> >
> > "StumpedAgain" wrote:
> >
> > > Try something like the following. I'm unsure why you did
> > > "rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A".
> > > Maybe I'm missing something. Also, I added a line to insert the above cell
> > > value into the newly cleared cell.
> > >
> > > Sub ClearRange()
> > > Dim rr, r As Range
> > > Set r = Range("A2:A500")
> > > For Each rr In r
> > > If rr.Value = "#N/A" Then
> > > rr.Clear
> > > rr.value = rr.offset(-1,0).value
> > > End If
> > > Next rr
> > > End Sub
> > >
> > > If you're looking to fill more blank cells, something like the following
> > > should work.
> > >
> > > Sub FillBlanks()
> > > Dim rr, r As Range
> > > Set r = Range("A2:A500")
> > > For Each rr In r
> > > If rr.Value = "" Then
> > > rr.value = rr.offset(-1,0).value
> > > End If
> > > Next rr
> > > End Sub
> > >
> > > Hope this helps!
> > > --
> > > -SA
> > >
> > >
> > > "ryguy7272" wrote:
> > >
> > > > Simple problem seems to be turning into a complex problem. I want to clear
> > > > all cells that show #N/A.
> > > >
> > > > Sub ClearRange()
> > > > Dim r As Range
> > > > Set r = Range("A2:A500")
> > > > For Each rr In r
> > > > If rr.Value = "#N/A" Then
> > > > rr.Offset(0, 1).Clear
> > > > End If
> > > > Next
> > > > End Sub
> > > >
> > > > As an aside, I was trying to fill all blank cells with values from above
> > > > cells. It is easy to find the end of the range, but Excel seems to lose the
> > > > address of the last cell in the used range once I tell it to go to the first
> > > > cell in the used range (in my case it is A5)
> > > >
> > > > Sub FindEnd()
> > > >
> > > > Dim Rng As Range
> > > > Cells(Rows.Count, "A").End(xlUp).Select
> > > > ActiveCell.Select
> > > > Rng = ActiveCell
> > > >
> > > > Selection.SpecialCells(xlCellTypeBlanks).Select
> > > > Range("A5:Rng").Select
> > > > Selection.FormulaR1C1 = "=R[-1]C"
> > > >
> > > > End Sub
> > > >
> > > >
> > > > Any help with either macro would be greatly appreciated!!
> > > >
> > > > Regards,
> > > > Ryan---
> > > >
> > > > --
> > > > RyGuy
>
> --
>
> Dave Peterson
>
|
Similar Threads
