Hi,
I need to increment the values in a column according to the selection in the adjacent column(& the row above it).
Scenario:
Column A is to be autonumbered with Sub Auto_Number(). User will select range in Col A with mouse.
If B2 has an "A" then A2 = -1
if B3 has a "S" then A3 = A2
But if B3 has a "Z" then A3 = -2
This could extend 200 rows.
But my code just puts in a "-1"
Plse correct:
Sub Auto_Number()
Dim Cell As Range
Dim A As Long, S As Long, Z As Long
Dim Ans As Integer
Ans = Application.WorksheetFunction.Sum(ActiveCell.Offset(-1, 0) - 1)
For Each Cell In Selection
Do Until ActiveCell.Offset(0, 1).Value = ""
If _
ActiveCell.Offset(0, 1) = "A" Then _
ActiveCell.Value = Ans
ActiveCell.Offset(1, 0).Activate
If _
ActiveCell.Offset(0, 1) = "S" Then _
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value
ActiveCell.Offset(1, 0).Activate
If _
ActiveCell.Offset(0, 1) = "Z" Then _
ActiveCell.Value = Ans
ActiveCell.Offset(1, 0).Activate
Loop
Next Cell
End Sub
Thanks again
Mohamed
Do Until Loop
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Do Until Loop
You're using a double loop (For Each ... Next and Do Until ... Loop); I see no need for that.
What should happen if the cell in column B is not blank and doesn't contain either "A", "S" or "Z"?
What should happen if the cell in column B is not blank and doesn't contain either "A", "S" or "Z"?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Do Until Loop
Hi Hans,
Column B has data validation list, can only be "Z", "A", "S". In wrkbk blank = end of workcycle, user completed job.
Thanks again
Mohamed
Column B has data validation list, can only be "Z", "A", "S". In wrkbk blank = end of workcycle, user completed job.
Thanks again
Mohamed
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Do Until Loop
Does this do what you want?
Code: Select all
Sub Auto_Number()
Dim Cell As Range
For Each Cell In Selection
Select Case Cell.Offset(0, 1).Value
Case "A"
Cell.Value = -1
Case "S"
Cell.Value = Cell.Offset(-1, 0).Value
Case "Z"
Cell.Value = -2
Case ""
Exit For
End Select
Next Cell
End Sub
Best wishes,
Hans
Hans