Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
I have posted this 2ce but I think I did a very bad job of explaining what I
was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
Here's a macro that calls out another macro. Change the Application.Run line
as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
WOW! Wonderfully brilliant!! :).. This seems to work perfectly! Now, if I
could impose further? I need to delete Rows 2 and 3 AFTER the first macro has run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3 I don't need.. and I would like the data that comes in from the formulas in this macro to have the same formatting (borders, font, etc) as the rest of the sheet.. Is this possible??? "Luke M" wrote: Here's a macro that calls out another macro. Change the Application.Run line as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
After the Application.Run command, add this line of coding:
Range("2:3").EntireRow.Delete As for the formatting, that's a little more tricky (you'd have to define each parameter individually, or try and copy the formatting from somewhere else). I would suggest that you record a short macro of you formatting the cells (I:L) the way you want, with borders, font, etc. Then in the VBE, copy that part of the macro over to the end of the macro I gave you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: WOW! Wonderfully brilliant!! :).. This seems to work perfectly! Now, if I could impose further? I need to delete Rows 2 and 3 AFTER the first macro has run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3 I don't need.. and I would like the data that comes in from the formulas in this macro to have the same formatting (borders, font, etc) as the rest of the sheet.. Is this possible??? "Luke M" wrote: Here's a macro that calls out another macro. Change the Application.Run line as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
Once again.. WONDERFULLY BRILLIANT! That works perfectly for the deleting of
the 1st 2 rows.. as to the formatting, I had already tried what you suggested except I don't know how to get it to only copy formats to what is being populated and that changes based on how much data is there everytime you run the macro.. for now, the code looks like this - see my comments within the code.. Also, I tried to find the proper structure of the routine/command to make sure it fit correctly within the macro but wasn't sure if this was ok or if other commands were needed.. and exactly where in the macro does it go? Range("H3").Select Selection.Copy Range("I2:L53").Select EXCEPT THIS LINE i WOULD LIKE TO HAVE IT ONLY POST THE FORMAT TO THE ACTUAL AREAS THAT HAVE DATA IN THEM.. THIS RANGE WILL VARY EVERYTIME THE MACRO IS RUN.. Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Luke M" wrote: After the Application.Run command, add this line of coding: Range("2:3").EntireRow.Delete As for the formatting, that's a little more tricky (you'd have to define each parameter individually, or try and copy the formatting from somewhere else). I would suggest that you record a short macro of you formatting the cells (I:L) the way you want, with borders, font, etc. Then in the VBE, copy that part of the macro over to the end of the macro I gave you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: WOW! Wonderfully brilliant!! :).. This seems to work perfectly! Now, if I could impose further? I need to delete Rows 2 and 3 AFTER the first macro has run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3 I don't need.. and I would like the data that comes in from the formulas in this macro to have the same formatting (borders, font, etc) as the rest of the sheet.. Is this possible??? "Luke M" wrote: Here's a macro that calls out another macro. Change the Application.Run line as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
Here's the complete code, with row deletion and format copied to cells with
formulas: '================== Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" Range("2:3").EntireRow.Delete 'Change as appropriate Sheets("Name of Sheet").Select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next Range("H3").Copy 'Grabs only the cells in I:L that have formulas Range("I:L").SpecialCells(xlCellTypeFormulas, 23).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: Once again.. WONDERFULLY BRILLIANT! That works perfectly for the deleting of the 1st 2 rows.. as to the formatting, I had already tried what you suggested except I don't know how to get it to only copy formats to what is being populated and that changes based on how much data is there everytime you run the macro.. for now, the code looks like this - see my comments within the code.. Also, I tried to find the proper structure of the routine/command to make sure it fit correctly within the macro but wasn't sure if this was ok or if other commands were needed.. and exactly where in the macro does it go? Range("H3").Select Selection.Copy Range("I2:L53").Select EXCEPT THIS LINE i WOULD LIKE TO HAVE IT ONLY POST THE FORMAT TO THE ACTUAL AREAS THAT HAVE DATA IN THEM.. THIS RANGE WILL VARY EVERYTIME THE MACRO IS RUN.. Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Luke M" wrote: After the Application.Run command, add this line of coding: Range("2:3").EntireRow.Delete As for the formatting, that's a little more tricky (you'd have to define each parameter individually, or try and copy the formatting from somewhere else). I would suggest that you record a short macro of you formatting the cells (I:L) the way you want, with borders, font, etc. Then in the VBE, copy that part of the macro over to the end of the macro I gave you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: WOW! Wonderfully brilliant!! :).. This seems to work perfectly! Now, if I could impose further? I need to delete Rows 2 and 3 AFTER the first macro has run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3 I don't need.. and I would like the data that comes in from the formulas in this macro to have the same formatting (borders, font, etc) as the rest of the sheet.. Is this possible??? "Luke M" wrote: Here's a macro that calls out another macro. Change the Application.Run line as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO THAT BUILDS ON ANOTHER MACRO'S DATA
WOW!!! YOU ARE AMAZING!! THANK YOU! It works perfectly!!!!!!!!!
THANK YOU! "Luke M" wrote: Here's the complete code, with row deletion and format copied to cells with formulas: '================== Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" Range("2:3").EntireRow.Delete 'Change as appropriate Sheets("Name of Sheet").Select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next Range("H3").Copy 'Grabs only the cells in I:L that have formulas Range("I:L").SpecialCells(xlCellTypeFormulas, 23).Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Sub '================= -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: Once again.. WONDERFULLY BRILLIANT! That works perfectly for the deleting of the 1st 2 rows.. as to the formatting, I had already tried what you suggested except I don't know how to get it to only copy formats to what is being populated and that changes based on how much data is there everytime you run the macro.. for now, the code looks like this - see my comments within the code.. Also, I tried to find the proper structure of the routine/command to make sure it fit correctly within the macro but wasn't sure if this was ok or if other commands were needed.. and exactly where in the macro does it go? Range("H3").Select Selection.Copy Range("I2:L53").Select EXCEPT THIS LINE i WOULD LIKE TO HAVE IT ONLY POST THE FORMAT TO THE ACTUAL AREAS THAT HAVE DATA IN THEM.. THIS RANGE WILL VARY EVERYTIME THE MACRO IS RUN.. Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False "Luke M" wrote: After the Application.Run command, add this line of coding: Range("2:3").EntireRow.Delete As for the formatting, that's a little more tricky (you'd have to define each parameter individually, or try and copy the formatting from somewhere else). I would suggest that you record a short macro of you formatting the cells (I:L) the way you want, with borders, font, etc. Then in the VBE, copy that part of the macro over to the end of the macro I gave you. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: WOW! Wonderfully brilliant!! :).. This seems to work perfectly! Now, if I could impose further? I need to delete Rows 2 and 3 AFTER the first macro has run b/c for some reason it puts in 3 header rows and the ones on Rows 2 and 3 I don't need.. and I would like the data that comes in from the formulas in this macro to have the same formatting (borders, font, etc) as the rest of the sheet.. Is this possible??? "Luke M" wrote: Here's a macro that calls out another macro. Change the Application.Run line as appropriate to match the name of your workbook and macro the Ron gave you. You may also need to change the name of the sheet to be selected. Sub AddFormulas() 'Change this line as appropriate Application.Run "YourWorkbook!NameOfOtherMacro" 'Change as appropriate Sheets("Name of Sheet").select For Each Cell In Range("C:C") x = Cell.Row 'Checks to make sure past header rows 'and that cell in column C is not empty If x < 2 Or IsEmpty(Cell) Then 'do nothing Else 'Create your formulas Cells(x, "I").Formula = "=D" & x & "+E" & x & "-F" & x Cells(x, "J").Formula = "=D" & x & "+E" & x & "-H" & x Cells(x, "K").Formula = "=G" & x Cells(x, "L").Formula = "=K" & x & "-K" & x End If Next End Sub -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Tree" wrote: I have posted this 2ce but I think I did a very bad job of explaining what I was looking for.. Hopefully I have done a better job now as I really need the assistance! Ron Bruin helped with a macro that combines data from several tabs into one sheet and that macro works great.. I want to create a macro that runs Ron's macro and then with that resulting data, does the following: (Row 1 contains headers from Ron's macro and I can't find how to have the Resulting Data Macro "Start"? on Row 2-also, Ron's macro seems to create 3 total header rows and I have to delete 2 of them in the "Resulting Data " macro); Columns A-H contain the data from the combo macro; Columns I:L would contain formulas (each column has a unique formula and uses the data from Columns D-H... BUT they only run IF Column C has text.. The formulas are as follows: Column I has Column D + Column E - Column F.. Column J has Column D + Column E - Column H Column K = Column G. Column L has Column J - Column K. And these formulas need to insert themselves in the respective rows in Columns I-L as long as there is text in Column C from the already run combo macro.. I appreciate your assistance!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MACRO FUNCTION conflict with other macro's | Excel Discussion (Misc queries) | |||
Does anyone know of a service/company that builds macros? | Excel Discussion (Misc queries) | |||
Hide Macro's in Toolbar / Macro's list | Excel Discussion (Misc queries) | |||
How do I change the order of how excel builds a chart? | Charts and Charting in Excel | |||
automated macro's? and macro limitation to a worksheet | Excel Programming |