macro to set cell to zero

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Hi,

I need some help with a macro that will automatically
populate cells with a zero if a previous cell is a certain
value. For example:

term 36
P1 P2 P3 P4 P5
beg. Month 1 13 25 0 0
end month 12 24 36 0 0

since the term=36, the end month cannot be greater than
36. The period in which the end month = term should be the
last period with months entered (the user enters the beg
and end months in the cells so there cannot be any
formulas in the cells that will get overwritten). Any
periods after the period with an end month that = term
should automatically be populated with a zero in the beg &
end months (such as P4 & P5).

Can you please help me with a macro to do this?

Thanks,
Robert
 
Hi Robert
one way: to achieve this you have to process the worksheet_change
event. First some assumptions:
- your termn is entered in cell A1 (e.g. 36 in your example)
- the beg. month is entered in row 3
- the end month in row 3
- the data is entered starting in column B ranging to column G

paste the following code in your worksheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const last_column = 7 'column G is the last column

If Intersect(Target, Me.Range("B3:G3")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
With Target
If .Value >= Me.Range("A1") Then
Application.EnableEvents = False
.Offset(-1, 1).Resize(2, last_column - .Row).Value = 0
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub

HTH
Frank
 
Hi Frank. Thanks for your reply.

The macro works fine if I enter data in B3. It then enters
a zero in columns B3:G3. However, if I enter data in
another column, the zeros get entered in columns beyond
the original range. ie, I need to have the last column as
a dynamic variable depending on which column the
number "36" is being entered in.

Also, if I put this further down the worksheet in row 200
for example, the expression :.Offset(-1, 1).Resize(2,
last_column - .Row).Value = 0 doesn't work.

Do you know how I can make the column range dynamic?

thanks for all your help. You guys are great!!
 
Hi Robert
typical mistake. First change the line
..Offset(-1, 1).Resize(2, last_column - .Row).Value = 0
to
..Offset(-1, 1).Resize(2, last_column - .column).Value = 0
this will solve the first problem

for your second question: As I mentioned in my previous post, currently
this procedure will only work for row 3. If you want this to happen for
other columns as well you have to change the line
If Intersect(Target, Me.Range("B3:G3")) Is Nothing Then Exit Sub
accordingly

Frank
 
That did the trick. Thank you very much!


-----Original Message-----
Hi Robert
typical mistake. First change the line
..Offset(-1, 1).Resize(2, last_column - .Row).Value = 0
to
..Offset(-1, 1).Resize(2, last_column - .column).Value = 0
this will solve the first problem

for your second question: As I mentioned in my previous post, currently
this procedure will only work for row 3. If you want this to happen for
other columns as well you have to change the line
If Intersect(Target, Me.Range("B3:G3")) Is Nothing Then Exit Sub
accordingly

Frank




.
Click to expand...
 
Back
Top