Hamond
07-21-2009, 05:25 AM
Hello,
I have data from column BC to DA in a sheet called changes. Actual data is from row 5 to row 163. Series names are in row 1.
I want to find any series/column in the range that contains any occurrence of the value #NUM!, and replace the series/entire column with the equivalent series from another sheet (called levels) that is also shares the same name in row 1.
So in the sheet "changes", if row 7 in column BP (call it Series ZK) contains #NUM!, then I want to find the equivalent series in the sheet "Levels" (which will also be called Series ZK in row 1 but in any column), copy over that entire column and replace the column in the changes sheet.
I've attached an example to make things clearer. Column BP - X14 in the sheet levels contains #NUM!, I want to replace this with column O from the levels sheet.
I'm thinking that it would be easy to identify the relevant columns either by using find or using a formula such as sum to sum each columns contents, this will return #num! if it contains it. But I'm not sure about the code.
Thanks,
Hamond
I have data from column BC to DA in a sheet called changes. Actual data is from row 5 to row 163. Series names are in row 1.
I want to find any series/column in the range that contains any occurrence of the value #NUM!, and replace the series/entire column with the equivalent series from another sheet (called levels) that is also shares the same name in row 1.
So in the sheet "changes", if row 7 in column BP (call it Series ZK) contains #NUM!, then I want to find the equivalent series in the sheet "Levels" (which will also be called Series ZK in row 1 but in any column), copy over that entire column and replace the column in the changes sheet.
I've attached an example to make things clearer. Column BP - X14 in the sheet levels contains #NUM!, I want to replace this with column O from the levels sheet.
I'm thinking that it would be easy to identify the relevant columns either by using find or using a formula such as sum to sum each columns contents, this will return #num! if it contains it. But I'm not sure about the code.
Thanks,
Hamond