increment by "1" a cell containing text & number

[fishinglittlepine]

I have a spreadsheet that I have 4 different suppliers in columns and items in about 2,000 rows. I need to create a code at the start of each row for each item, i.e WF01 - 500, WA01 - 500, LC01 - 1000.

I filter the lists to only show the WF or WA or LC items of supply and want to automatically increase the code WF01 to WF02 etc.

I understand that I can do this by simply dragging the bottom right corner down over them if they are not filtered, but I can't do it when they are filtered as the row numbers are 1, 4, 11, non consecutive.

It also won't accept =A1+1 dragged down as it doesn't recognise the text.

Help

Vince

 

[HarryS]

Hello Vince

A space in a column will be takesn as <> ""
If you have an x in each then try = "x" instead
( set
Option compare text
at top of module to avoid confusion with x and X if needed)

If there was already some other junk in column A
then put in a line

cells(rr,1) = ""
before the first If line
note the logic now is that the last column of 4,5,6 that has an anything in it will be taken to be the item...

Sub Code_Numbering()
    Dim WFn%, LOn%, WAn%, rr%
    Dim Startr%, Endr%
    Startr = 13: Endr = 2000
    ' what ever your start and end rows are
    For rr = Startr To Endr
        'looks like you have suppliers in columns ( using 4,5,6 as example)
        Cells(rr, 1) = ""    ' to set to blank any previous data
        'if you wanted look at  x as the only  marker then change if statements to like
        'If Cells(rr, 4) = "x"  Then

        If Cells(rr, 4) <> "" Then    ' whatever column wf is in
            WFn = WFn + 1
            Cells(rr, 1) = "WF" & Right("000" & WFn, 3)    ' to have all 3 digit codes
        End If
        If Cells(rr, 5) <> "" Then
            WAn = WAn + 1
            Cells(rr, 1) = "WA" & Right("000" & WAn, 3)    ' to have all 3 digit codes
        End If
        If Cells(rr, 6) <> "" Then
            LCn = LCn + 1
            Cells(rr, 1) = "LC" & Right("000" & LCn, 3)    ' to have all 3 digit codes
        End If
        ' you could change order of the if statements to give preference to the last mentioned
        ' or make the placement statements like
        'Cells(rr, 1) = cells(rr,1) & "," & "LC" & Right("000" & LCn, 3)
        ' to include doubles as WA123,LC045
    Next rr
End Sub

 

[fishinglittlepine]

Biff,

I got it!

<TABLE style="WIDTH: 975pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1300><COLGROUP><COL style="WIDTH: 975pt; mso-width-source: userset; mso-width-alt: 20800" width=1300><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #b8cce4; WIDTH: 975pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=17 width=1300>=IF(SUBTOTAL(3,D25),TEXT(SUBTOTAL(3,D$3:D25),"WF00"),IF(SUBTOTAL(3,E25),TEXT(SUBTOTAL(3,E$3:E25),"WA00"),IF(SUBTOTAL(3,F25),TEXT(SUBTOTAL(3,F$3:F25),"LC00"),"")))</TD></TR></TBODY></TABLE>

Had to change the formula marginally in the first subtotal section as it was still having issues with including LCXXX even if the LC column was blank? Still don't know why that was??

But it's working now.

Thank you for assistance.

Vince

 

[fishinglittlepine]

Harry,

Thanks for that, I think that on board as homework at the moment, my little brain is sore. I managed to get the thing working with the formula that Biff gave me, but I had to modify that as well? Must be something in those LC cells that's getting picked up somewhere?

Apart from that, how's the fishing in sunny Qld, we're just getting into reasonable fishing climes here in Melbourne, have to start planning?

Thanks gain for your assistance.

Vince

 

[T. Valko]

Biff,

I got it!

<TABLE style="WIDTH: 975pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1300 border=0><COLGROUP><COL style="WIDTH: 975pt; mso-width-source: userset; mso-width-alt: 20800" width=1300><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 975pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #b8cce4" width=1300 height=17>=IF(SUBTOTAL(3,D25),TEXT(SUBTOTAL(3,D$3:D25),"WF00"),IF(SUBTOTAL(3,E25),TEXT(SUBTOTAL(3,E$3:E25),"WA00"),IF(SUBTOTAL(3,F25),TEXT(SUBTOTAL(3,F$3:F25),"LC00"),"")))</TD></TR></TBODY></TABLE>

Had to change the formula marginally in the first subtotal section as it was still having issues with including LCXXX even if the LC column was blank? Still don't know why that was??

But it's working now.

Thank you for assistance.

Vince

You're welcome. Thanks for the feedback!

 

[fishinglittlepine]

Thank you all for your assistance, I am always amazed at the advice given through this forum, it helps me do my job and hopefully I'll learn enough to help others some day?

Vince