Do Until Loop

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Do Until Loop

Post by MSingh »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Do Until Loop

Post by HansV »

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"?
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Do Until Loop

Post by MSingh »

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

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Do Until Loop

Post by HansV »

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