I am getting a "Method Range of Object Global Failed with this code. What am
I doing wrong?

Dim MyRangeA As Range
Dim LastRowA As Long

Range("J14").Select
LastRowA = Cells(Cells.Rows.Count, "J").End(xlUp).Row
Set MyRangeA = Range("J" & ActiveCell.Row & ":J" & LastRowA - 4)
ActiveCell.FormulaR1C1 = "=MID(R3C2,2,10)"
Range("j14").AutoFill Range("MyRangeA")

End Sub

I didn't test your code and you didn't tell us which line failed, but I'm
guessing it was the last line. Just looking quickly... MyRangeA was declared
as a Range variable and you Set a range into it, so why are you encasing
that Range variable name within quote marks and a encasing that within a
Range call? Wouldn't this be what you want...

Range("j14").AutoFill MyRangeA

since MyRangeA is already a Range?

--
Rick (MVP - Excel)



"Thanks" <(E-Mail Removed)> wrote in message
news:A96FC65D-1F28-4936-B35D-(E-Mail Removed)...
> I am getting a "Method Range of Object Global Failed with this code. What
> am
> I doing wrong?
>
> Dim MyRangeA As Range
> Dim LastRowA As Long
>
> Range("J14").Select
> LastRowA = Cells(Cells.Rows.Count, "J").End(xlUp).Row
> Set MyRangeA = Range("J" & ActiveCell.Row & ":J" & LastRowA - 4)
> ActiveCell.FormulaR1C1 = "=MID(R3C2,2,10)"
> Range("j14").AutoFill Range("MyRangeA")
>
> End Sub

Yes thats it


"Rick Rothstein" wrote:

> I didn't test your code and you didn't tell us which line failed, but I'm
> guessing it was the last line. Just looking quickly... MyRangeA was declared
> as a Range variable and you Set a range into it, so why are you encasing
> that Range variable name within quote marks and a encasing that within a
> Range call? Wouldn't this be what you want...
>
> Range("j14").AutoFill MyRangeA
>
> since MyRangeA is already a Range?
>
> --
> Rick (MVP - Excel)
>
>
>
> "Thanks" <(E-Mail Removed)> wrote in message
> news:A96FC65D-1F28-4936-B35D-(E-Mail Removed)...
> > I am getting a "Method Range of Object Global Failed with this code. What
> > am
> > I doing wrong?
> >
> > Dim MyRangeA As Range
> > Dim LastRowA As Long
> >
> > Range("J14").Select
> > LastRowA = Cells(Cells.Rows.Count, "J").End(xlUp).Row
> > Set MyRangeA = Range("J" & ActiveCell.Row & ":J" & LastRowA - 4)
> > ActiveCell.FormulaR1C1 = "=MID(R3C2,2,10)"
> > Range("j14").AutoFill Range("MyRangeA")
> >
> > End Sub
>
> .
>