Macro help required!

D

Don Niall

Hi - I would appreciate some input on the macro below. I
apologise in advance as I am just getting familiar with
macros. The macro is working but only on the first cell
B4. I need it to step through each cell from B4 to B17,
and do the same procedure ....I am guessing I need some
kine of For/Netxt statement - or some kind of loop
condition? Would really appreciate some help?
(PS Any good resources available - books or others - on
Macros for beginners?)

Don-

Dim myRange As Range
Set myRange = Range("B4:B17")
If myRange.Value = "RED" Then
With myRange
.Offset(0, -1).Value = ClearContents
.Offset(0, 1).Value = 0
.Offset(0, 2).Value = 0
.Offset(0, 4).Value = 0
End With
End If
End Sub
 
N

Norman Jones

Hi Don,

Try:

Sub Tester()
Dim myRange As Range
Dim cell As Range
Set myRange = Range("B4:B17")
For Each cell In myRange
If cell.Value = "RED" Then
With cell
.Offset(0, -1).ClearContents
.Offset(0, 1).Resize(1, 2).Value = 0
.Offset(0, 4).Value = 0
End With
End If
Next
End Sub
 
D

Don Niall

Thankyou! This worked ...... well very nearly!!!
The problem I am now encountering is, if the value is set
to anything OTHER THAN "RED" it still triggers the same
outcomes (ie the identifed cells are set to zero, and the
contents of 1 cell are cleared).

The "RED" identifier is coming directly from a Data
Validation list I have specified in cells B4 - B17. There
are three other entries in the same list(GREEN, BLUE,
YELLOW). Is it possible that the macro 'see's' ALL values
in the List - irrespective of which one actually appears
in the cell? If so how do I get around this?

Many thanks,

Don-
 
N

Norman Jones

Hi Don,

The use of data validation should have no relevance and the my code works
for me.

Are you SURE that you are using the code that I suggested? If you didn't
paste the code into your module, then it is possible that you have a
transription error.
 
D

Don Niall

Norman - Many thanks! There was indeed an error in my code.
One small thing I noticed:
If B4 (for example) is set to "RED" and the w/s saved, and
closed. When I re-open the w/s the macro doesn't fire (or
at least doesn't appear to)? I would have thought once I
get the prompt to 'Enable Macro's' it would fire, and
trigger the outputs required ......??

Any thoughts? (I guess I need to get more conversant with
macros, eh?!!)

Don-
 
H

Harald Staff

No. Macros run when you run them, or when they are called from other macros.

Imagine you had a "delete files" macro for some spesific cleaning task,
would you want it to delete files as soon as you enable macros in general
on opening ?

HTH. Best wishes Harald
 
N

Norman Jones

Hi Don,

If you want the macro to fire when the workbook is opened, try the
following:

Assuming your macro is in a standard module (rather than a sheet or the
thisworkbook module),
(1) Right-click the Excel icon to the left of 'File' on your menu bar
(2) Select 'View Code'
(3) Paste the following code

Sub workbook_open()
Call Tester '<------- REPLACE !!
End Sub

Replace Tester with the name of your macro.

It would be well worth your time looking at event procedures and, more
particularly, the discussion
http://www.cpearson.com/excel/events.htm
on Chip Pearson's web site.
 
Top