Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
Hello -
I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 ** copy/paste_values the inserted formulas Can you please help me build this code? Thanks! Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
Hi Bob -
Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
Ok, try this
Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 = "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hi Bob - Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
Hi Bob -
I entered the code as written below and when I try to run it, I get: Run-time error '1004': Application-defined or object-defined error Ideas? thanks, ray Bob Phillips wrote: Ok, try this Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 = "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hi Bob - Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
As follow-up, I checked the worksheet and most of the code seems to
have worked - the two columns are deleted and the correct formula has been entered and copied down to the last existing cell. The formula was not copied down one add'l row, nor were the other columns totaled down. rgds, ray wrote: Hi Bob - I entered the code as written below and when I try to run it, I get: Run-time error '1004': Application-defined or object-defined error Ideas? thanks, ray Bob Phillips wrote: Ok, try this Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 = "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hi Bob - Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
I mis-interpreted that bit
Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 2).FormulaR1C1 = _ "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol - 3).Resize(, 2).FormulaR1C1 = _ "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... As follow-up, I checked the worksheet and most of the code seems to have worked - the two columns are deleted and the correct formula has been entered and copied down to the last existing cell. The formula was not copied down one add'l row, nor were the other columns totaled down. rgds, ray wrote: Hi Bob - I entered the code as written below and when I try to run it, I get: Run-time error '1004': Application-defined or object-defined error Ideas? thanks, ray Bob Phillips wrote: Ok, try this Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 = "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hi Bob - Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Last_column and autofill ...help!
Perfect! Thanks very much for your help....
//ray Bob Phillips wrote: I mis-interpreted that bit Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 2).FormulaR1C1 = _ "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol - 3).Resize(, 2).FormulaR1C1 = _ "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... As follow-up, I checked the worksheet and most of the code seems to have worked - the two columns are deleted and the correct formula has been entered and copied down to the last existing cell. The formula was not copied down one add'l row, nor were the other columns totaled down. rgds, ray wrote: Hi Bob - I entered the code as written below and when I try to run it, I get: Run-time error '1004': Application-defined or object-defined error Ideas? thanks, ray Bob Phillips wrote: Ok, try this Sub LastColAutofill() Dim iLastRow As Long Dim iLastCol As Long iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column If iLastCol 2 Then Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete ElseIf iLastCol 1 Then Columns(iLastCol).Offset(0, -2).Delete End If iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 = "=RC[-3]/RC[-4]" Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ray" wrote in message ... Hi Bob - Formula will be: "=RC[-3]/RC[-4]" And I need a Total of ALL values in Column 6 and a total of ALL values in Column 7. Then, the formula (above) copied down (in the far-right column) will use these totals. Is that fairly clear? Sorry for confusion... //ray "Bob Phillips" wrote: wrote in message ups.com... Hello - I've been searching this forum for help and while I've found bits/pieces of useful code, I'm not sure how to put it all together. Here's what I need to do: [Excel2002, windows XP] ** find last column with data ** delete two columns, one column from the end - Ex if sheet has 10columns, delete #8 & 9 ** in the first blank column, insert fomula (starting 4th row down) and autofill to last row AND THEN one more row What formula? ** then total the values in two columns - Ex if sheet has 10col, total #6 & 7 Why 6 & 7, why not 11? ** copy/paste_values the inserted formulas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: last_column & fill-down help | Excel Discussion (Misc queries) | |||
Last_column & autofill help.... | Excel Programming | |||
Autofill | Excel Worksheet Functions | |||
Autofill: Need to autofill one week block, (5) weekday only into cells. | Excel Discussion (Misc queries) | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming |