Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sequentially calculating the next cell value.

Status
Not open for further replies.

tudor30

Technical User
Jan 7, 2004
49
US
The code below is used to place an identified value greater than the cell of the same column in the previous row if the values between the two other cells in each row are different. This works well when there is 26 or fewer different compariasons. (building A, B, C,... etc.)

I'm looking for a version that will conitnue adding +1 to the previous cell value either numerically. (1, 2, ... but continue adding +1 after it reaches 9) If you change to lastletter = "1" than after 9 it uses symbols and then letters.

Code being used:

lastletter = "A"

Cells(2, 16) = lastletter
For intalpha = 3 To Cells(65536, 3).End(xlUp).Row

If Cells(intalpha, 2) = 0 Then lastletter = Chr(Asc(lastletter) + 1)
Cells(intalpha, 16) = lastletter
Next intalpha
 
Tudor,

You can do this much easier, I think.

You begin relative to some ROW and then use (Row - StartRow) in all your calculations. THe incrimental value is either MOD 26 or MOD 36 (referred to as the BASE), depending if you are including 0 thru 9 and A thru Z. The primary value is calculated using the INTEGER value of (Row / BASE).

Might be something like this...
Code:
Sub b()
    Const BASE = 36
    For r = 1 To 999
        s1 = Int((r - 1) / BASE)
        s2 = (r - 1) Mod BASE 
        If s1 = "0" Then
            s1 = " "
        ElseIf s1 >= 10 Then
            s1 = Chr(s1 + 55)
        End If
        If s2 >= 10 Then
            s2 = Chr(s2 + 55)
        End If
        Cells(r, 1).Value = s1 & s2
    Next
End Sub
???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Skip,
Thanks for the reply.

I apologize if I have not communicated thoroughly. Is it possible to use what you have only with the results be all numeric and then an example of alphanumeric lists of numbers in column A?

Thanks again,
John

 
I don't understand. Please post an example.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
When I run the first set of code that uses lastletter = I would get the following results in the work 2 column (1 to 8) I would like this number to continue up to 999, which would include enough "0" Level PartNumbers to be used.

Level PartNum work2
0 RE523132 1
1 R519336 1
1 R85027 1
1 RE519990 1
0 RE519990 2
1 19M9329 2
1 24H1439 2
1 40M7064 2
1 R517522 2
1 R518087 2
1 RE519802 2
1 RE519803 2
1 RE519810 2
0 RE519803 3
1 14M7148 3
1 R504728 3
1 R517506 3
1 RE521530 3
0 R518087 4
1 R518246 4
0 R517506 5
1 R518244 5
0 RE519810 6
1 30M7024 6
1 R517510 6
0 R517504 7
1 R518243 7
0 RE519802 8
1 14M7148 8
1 R504728 8
1 R517504 8
1 RE521530 8
 
I am TOTALLY confused!

What does you example have to do with "I" or any other LAST LETTER???

Here's a very simple FORMULA for Column C that does what your example SEEMS to request...
[tt]
C2: =IF(A2=0,IF(ISNUMBER(C1),C1+1,1),C1)
[/tt]
and copy down.

Using VBA
Code:
    For r = 2 To [A1].CurrentRegion.Rows.Count
      With Cells(r, 1)
        If .Value = 0 Then
          If r = 2 Then
            .Offset(0, 2).Value = 1
          Else
            .Offset(0, 2).Value = .Offset(-1, 2).Value + 1
          End If
        Else
          .Offset(0, 2).Value = .Offset(-1, 2).Value
        End If
      End With
    Next
???


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top