Macro/Worksheet interaction

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Tom Ogilvy]

Range(ActiveCell.Value).Replace What:=Range("B9"), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Guest]

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]

Range(ActiveCell, ActiveCell.Offset(2, 2)). _
Replace What:=Range("AB28"), _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Guest]

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

 

[Guest]

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