Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
I am trying to write an Excel Macro to carry out 3 repetative changes to
groups of cells on a matrix 36 cells wide x 24 cells deep, after I have input a numeric value to a specific cell on the worksheet and then chosen an active cell. The first change is to read the numeric value into a REPLACE function in the macro. Next to select a range of cells 4 columns wide x 3 rows deep from the active cell in the top left location. I have tried doing this with the command €“ Range(Activecell,activecell.Offset(3,2)).Select Clearcontents But when I try to run this, it returns the comment €œSub or function not defined€ The global array is made up of 6 €“ 12 x 12 sub matrices €“ 3 wide x 2 deep. The second requirement is to locate which of these 6 sub matrices contain the active cell. I think this can be done by nested €˜if statements. I.e. If(activecell<M13,subr1,if(activecell<Y13,subr2,if (..................etc Subr1 then says Range(A1:L12) subrx Subr 2 Range(M1:X12) subrx But again it will not run The final requirement is to select a range of 4 columns with the active cell column reference as the leftmost. Can you help? Les Power |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
A sample replace command might be:
Range("A1:Z26").Replace What:=ActiveCell.Value, _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False To expand to a range of specific dimensions (3 rows by 4 columns) ActiveCell.Resize(3,4).ClearContents perhaps. -- Regards, Tom Ogilvy "Rewop Eilsel" wrote in message ... I am trying to write an Excel Macro to carry out 3 repetative changes to groups of cells on a matrix 36 cells wide x 24 cells deep, after I have input a numeric value to a specific cell on the worksheet and then chosen an active cell. The first change is to read the numeric value into a REPLACE function in the macro. Next to select a range of cells 4 columns wide x 3 rows deep from the active cell in the top left location. I have tried doing this with the command - Range(Activecell,activecell.Offset(3,2)).Select Clearcontents But when I try to run this, it returns the comment "Sub or function not defined" The global array is made up of 6 - 12 x 12 sub matrices - 3 wide x 2 deep. The second requirement is to locate which of these 6 sub matrices contain the active cell. I think this can be done by nested 'if' statements. I.e. If(activecell<M13,subr1,if(activecell<Y13,subr2,if (..................etc Subr1 then says Range(A1:L12) subrx Subr 2 Range(M1:X12) subrx But again it will not run The final requirement is to select a range of 4 columns with the active cell column reference as the leftmost. Can you help? Les Power |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
Thank you Tom for your response which works well, but not for my problem. I won't the REPLACE value to be read from a fixed cell on the worksheet and the ACTIVECELL to locate the area for its implementation. Perhaps you can regurgitate it to accomplish that. Les Power |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
Range(ActiveCell.Value).Replace What:=Range("B9"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False -- Regards, Tom Ogilvy "Rewop Eilsel" wrote in message ... Thank you Tom for your response which works well, but not for my problem. I won't the REPLACE value to be read from a fixed cell on the worksheet and the ACTIVECELL to locate the area for its implementation. Perhaps you can regurgitate it to accomplish that. Les Power |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
I could not get your second suggestion to run, however it gave me an idea for
the following Range(ActiveCell, ActiveCell.Offset(2, 2)). Replace What:=Range("AB28"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False I feel that this is on the right lines but it did not run, giving me a compile & syntax error on the first line - any suggestions Regards Les Power "Tom Ogilvy" wrote: Range(ActiveCell.Value).Replace What:=Range("B9"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False -- Regards, Tom Ogilvy "Rewop Eilsel" wrote in message ... Thank you Tom for your response which works well, but not for my problem. I won't the REPLACE value to be read from a fixed cell on the worksheet and the ACTIVECELL to locate the area for its implementation. Perhaps you can regurgitate it to accomplish that. Les Power |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
Range(ActiveCell, ActiveCell.Offset(2, 2)). _
Replace What:=Range("AB28"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False -- Regards, Tom Ogilvy "Rewop Eilsel" wrote in message ... I could not get your second suggestion to run, however it gave me an idea for the following Range(ActiveCell, ActiveCell.Offset(2, 2)). Replace What:=Range("AB28"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False I feel that this is on the right lines but it did not run, giving me a compile & syntax error on the first line - any suggestions Regards Les Power "Tom Ogilvy" wrote: Range(ActiveCell.Value).Replace What:=Range("B9"), _ Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False -- Regards, Tom Ogilvy "Rewop Eilsel" wrote in message ... Thank you Tom for your response which works well, but not for my problem. I won't the REPLACE value to be read from a fixed cell on the worksheet and the ACTIVECELL to locate the area for its implementation. Perhaps you can regurgitate it to accomplish that. Les Power |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
FAO Tom Ogilvy
Thankyou for your latest suggestion, it works perfectly and enables me to write the first elements of the programme, outlined in my first question. This leaves just the last two elements from the line "The global array..." The first of these being, I think, a nested IF statement? The second perhaps a COLUMNS statement Be very pleased if you can resolve these last two for me. Regards Les Power "Rewop Eilsel" wrote: I am trying to write an Excel Macro to carry out 3 repetative changes to groups of cells on a matrix 36 cells wide x 24 cells deep, after I have input a numeric value to a specific cell on the worksheet and then chosen an active cell. The first change is to read the numeric value into a REPLACE function in the macro. Next to select a range of cells 4 columns wide x 3 rows deep from the active cell in the top left location. I have tried doing this with the command €“ Range(Activecell,activecell.Offset(3,2)).Select Clearcontents But when I try to run this, it returns the comment €œSub or function not defined€ The global array is made up of 6 €“ 12 x 12 sub matrices €“ 3 wide x 2 deep. The second requirement is to locate which of these 6 sub matrices contain the active cell. I think this can be done by nested €˜if statements. I.e. If(activecell<M13,subr1,if(activecell<Y13,subr2,if (..................etc Subr1 then says Range(A1:L12) subrx Subr 2 Range(M1:X12) subrx But again it will not run The final requirement is to select a range of 4 columns with the active cell column reference as the leftmost. Can you help? Les Power |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/Worksheet interaction
FAO Tom Ogilvy
May I ask wether you will you be responding to my last request for help, on 29th. September, or do you think I should now direct my query elsewhere? Les Power "Rewop Eilsel" wrote: I am trying to write an Excel Macro to carry out 3 repetative changes to groups of cells on a matrix 36 cells wide x 24 cells deep, after I have input a numeric value to a specific cell on the worksheet and then chosen an active cell. The first change is to read the numeric value into a REPLACE function in the macro. Next to select a range of cells 4 columns wide x 3 rows deep from the active cell in the top left location. I have tried doing this with the command €“ Range(Activecell,activecell.Offset(3,2)).Select Clearcontents But when I try to run this, it returns the comment €œSub or function not defined€ The global array is made up of 6 €“ 12 x 12 sub matrices €“ 3 wide x 2 deep. The second requirement is to locate which of these 6 sub matrices contain the active cell. I think this can be done by nested €˜if statements. I.e. If(activecell<M13,subr1,if(activecell<Y13,subr2,if (..................etc Subr1 then says Range(A1:L12) subrx Subr 2 Range(M1:X12) subrx But again it will not run The final requirement is to select a range of 4 columns with the active cell column reference as the leftmost. Can you help? Les Power |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro interaction | Excel Discussion (Misc queries) | |||
Macro Interaction on Server | Excel Programming | |||
chart/worksheet/macro interaction | Charts and Charting in Excel | |||
macro/worksheet/chart interaction | Excel Discussion (Misc queries) | |||
How can I stop a macro for Human Interaction | Excel Programming |