| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Hi Paul,
thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps |
|
#2
|
|||
|
|||
|
sorry kevin i cant help.I did a wee bit more research and from what i have
seen custom functions do not extend to formatting.Custom funcions are a kind of macro. My only suggestion is either wait for gordon to respond or email him and tell him that you will make a new post in progamming.In that post ask if a custom function will do the job of copying or linking formats,or if a macro is required.Are your pin diagrams always the same size or do they vary ?ie one pin will be 2r x 5c and another 2r x 10c and even bigger??? -- paul remove nospam for email addy! "kevinm" wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage |
|
#3
|
|||
|
|||
|
Kevin
You've not been forgotten. Been busy but will try to get you something in the next day or so. Monday I'm starting a Consulting contract and will be out of town for most of every week until end of September so I better get you something before then. Gord On Wed, 11 May 2005 13:06:11 -0700, kevinm wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. On this Toolbar is a "relative reference" toggle button. Record your macro with relative references turned on. Gord Dibben Excel MVP On Sun, 8 May 2005 09:00:01 -0700, kevinm wrote: Hi Paul, thanks for your suggestion. however I have tried creating a macro but couldn't get it to do what I want. I had problems in that I could only manage to get Excel to create a macro that used absolute cell refereences, so when I went to a completely different cell and ran the macro it copied from the same source cell every time. Somehow I need to get Excel to build a macro with relative cell references, then it might work. I wil read up on macros again and try to figure it out, Kevin "paul" wrote: i think you will need a simple macro to do this ie paste special-formats,paste special value -- hope this helps |
|
#4
|
|||
|
|||
|
Gord,
no worries, a few days isn't going to cause me any problem. Besides, when we have this working it will probably save me days of work and pain anyway, Kevin "Gord Dibben" wrote: Kevin You've not been forgotten. Been busy but will try to get you something in the next day or so. Monday I'm starting a Consulting contract and will be out of town for most of every week until end of September so I better get you something before then. Gord On Wed, 11 May 2005 13:06:11 -0700, kevinm wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, Kevin "Gord Dibben" wrote: Kevin When you go to ToolsMacroRecord New Macro and OK, the Stop Recording Toolbar should pop up. |
|
#5
|
|||
|
|||
|
Kevin/Gordon this
http://www.microsoft.com/office/comm...1-2c0acdd69cbc thread lead me to this http://www.mvps.org/dmcritchie/excel/colors.htm page,and the macro below it looks like this could be what kevin is after??? Setting Interior Color based on another Cell (#popbased) Option Explicit Global gblColorIndex As Integer Sub SetInteriorColor() gblColorIndex = ActiveCell.Interior.ColorIndex End Sub Sub PutInteriorColor() Selection.Interior.ColorIndex = gblColorIndex End Sub Sub SameInteriorAsA1() Selection.Interior.ColorIndex = [A1].Interior.ColorIndex End Sub -- paul remove nospam for email addy! "kevinm" wrote: Gord, no worries, a few days isn't going to cause me any problem. Besides, when we have this working it will probably save me days of work and pain anyway, Kevin "Gord Dibben" wrote: Kevin You've not been forgotten. Been busy but will try to get you something in the next day or so. Monday I'm starting a Consulting contract and will be out of town for most of every week until end of September so I better get you something before then. Gord On Wed, 11 May 2005 13:06:11 -0700, kevinm wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub any suggestions on how I could avoid this error would be greatly appreciated, thanks, Kevin "kevinm" wrote: Hi Gord, unfortunately I dont seem to be able to get the Stop Recording Toolbar to pop up. I am certain that it used to pop up but for some reason it is no longer doing this. I have been forced to stop the recording by going into Tools/Macro/Stop Recordiong. Do you know how I go about enabling that Stop Recording popup? thanks, |
|
#6
|
|||
|
|||
|
Paul,
thanks for the pointer. Although I don't fully understand the VB code, if the comments in the example code are anything to go by this would perform the color copying from the cell on sheet1 to a different cell on sheet2. The part I am still missing is how to read the cell contents on sheet1 and use that to determine the target cell location on sheet2. I will quote the simplified example from my earlier post .. I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc regards, Kevin "paul" wrote: Kevin/Gordon this http://www.microsoft.com/office/comm...1-2c0acdd69cbc thread lead me to this http://www.mvps.org/dmcritchie/excel/colors.htm page,and the macro below it looks like this could be what kevin is after??? Setting Interior Color based on another Cell (#popbased) Option Explicit Global gblColorIndex As Integer Sub SetInteriorColor() gblColorIndex = ActiveCell.Interior.ColorIndex End Sub Sub PutInteriorColor() Selection.Interior.ColorIndex = gblColorIndex End Sub Sub SameInteriorAsA1() Selection.Interior.ColorIndex = [A1].Interior.ColorIndex End Sub -- paul remove nospam for email addy! "kevinm" wrote: Gord, no worries, a few days isn't going to cause me any problem. Besides, when we have this working it will probably save me days of work and pain anyway, Kevin "Gord Dibben" wrote: Kevin You've not been forgotten. Been busy but will try to get you something in the next day or so. Monday I'm starting a Consulting contract and will be out of town for most of every week until end of September so I better get you something before then. Gord On Wed, 11 May 2005 13:06:11 -0700, kevinm wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then selecting a cell 8 rows above. What are your exact needs? Perhaps a simpler macro can be written. Gord Dibben Excel MVP On Mon, 9 May 2005 10:49:01 -0700, kevinm wrote: Gord, I managed to get the 'Stop Recording' popup back up, I have recorded a macro with relative references enabled BUT I get a VB error when I run it .. "Run-time error '1004': Application-defined or object-defined error." If I record a macro with relative references turned off VB doesn't give me the error. Here are the details on the macro which I recorded: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 09/05/2005 by UBV2000 ' ' ActiveCell.Offset(-9, 0).Range("A1").Select Selection.Copy ActiveCell.Offset(9, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False ActiveCell.Offset(-8, 0).Range("A1").Select End Sub |
|
#7
|
|||
|
|||
|
kevin I have a simple macro which does what you want.I am not quite sure
where to go from here tho.I can email it to you and maybe you can ask on the programming forum how you can modify it to "loop" through your ranges or pin blocks The only catch is that instead of colours they are numbers..you will see what i mean ie black is colorindex = 1,whte is colorindex = 2,etc etc -- paul remove nospam for email addy! "kevinm" wrote: Paul, thanks for the pointer. Although I don't fully understand the VB code, if the comments in the example code are anything to go by this would perform the color copying from the cell on sheet1 to a different cell on sheet2. The part I am still missing is how to read the cell contents on sheet1 and use that to determine the target cell location on sheet2. I will quote the simplified example from my earlier post .. I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc regards, Kevin "paul" wrote: Kevin/Gordon this http://www.microsoft.com/office/comm...1-2c0acdd69cbc thread lead me to this http://www.mvps.org/dmcritchie/excel/colors.htm page,and the macro below it looks like this could be what kevin is after??? Setting Interior Color based on another Cell (#popbased) Option Explicit Global gblColorIndex As Integer Sub SetInteriorColor() gblColorIndex = ActiveCell.Interior.ColorIndex End Sub Sub PutInteriorColor() Selection.Interior.ColorIndex = gblColorIndex End Sub Sub SameInteriorAsA1() Selection.Interior.ColorIndex = [A1].Interior.ColorIndex End Sub -- paul remove nospam for email addy! "kevinm" wrote: Gord, no worries, a few days isn't going to cause me any problem. Besides, when we have this working it will probably save me days of work and pain anyway, Kevin "Gord Dibben" wrote: Kevin You've not been forgotten. Been busy but will try to get you something in the next day or so. Monday I'm starting a Consulting contract and will be out of town for most of every week until end of September so I better get you something before then. Gord On Wed, 11 May 2005 13:06:11 -0700, kevinm wrote: Hi Paul, thanks for the suggestion, I am certainly willing to give is a try but how do I create this function, is it just another macro? Do I have to use the VB editor to type in the function by hand? sorry for the stupid questions, I have not been to this depth with Excel before, Kevin "paul" wrote: How about this for an idea.Have a custom worksheet function called say colour where a a cell (or selected range?) is shaded in relation to a certain value.I had a go using this code but it didnt like it Function colour() colour = Cells.Interior. .ColorIndex = 1 .Pattern = xlSolid End Function =colour(1) would result in the selected cell or cells being shaded in black the values on sheet 1 could then be linked to the cells on sheet two to give the result kevin wants.I think perhaps it could be a function that would be more flexible than conditional formatting. -- paul remove nospam for email addy! "kevinm" wrote: Gord, I am glad you understand my requirements now. It sounds so simple to do when I explain it in simple terms but I haven't been able to figure it out. If it is not possible I will just have to create a huge VBA file with a separate procedure defined for every cell in column A, this is going to be extremely tedious and error prone. In the real spreadsheet column A has about 600 rows to process! If you (or anyone else reading watching this forum) can figure it out it will save me an awful lot of time. Fingers crossed .. Kevin "Gord Dibben" wrote: Kevin I'd have to look at this one for a while. VBA is not my strong suit. Stick around here and wait for someone(there are many) with greater skills than myself. Meantime, I'll try to work on it between Tee-Times and other projects. Gord On Tue, 10 May 2005 09:01:08 -0700, kevinm wrote: Hi Gord, sorry I am doing a poor job explaining my requirements, it is difficult without actually sending you an example spreadsheet. Let me have another go at explaining, consider this simplified example: Sheet1 looks something like: Column A B C D E F Row 1 C3 test Red_Cell Row 2 D2 test Blue_Cell Row 3 A4 test Green_Cell Row 4 B3 test Pink_Cell Initially Sheet2 looks like: Column A B C D E F Row 1 Row 2 Row 3 Row 4 I would like a macro which work something like: Step1. Read cell value for first item in Sheet1 column A (A1 value = "C3") Step2. Check the color format of the cell two columns to the right (colored RED) Step3. Go to Sheet2, color the cell whose reference was determined in Step1 (C3) the color identified in Step2. Step4. Read the cell value for the second item in Sheet1 column A (A2 value = "D2") Step5. Check the color format of the cell two columns to the right (colored BLUE) Step6. Go to Sheet2, color the cell whose reference was determined in Step4 (D2) the color identified in Step5. Step7. Read the cell value for the third item in Sheet1 column A (A2 value = "A4") etc, etc When the macro has finished Sheet2 should look like: Column A B C D E F Row 1 Row 2 (blue) Row 3 (pink) (red) Row 4 (green) Where (xxx) is the color fill of the cell, not the value for the cell contents. I hope that my explanation is a little clearer this time, Kevin "Gord Dibben" wrote: Kevin Still not sure what your needs are. Copy cells or copy formats? Also "a number of them are grouped into 20 consecutive cells". How many and what is the criterion for deciding which 20 to copy? In blocks of 20 cells to where? This macro will copy cells only to another worksheet. Sub move20() ActiveCell.Resize(20, 1).Copy Destination:=Worksheets("Sheet2").Range("A1") End Sub Probably not what you want, but a start. No need for a new thread unless you want to post over in the excel.programming group, but everyone over there reads this group also. Gord On Mon, 9 May 2005 15:10:07 -0700, kevinm wrote: Gord/Myrna, thanks for your help, I went back and recreated the macro, I don't know what I did different but it is working now. All I need to figure out now is how to repeat the macro operation a number of times. Consider: My master worksheet has about 600 cells, all in one column. Of these a number of them are grouped into 20 consecutive cells. Instead of executing the macro twenty times to copy the 20 cells to worksheet 2, is there a way of looping the macro a user specified number of times? Let me know if I should start a new thread for this, thanks, Kevin "Gord Dibben" wrote: Kevin Errors out on this line most likely.......... ActiveCell.Offset(-9, 0).Range("A1").Select If the activecell is in above row 10 this will throw an error because you are trying to select from 9 rows above the activecell. The -9 moves up 9 rows, the 0 means column remains unchanged. What your macro is doing is selecting a cell 9 rows above the active cell then copying the format of that cell and pasting into the active cell then |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
