HI Mike.......
Well, many thanks for your suggestions.......combining both of yours, and
tweaking a little for my application, the thing works
beautifully.......You've taught me much neat stuff.........
Here's the working version:
Sub InsertRanks()
Dim LRowA As Long, LRowR As Long
Dim rng As Range, c As Range
LRowA = Cells(Rows.Count, "A").End(xlUp).Row
LRowR = Cells(Rows.Count, "R").End(xlUp).Row
Set rng = Range("R" & LRowR + 1 & ":R" & LRowA)
For Each c In rng
Range("R1").Copy
c.Select
ActiveSheet.Paste
c.Value = c.Value
Range("s1").Copy
c.Offset(, 1).Select
ActiveSheet.Paste
c.Offset(, 1).Value = c.Offset(, 1).Value
Next
End Sub
Of course R1 and S1 contain the big formulas.........
Again, thank you most kindly for helping me through this.......
Vaya conDios,
Chuck, CABGx3
"Mike Fogleman" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> OK, I guess I'll give you what you asked for:
> This will filldown column R
>
> Sub test()
> Dim LRowA As Long, LRowR As Long
> Dim rng As Range, c As Range
>
> LRowA = Cells(Rows.Count, "A").End(xlUp).Row
> LRowR = Cells(Rows.Count, "R").End(xlUp).Row
>
> Set rng = Range("R" & LRowR + 1 & ":R" & LRowA)
> Range("R" & LRowR + 1).Formula =
>
"=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$
4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4))
),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+""})),""NoData"",LO
OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4)*$J$12:$j$1
0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4))),{0,61,7
1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+""}))"
>
> rng.FillDown
> rng.Value = rng.Value
>
> End Sub
>
> Mike F
> "CLR" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hey Mike.........thanks much for the comeback.
> > Your code works super fine with a simple formula like you show, but when
I
> > try to modify it to use my compound real formula I get no joy.........
> >
> > Here's my formula, which worked ok when macroed into cell R2 and copied
> > down.........
> > .Value =
> >
"=IF(ISERR(LOOKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$
> >
4)*J2:j10000))/(SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4))
>
> ),{0,61,71,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+""})),""NoData"",
LO
> >
OKUP((SUMPRODUCT(($A$12:$A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4)*$J$12:$j$1
> >
0000))/(SUMPRODUcT(($A$12:A$10000=$A2)*($q$12:$q$10000>=DATA!$K$4))),{0,61,7
> > 1,81,91,101},{""F"",""D"",""C"",""B"",""A"",""A+""}))"
> >
> > it fails in that context, to the error "NoData"...... the$A2 reference
is
> > wrong for that row, ........
> >
> > so I tried to modify it by replacing the $A2 with c.offset(, -17)
because
> > I
> > am putting the formula actually in column R......and still want to refer
> > to
> > the value in column A of that row........this failed also to the error
> > "NoData".........and in both cases, the macro actually put the formula
in
> > the cells rather than the values of their calculations.
> >
> > Obviously this is over my head.....please help
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> > "Mike Fogleman" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> No need to autofill and paste special, just assign values to the cells:
> >>
> >> Sub test()
> >> Dim LRowA As Long, LRowB As Long
> >> Dim rng As Range, c As Range
> >>
> >> LRowA = Cells(Rows.Count, "A").End(xlUp).Row
> >> LRowB = Cells(Rows.Count, "B").End(xlUp).Row
> >>
> >> Set rng = Range("B" & LRowB + 1 & ":B" & LRowA)
> >>
> >> For Each c In rng
> >> 'puts a value in column B
> >> 'that is twice the value of column A
> >> c.Value = c.Offset(, -1) * 2
> >> ' puts a value in column C
> >> ' that is 1 greater than column B
> >> c.Offset(, 1).Value = c.Value + 1
> >> Next
> >> End Sub
> >>
> >> Mike F
> >> "CLR" <(E-Mail Removed)> wrote in message
> >> news:uG37M$(E-Mail Removed)...
> >> > Hi All.......
> >> > I have three columns, column A is about 3500 rows, (exact quantity
> >> > unknown),
> >> > columns B and C have the same quantity of rows each but are a few
> > hundred
> >> > less than column A, again (exact quantity unkown) I would like to
find
> >> > the
> >> > lower points of cols B and C and insert a separate fomula into each
> > column
> >> > and autofill both formulas down as far as there is data in column A,
> > then
> >> > Copy > PasteSpecial > Values to get rid of the formulas.....thus
giving
> > me
> >> > 3
> >> > columns of data of all the same length.
> >> >
> >> > Any help would be appreciated.
> >> >
> >> > Vaya con Dios,
> >> > Chuck, CABGx3
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>
Similar Threads
