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!!!

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



bodhisatvaofboogie wrote:

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

Not way off. Try

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

HTH. Best wishes Harald

"bodhisatvaofboogie" skrev i
melding ...
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!!!

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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

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!!!

"Harald Staff" wrote:

Not way off. Try

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

HTH. Best wishes Harald

"bodhisatvaofboogie" skrev i
melding ...
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!!!

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

Dave Peterson

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" wrote:

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

Dave Peterson

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" wrote:

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

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?

bodhisatvaofboogie wrote:

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" wrote:

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

Dave Peterson


--

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"

bodhisatvaofboogie wrote:

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" wrote:

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

Dave Peterson


--

Dave Peterson

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

"Dave Peterson" wrote:

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 wrote:

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!!!

"Dave Peterson" wrote:

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



bodhisatvaofboogie wrote:

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


--

Dave Peterson