Macro Formula Reproduction

[bodhisatvaofboogie]

Here is what I am using:

Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC4/R51C4"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E49")

That selects E2, applies the formula and autofills down to the last cell
with values in it which happens to be row 49. NOW, how do I code it to do
that regardless of how many rows are in the imported data. Because some data
will be 60 rows long, others will be 50, etc. That formula selects
specifically that amount and I want it to just select the whole column
regardless of amount of rows. Make sense?

So I'm assuming some kind of change to:

Selection.AutoFill Destination:=Range("E2:E49")

or am I way off???

THANKS!!!

 

[Dave Peterson]

You really don't want the whole column--from E2:E65536, do you?

Can you pick out a column that will indicate where you want to stop.

I used column A in this sample:

dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("e2:E" & lastrow).formula = "=d2/d$51"
'or
.range("e2:e" & lastrow).formular1c1 = "=rc4/rc51c4"
end with

 

[Harald Staff]

Not way off. Try

Range("E2").AutoFill Destination:=Range("E2:E" & Cells(Rows.Count,
4).End(xlUp).Row)

HTH. Best wishes Harald

 

[bodhisatvaofboogie]

That works great Dave, NOW the only thing is:

The Row 51 in the formula, that will be in differing spots from data to
data. That is a subtotal that is placed in the last row +2 using this
formula:

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Cells(LastRow + 2, "D") = Application.Sum _
(Range(Cells(1, "D"), Cells(LastRow, "D")))

So the entire column is then subtotaled into an empty cell located in a cell
two cells below the last row cell. So how do I combine the two things, so
that the 51 in the formula you provided for me will in fact be the lastrow +2
space. That way when I import varying data sets, it's not stuck at row 51
for the subtotal. Make sense???

THANKS!!!

 

[bodhisatvaofboogie]

I'm trying to work in what you gave me, and failing miserably. SO, walk me
through where to plug in what ya gave me please THANKS!!!

 

[Dave Peterson]

maybe...

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Cells(LastRow + 2, "D") = Application.Sum _
(Range(Cells(1, "D"), Cells(LastRow, "D")))
range("e2:E" & lastrow).formula = "=d2/d$" & lastrow

I'm not sure I understand, though.

 

[bodhisatvaofboogie]

WOW....that is SOOOO close. it worked as far as the formula is concerned.
I got a couple extra things and I would like to not have them happen.
Lemme provide an example:

ColumnD ColumnE
1234 %60
1234 %40
1234 %30
1234 %20
1234 %10
%0 <---- this is extra
SUBTOTAL %100 <--- This is extra

SUBTOTAL2 <----This is extra

So it actually DID what it was supposed to, but added those two extra
percents and a second subtotal. Does that make sense? AND how do I fix it??
THANKS!!!

 

[bodhisatvaofboogie]

Wait, I got that formula to work nicely... BUT, it is creating some funny
stuff. The percents are coming out odd....instead of 22.0581 it is comign
out -2205.81%
What's up with that?

 

[Dave Peterson]

It kind of scares me that you're using column D to find the lastrow.

If you run the macro once, the lastrow will be based on the raw data.

But if you run it again, then the lastrow will be the one that adds that
subtotal (lastrow + 2), so you'll have two subtotals.

Run it again and you'll get more.

Maybe, just maybe, if you have no gaps in your data, you should start at the top
and work down:

LastRow = Cells(Rows.Count, "D").End(xldown).row

Then your macro will overwrite your existing subtotals.

======
Or just use a different column to determine that lastrow. Do you have another
field that is always filled in when you have data on that row?

WOW....that is SOOOO close. it worked as far as the formula is concerned.
I got a couple extra things and I would like to not have them happen.
Lemme provide an example:

ColumnD ColumnE
1234 %60
1234 %40
1234 %30
1234 %20
1234 %10
%0 <---- this is extra
SUBTOTAL %100 <--- This is extra

SUBTOTAL2 <----This is extra

So it actually DID what it was supposed to, but added those two extra
percents and a second subtotal. Does that make sense? AND how do I fix it??
THANKS!!!

 

[Dave Peterson]

Those values are exactly the same.

1 = 100%
22.0581 = 2205.81%

Maybe you want to divide by 100 yourself???

range("e2:E" & lastrow).formula = "=d2/100/d$" & lastrow
or
range("e2:E" & lastrow).formula = "=d2/d$" & lastrow & "/100"

Wait, I got that formula to work nicely... BUT, it is creating some funny
stuff. The percents are coming out odd....instead of 22.0581 it is comign
out -2205.81%
What's up with that?

 

[bodhisatvaofboogie]

It's all workin....SMOOTH!!! THANKS!!!!!!

maybe...

LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Cells(LastRow + 2, "D") = Application.Sum _
(Range(Cells(1, "D"), Cells(LastRow, "D")))
range("e2:E" & lastrow).formula = "=d2/d$" & lastrow

I'm not sure I understand, though.