Do while true

[hayati]

Good Morning,

Can I get some help in a code I am trying to put together. I would like the code to do the following:

If the cell in A1 is PH do (something) until it finds the next PH or NP in column A. I already have (something) coded but I can't seem to find how to do a loop.

I am using a select case method as there are a few more options that column A can hold. I think this can be done with a "Do while true" method but couldn't figure it out how.

Thanks and Regards,
Matt

 

[Norie]

Matt

Can you post the code you have and explain further?

Do you want to go through every entry in column A and do something according to what value is in the cell?

 

[Greg Truby]

Always executes at least once...

Do
   <statements>
Loop While <condition>

or

Do
   <statements>
Loop Until <condition>

May or may not execute

Do While <condition>
   <statements>
Loop

or

Do Until <condition>
   <statements>
Loop

{edit}I see Norie has suggested you post your code. I agree, that would help. There may be a way to accomplish the task without a [slow] loop.{/edit}

 

[al_b_cnu]

Hi,

Something like this, maybe:

Sub DoUntilNextPHorNP()
Dim WS As Worksheet

Dim lRowEnd As Long
Dim R As Range

If [A1] <> "PH" Then Exit Sub
For Each R In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    If R.Text = "PH" Or R.Text = "NP" Then Call YourMacro(CurrentRow:=R.Row)
Next R
End Sub
Sub YourMacro(ByVal CurrentRow As Long)
MsgBox CurrentRow
End Sub

 

[hayati]

Thank you all for your promt replies. here is the code I am using:

Sub test1()
Dim transcode As Integer
Dim i
Dim lastrow As Integer
Dim mismatch As Integer
lastrow = [A65536].End(xlUp).Row
transcode = 1
For i = 2 To lastrow
    Select Case Cells(i, 1)
        Case Is = "NP"
            Cells(i, 2) = transcode
            If Left(Cells(i, 4), 5) = "H/W P" Or Left(Cells(i, 4), 5) = "S/W P" Or Left(Cells(i, 4), 5) = "MIXED" Then
                mismatch = 1
            Else
            End If
        
        Case Is = "PH"
            Cells(i, 2) = transcode
            ' this is where I need
            'the do while true part
            'it needs to use transcode
            'until it finds another PH or another NP in the following cells

    End Select
    transcode = transcode + 1
Next i
End Sub

What the test sheet looks like is:

Column A Column B
NP 1
NP 2
NP 3
NP 4
PH 5
PD 5
PD 5
PD 5
PD 5
PH 6
PD 6
PH 7
PD 7
PH 8
PD 8
PH 9
PD 9
PH 10
PD 10

The desired results are in Column B

Thanks once again for all your help.

REgards,
Matt

 

[Norie]

Can you explaing the logic?

 

[hayati]

Norie thanks for your help!

Let me try to explain as much as I can:

Column A can hold three variables. They are NP, PH and PD.

For each variable in Column A I need a transaction identifier put in column B. The logic will be:

If the cell in A is NP than the transaction code (column B) will be the next following identifier

If the cell in A is PH than the same transaction code will be used until it hits the next PH or NP

To give you more of an idea what I would like to do. These are product types. NP = no package, PH= package header, PD = package detail

I would like the transaction identifiers to number each product group so an NP will be one number and a package header with all its components will be another number...

here is another example:
Column A Column B
NP 1
NP 2
PH 3
PD 3
PD 3
PD 3
PH 4
PD 4
PD 4
PD 4
NP 5
NP 6
PH 7
PD 7

So you can see we actually sold 7 products some which have components attached to it.

 

[hayati]

There are also two checks which I am doing that is the "if ... mismatch = 1" part of the code. There is also another check on the PH which will be:

If A is PH but ther is no PD that follows there will be an error msg. This would mean that we sold a Package header without its components, or more likely a data entry error.

 

[Greg Truby]

Seeding B1 with a 1, this formula seems to work ok in B2:B14 =B1+OR(A2="NP",A2="PH")

Edit - for the error checking I just put this formula in C2 and copied down: =IF(AND(A1="PH",A2<>"PD"),"Header w/o Detail","") You could also do something in column B with conditional formatting.

 

[hayati]

Greg thanks for the idea

This is what I get with the formula:

1
NP 2
NP 2
PH 3
PD 3
PD 4
PD 4
PH 5
PD 5
PD 6
PD 6
NP 7
NP 7
PH 8
PD 8

But for instance the first two NP's are two seperate "no package" products so they should be 1 and 2. Also on the first package row 3 on the second component (PD) it changes the number while it should keep it as 3. Maybe I did something wrong?