Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset,Match,Copy, Paste Special - A Complicated Task - Help Pleas
If any one could help with this complicated task I would be very grateful, it
is the last bit of a project I'm working on and would be the icing on the cake. I'll try to eplain the process I'm looking to achieve. I have a sheet (DDE Sheet) which will contain a table of DDE links. The table always starts from D7. The number of Columns may go as far as AZ and the number of rows my go to 107. The DDE links look like this =the962|IQVALUE!'L13O011G5(Dv)' where [G5] is the variable for the columns and [L13O011] is the variable for the rows. (I already have code for finding and replacing the row variable). I have another sheet (Link Refs) which contains all the various DDE links as text strings (not currently active) using the G & 5 as row and column references ie. {A} {B} {C} {upto J} {1} [BASE] S G === {2} 1 =the962|IQVALUE!'L13O011S1(Dv)' =the962|IQVALUE!'L13O011G1(Dv)' {3} 2 =the962|IQVALUE!'L13O011S2(Dv)' =the962|IQVALUE!'L13O011G2(Dv)' upto 510 etc etc In the DDE Sheet, columns are in pairs ie. E & F , G & H (with 25 pairs) where E5 and F5 are merged and the user enters a reference like S1, so using MID(1,1) and MID(2,1) E6 and F6 contain S and 1 (hidden from user) and the remaing rows, from 7 to 107, in each pair are merged. What I would like to do is, using the values in E6 and F6 find the corresponding string in the Link Refs sheet, Copy then paste special - values to the coreesponding column, in this case E and fill the column fro row 7, with the same string down to the last entry in column C. As the user moves to the next pair of columns, in this case G5 & H6 (merged) and enters a value like G2 the process is repeated for that pair of columns. The final process to make these links live is to find and replace the = at the begining of the string with =, this activates the link. I can probably figure this bit out but as for the rest I've tried setting some DIM statementsto ranges and then use OFFSET and MATCH,MATCH but I don't really know what I'm doing. Thanks in advance to anyone who responds |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset,Match,Copy, Paste Special - A Complicated Task - Help Pleas
By the way, the stings in the Link Refs sheet ate produced by batch varius
cells with & ie =$A$2&E$2&$A41&$E$1 for the S1 example and dragged down the page. I'm not sure if this would make a difference! Mark "Mark Dullingham" wrote: If any one could help with this complicated task I would be very grateful, it is the last bit of a project I'm working on and would be the icing on the cake. I'll try to eplain the process I'm looking to achieve. I have a sheet (DDE Sheet) which will contain a table of DDE links. The table always starts from D7. The number of Columns may go as far as AZ and the number of rows my go to 107. The DDE links look like this =the962|IQVALUE!'L13O011G5(Dv)' where [G5] is the variable for the columns and [L13O011] is the variable for the rows. (I already have code for finding and replacing the row variable). I have another sheet (Link Refs) which contains all the various DDE links as text strings (not currently active) using the G & 5 as row and column references ie. {A} {B} {C} {upto J} {1} [BASE] S G === {2} 1 =the962|IQVALUE!'L13O011S1(Dv)' =the962|IQVALUE!'L13O011G1(Dv)' {3} 2 =the962|IQVALUE!'L13O011S2(Dv)' =the962|IQVALUE!'L13O011G2(Dv)' upto 510 etc etc In the DDE Sheet, columns are in pairs ie. E & F , G & H (with 25 pairs) where E5 and F5 are merged and the user enters a reference like S1, so using MID(1,1) and MID(2,1) E6 and F6 contain S and 1 (hidden from user) and the remaing rows, from 7 to 107, in each pair are merged. What I would like to do is, using the values in E6 and F6 find the corresponding string in the Link Refs sheet, Copy then paste special - values to the coreesponding column, in this case E and fill the column fro row 7, with the same string down to the last entry in column C. As the user moves to the next pair of columns, in this case G5 & H6 (merged) and enters a value like G2 the process is repeated for that pair of columns. The final process to make these links live is to find and replace the = at the begining of the string with =, this activates the link. I can probably figure this bit out but as for the rest I've tried setting some DIM statementsto ranges and then use OFFSET and MATCH,MATCH but I don't really know what I'm doing. Thanks in advance to anyone who responds |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Offset,Match,Copy, Paste Special - A Complicated Task - Help P
Anybody ! Please
"Mark Dullingham" wrote: By the way, the stings in the Link Refs sheet ate produced by batch varius cells with & ie =$A$2&E$2&$A41&$E$1 for the S1 example and dragged down the page. I'm not sure if this would make a difference! Mark "Mark Dullingham" wrote: If any one could help with this complicated task I would be very grateful, it is the last bit of a project I'm working on and would be the icing on the cake. I'll try to eplain the process I'm looking to achieve. I have a sheet (DDE Sheet) which will contain a table of DDE links. The table always starts from D7. The number of Columns may go as far as AZ and the number of rows my go to 107. The DDE links look like this =the962|IQVALUE!'L13O011G5(Dv)' where [G5] is the variable for the columns and [L13O011] is the variable for the rows. (I already have code for finding and replacing the row variable). I have another sheet (Link Refs) which contains all the various DDE links as text strings (not currently active) using the G & 5 as row and column references ie. {A} {B} {C} {upto J} {1} [BASE] S G === {2} 1 =the962|IQVALUE!'L13O011S1(Dv)' =the962|IQVALUE!'L13O011G1(Dv)' {3} 2 =the962|IQVALUE!'L13O011S2(Dv)' =the962|IQVALUE!'L13O011G2(Dv)' upto 510 etc etc In the DDE Sheet, columns are in pairs ie. E & F , G & H (with 25 pairs) where E5 and F5 are merged and the user enters a reference like S1, so using MID(1,1) and MID(2,1) E6 and F6 contain S and 1 (hidden from user) and the remaing rows, from 7 to 107, in each pair are merged. What I would like to do is, using the values in E6 and F6 find the corresponding string in the Link Refs sheet, Copy then paste special - values to the coreesponding column, in this case E and fill the column fro row 7, with the same string down to the last entry in column C. As the user moves to the next pair of columns, in this case G5 & H6 (merged) and enters a value like G2 the process is repeated for that pair of columns. The final process to make these links live is to find and replace the = at the begining of the string with =, this activates the link. I can probably figure this bit out but as for the rest I've tried setting some DIM statementsto ranges and then use OFFSET and MATCH,MATCH but I don't really know what I'm doing. Thanks in advance to anyone who responds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
complicated copy/paste/delete | Excel Programming | |||
Copy and paste has got complicated. Help please | Excel Discussion (Misc queries) | |||
Complicated Index Match Offset function | Excel Worksheet Functions | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming |