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

 

[Greg Truby]

That's just due to an alignment issue. Here's what mine looks like. Note that I changes the value in row 11 to test the error message formula in column C.

sampling with replacement problem (scheduling).xls
	A	B	C	D
1	NP	1
2	NP	2
3	PH	3
4	PD	3
5	PD	3
6	PD	3
7	PH	4
8	PD	4
9	PD	4
10	PH	5
11	NP	6	Header w/o Detail
12	NP	7
13	PH	8
14	PD	8
Sheet2

 

[Greg Truby]

Look's like Colo's utility didn't show the formulas in column C if they resulted in a null string. But they're there. Actually, I would probably change the formula in C2 down to read =IF(AND(A1="PH",A2<>"PD"),"Header w/o Detail",IF(AND(A1="NP",A2="PD"),"Detail w/o Header","")) to check for detail records with no header as well.

 

[hayati]

Greg,

Yes indeed it is working great!! Thanks a lot! I

Is it posible to use this in a macro?

Once again thanks for your help!!

Cheers,
Matt

Edited: I love your logic in the formula by the way. Didn't know that a True value could be used as a 1 within another formula!

 

[hayati]

I tried using:

transcode = Application.WorksheetFunction.ABS(B1+OR(A2="NP",A2="PH"))

But is is giving me an error and pointing to the OR part of the formula.

I thought by using Application.WorksheetFunction.... You could use any function you can use in Excel?

Cheers
Matt

 

[Norie]

Matt

Why not just put Greg's formula in the first cell and then copy down?

 

[Greg Truby]

I was just gettin' ready to ask the same thing... why do you still need the transcode variable (or any VBA for that matter)? What else is going on?

 

[hayati]

Sorry for the confusion.

This formula works great for me and I already got my result. (Thanks once again!)

But this is a shared workbook (2 admins including myself and about 35 users) with 120 columns in use and it has a 600< line macro running in the background which populates other worksheets. Also it is used for uploading info into a database everymonth.

For these reasons everytime we need a column updated we try to add a part to the macro to keep things in the background and keep things simple on the surface.

My experience with multiple users and formulas on a sheet have not been very good, some how it always gets played around with.

Hope this doesn't complicate things too much...

Thanks for your help once again.

Regars,
Matt

 

[Greg Truby]

