Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
Need help with coding that would look at cells within columns that are
colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
Here is a macro that does what you wanted I think. You must start the macro
with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
Keith, thank you so much for taking the time to help me! I ran the macro but
rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
When you get the error, click on Debug, and hover your cursor over MyRow in
the code, and let me know what value is shown for it. That will help me figure out what the problem is. Keith "JCarter" wrote: Keith, thank you so much for taking the time to help me! I ran the macro but rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
it show "MyRow = (and then it's shows the link to my feeder file)
All my shaded cells are links to other files/worksheets outside this particular file. -- JCarter Still Learning "Keithlo" wrote: When you get the error, click on Debug, and hover your cursor over MyRow in the code, and let me know what value is shown for it. That will help me figure out what the problem is. Keith "JCarter" wrote: Keith, thank you so much for taking the time to help me! I ran the macro but rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
I'm sorry, my code was weak. It used absolute values to determine the row
portion. This new code figures out the row number based on the dollar sign. There is still one weakness. If your formulas have no $ signs in them it will fail. But since they are all referencing another workbook, they would have dollar signs unless they were removed after creating the reference. There's probably a better way to do this, but it will work if the dollar signs are there. I'm assuming all your shaded cells have a formula referencing a different workbook. Try this and see how it works. Sub Shading_Test() Dim MyRow, MyChar, MyPlace Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyPlace = Len(ActiveCell.Formula) MyChar = Mid(ActiveCell.Formula, MyPlace, 1) Do While MyPlace 1 If MyChar = "$" Then MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) - MyPlace) Exit Do Else MyPlace = MyPlace - 1 MyChar = Mid(ActiveCell.Formula, MyPlace, 1) End If Loop MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Keith "JCarter" wrote: it show "MyRow = (and then it's shows the link to my feeder file) All my shaded cells are links to other files/worksheets outside this particular file. -- JCarter Still Learning "Keithlo" wrote: When you get the error, click on Debug, and hover your cursor over MyRow in the code, and let me know what value is shown for it. That will help me figure out what the problem is. Keith "JCarter" wrote: Keith, thank you so much for taking the time to help me! I ran the macro but rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
You are a GENIUS!!!!!!! It works like a charm! Thank you thank you thank
you!!!! -- JCarter ALWAYS Learning "Keithlo" wrote: I'm sorry, my code was weak. It used absolute values to determine the row portion. This new code figures out the row number based on the dollar sign. There is still one weakness. If your formulas have no $ signs in them it will fail. But since they are all referencing another workbook, they would have dollar signs unless they were removed after creating the reference. There's probably a better way to do this, but it will work if the dollar signs are there. I'm assuming all your shaded cells have a formula referencing a different workbook. Try this and see how it works. Sub Shading_Test() Dim MyRow, MyChar, MyPlace Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyPlace = Len(ActiveCell.Formula) MyChar = Mid(ActiveCell.Formula, MyPlace, 1) Do While MyPlace 1 If MyChar = "$" Then MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) - MyPlace) Exit Do Else MyPlace = MyPlace - 1 MyChar = Mid(ActiveCell.Formula, MyPlace, 1) End If Loop MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Keith "JCarter" wrote: it show "MyRow = (and then it's shows the link to my feeder file) All my shaded cells are links to other files/worksheets outside this particular file. -- JCarter Still Learning "Keithlo" wrote: When you get the error, click on Debug, and hover your cursor over MyRow in the code, and let me know what value is shown for it. That will help me figure out what the problem is. Keith "JCarter" wrote: Keith, thank you so much for taking the time to help me! I ran the macro but rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding needed for shaded cells
You're welcome! I'm glad it worked.
I did figure out a better way to determine the row of a formula by using the FormulaR1C1 property of the active cell, but it would take some time to code. So since this worked for you, I'll just tuck that away for future reference. Keith "JCarter" wrote: You are a GENIUS!!!!!!! It works like a charm! Thank you thank you thank you!!!! -- JCarter ALWAYS Learning "Keithlo" wrote: I'm sorry, my code was weak. It used absolute values to determine the row portion. This new code figures out the row number based on the dollar sign. There is still one weakness. If your formulas have no $ signs in them it will fail. But since they are all referencing another workbook, they would have dollar signs unless they were removed after creating the reference. There's probably a better way to do this, but it will work if the dollar signs are there. I'm assuming all your shaded cells have a formula referencing a different workbook. Try this and see how it works. Sub Shading_Test() Dim MyRow, MyChar, MyPlace Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyPlace = Len(ActiveCell.Formula) MyChar = Mid(ActiveCell.Formula, MyPlace, 1) Do While MyPlace 1 If MyChar = "$" Then MyRow = Mid(ActiveCell.Formula, MyPlace + 1, Len(ActiveCell.Formula) - MyPlace) Exit Do Else MyPlace = MyPlace - 1 MyChar = Mid(ActiveCell.Formula, MyPlace, 1) End If Loop MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, MyPlace) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Keith "JCarter" wrote: it show "MyRow = (and then it's shows the link to my feeder file) All my shaded cells are links to other files/worksheets outside this particular file. -- JCarter Still Learning "Keithlo" wrote: When you get the error, click on Debug, and hover your cursor over MyRow in the code, and let me know what value is shown for it. That will help me figure out what the problem is. Keith "JCarter" wrote: Keith, thank you so much for taking the time to help me! I ran the macro but rec'd an error stating "Run Time Error 13" Type Mismatch. It seems to be looking at the "MyRow = MyRow +1" How would I correct this? -- JCarter Still Learning "Keithlo" wrote: Here is a macro that does what you wanted I think. You must start the macro with the active cell being at the bottom row of your data. It then will work its way up performing the operation on all rows except row one, which I assume has a label. I'm also assuming that you want this performed on any shaded row, regardless of the color, etc. I tested this and it worked for me. Please test it on a saved file so that if you don't get the results you wanted, you can just close and not save and then re-open to get back to where you were before you ran the macro, because you can't use the undo operation to undo a macro. Sub Shading_Test() Dim MyRow Do While ActiveCell.Row 1 If ActiveCell.Interior.ColorIndex < xlNone Then MyRow = Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 11) MyRow = MyRow + 1 ActiveCell.Offset(0, 1).Formula = Left(ActiveCell.Formula, 11) & MyRow End If ActiveCell.Offset(-1, 0).Select Loop End Sub Hope this helps. Keith "JCarter" wrote: Need help with coding that would look at cells within columns that are colored (shaded) and automatically copy/paste to the next column over and chg the links to the next row # in the link For example: Column Z;Row 4 is a shaded cell, that has the link formula referencing another files, "Sheet1!$D$60". The coding I want would look at that link formula and copy/paste to Column AA; Row 4 but now show the link reference as "Sheet1!$D$61" I know this sounds and looks crazy, but it's really what I need. For the code to look for the shaded cell, and increase the row by 1 after it copies/paste the formula in the next column. ANY and all guidance is greatly appreciated! :o) -- JCarter Still Learning |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shaded cells won't print shaded | Excel Worksheet Functions | |||
Coding assistance needed. | Excel Programming | |||
Macro coding needed. | Excel Programming | |||
Macro coding help needed | Excel Programming | |||
Coding help needed | Excel Programming |