In that case I'd do my update by letting Excel to the heavy lifting via the formulas. Loops are pretty slow. I'd use VBA to just pop in the formulae and then overwrite with values if desired. Something like so:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> UpdateColumnsB_and_C()
    
    <SPAN style="color:#00007F">Dim</SPAN> rngCurr <SPAN style="color:#00007F">As</SPAN> Range, rngColB <SPAN style="color:#00007F">As</SPAN> Range, rngColC <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Set</SPAN> rngCurr = [A1].CurrentRegion
    <SPAN style="color:#00007F">With</SPAN> rngCurr
        <SPAN style="color:#00007F">Set</SPAN> rngColB = .Columns(2).Offset(1).Resize(.Rows.Count - 1)
        <SPAN style="color:#00007F">Set</SPAN> rngColC = .Columns(3).Offset(1).Resize(.Rows.Count - 1)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    [B1] = 1
    <SPAN style="color:#00007F">With</SPAN> rngColB
        .FormulaR1C1 = "=R[-1]C+OR(RC[-1]=""NP"",RC[-1]=""PH"")"
        .Formula = .Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">With</SPAN> rngColC
        .FormulaR1C1 = _
            "=IF(AND(RC[-2]=""PH"",R[1]C[-2]<>""PD""),""Header w/o Detail"",IF(AND(R[-1]C[-2]=""NP"",RC[-2]=""PD""),""Detail w/o Header"",""""))"
        .Formula = .Value
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>

If you are not doing so already, I would compartmentalize update processes into subroutines so that you have a main routine that calls multiple subroutines rather than one gargantuan update routine with hundreds and hundreds of lines of code.

 

[hayati]

Greg,

Thank you so much. Yes at the moment each column is populated buy a new subroutine. I have chaged the columns and fit it in the sheet. It works great.

Just one last question:


Can I populate any excel formula by using the

.FormulaR1C1 = "..."
.Formula = .Value

Method?

Thanks once again. It is working great!

Cheers,
Matt

 

[Greg Truby]

To answer your question(s) - yes and yes. The rngXXX.Formula = rngXXX.Value is functionally the same as pasting values. If I may offer some advice. If you are using XL2002 or better, you can use enumerated constants to make your code more robust. If you're using <2002 then you can use module-level or global constants. Use them to define column positions. That way (a) the code is more "self-documenting" and (b) maintainance is much easier. If you or another administrator find that you need to alter the spreadsheet's structure by inserting or deleting a column all you have to do is alter the enumeration (or Const) statement(s) and voilá everything is updated. It takes longer to set up but if you need to alter the worksheet structure even just once or twice, you'll find it will have saved you a lot of re-coding time. Here's an example from some of my own coding...

Public Enum eg_tatColumns
    tcolCustName = 1        '  1  A
    tcolAcctNum             '  2  B
    tcolGroupWhse           '  3  C
    tcolGroupTime           '  4  D
    tcolCustPO              '  5  E
    tcolPNSupp              '  6  F
    tcolQty                 '  7  G
    tcolInvcNum             '  8  H
    tcolInvcLn              '  9  I
    tcolOpenD               ' 10  J
    tcolCnfmD               ' 11  K
    tcolCarr                ' 12  L
    tcolWaybill             ' 13  M
    tcolLclAirport          ' 14  N
    tcolArrWhse             ' 15  O
    tcolPosted              ' 16  P
    tcolReShipped           ' 17  Q
    tcolCustRecdD           ' 18  R
    tcolCustLoc             ' 19  S
    tcolTATLclAirport       ' 20  T
    tcolTAT1st = 20         ' ** redef
    tcolTATWhse             ' 21  U
    tcolTATPost             ' 22  V
    tcolTATReShip           ' 23  W
    tcolTATCustRecInCap     ' 24  X
    tcolTATCustRecOutCap    ' 25  Y
    tcolTATTotalThruRect    ' 26  Z
    tcolTATTotalThruPost    ' 27 AA
    tcolTATLast = 27        ' ** redef
End Enum


'..............
' the following creates a formula with RELATIVE column references
'..............
    ' _____                             _____
    ' _____TAT for ===> NPC Whse to Post_____
    Set rngCol = rngCurr.Columns(eg_tatColumns.tcolTATPost)
    With rngCol
        .FormulaR1C1 = "=IF((RC" & eg_tatColumns.tcolArrWhse & "*RC" & _
                       eg_tatColumns.tcolPosted & _
                       ")<>0,MAX(0,NETWORKDAYS(RC[" & _
                       eg_tatColumns.tcolLclAirport - eg_tatColumns.tcolTATWhse & _
                       "],RC[" & _
                       eg_tatColumns.tcolArrWhse - eg_tatColumns.tcolTATWhse & _
                       "])-1),""x"")"
        .Formula = .Value
    End With

'........
' after a while I got smarter and just created R1C1 formulas with 
' absolute column references; much easier to write the code
'.........
    ' ______                                                ______
    ' ______TAT column for rush orders inside capital province.______
    Set rngCol = rngCurr.Columns(eg_tatColumns.tcolTATCustRecInCap)
    With rngCol
        .FormulaR1C1 = "=IF(AND((RC" & eg_tatColumns.tcolReShipped & "*RC" & _
                       eg_tatColumns.tcolCustRecdD & _
                       ")<>0,OR(RC" & eg_tatColumns.tcolCustLoc & _
                       "=0,RC" & eg_tatColumns.tcolCustLoc & _
                       "=""In Cap. Prov."")),MAX(0,NETWORKDAYS(RC" & _
                       eg_tatColumns.tcolReShipped & ",RC" & _
                       eg_tatColumns.tcolCustRecdD & ")-1),""x"")"
        .Formula = .Value
    End With

Regards,