Add alpha character...

Question

I had one of the "community" help me with a problem in which he wrote a VB code for me. The first column was a part number in which a five digit number was entered (44141). My problem is I want to add an alpha to it which indicates a version of the part. Whenever I add it to the Table it no longer will add the number of parts to the sheet. I think its because its looking for only number values in the part number. This is the portion of the code I think needs to be changed.....any thoughts on how to proceed?

   Application.Volatile

    Set ws = rng.Parent

    With ws

        Set rngTemp = .Cells(rng.Row, 1)    'Assign range variable to the cell same row in Part# column

        If rngTemp.Value = "" Then          'If the cell in column A and on same row is blank

            Set rngTemp = rngTemp.End(xlUp) 'Move up to non blank cell containing the Part# and assign that cell to the range variable

        End If

        If IsNumeric(rngTemp) Then

            'If first Part# is deleted then rngTemp becomes the column header and will not be numeric and cause an error.

            TotUnits = rngTemp.Offset(0, 1).Value     'Assign the value in the Cell to right of Part# (#Full Units Required) to the Funtion

        Else

            TotUnits = 0    'Set to zero if first Part# is deleted

        End If

    End With

End Function

Answer 1

In Module1 edit the code as per the comments in bold italics in the example code below. (Delete the old line and insert the new line it its place).

Because you have placed an alpha on the end of the number it does not evaluate to a numeric to identify that it is not the column header so we will test the left 5 characters only to ensure they are numeric.

You will need to re-insert your column header (Total Boards Required) in cell E1.

Function TotUnits(rng As Range) As Long

    Dim ws As Worksheet

    Dim rngTemp As Range

    Application.Volatile

    Set ws = rng.Parent

    With ws

        Set rngTemp = .Cells(rng.Row, 1)    'Assign range variable to the cell same row in Part# column

        If rngTemp.Value = "" Then          'If the cell in column A and on same row is blank

            Set rngTemp = rngTemp.End(xlUp) 'Move up to non blank cell containing the Part# and assign that cell to the range variable

        End If

        'If IsNumeric(rngTemp) Then     'Delete this line of code

        If IsNumeric(Left(rngTemp, 5)) Then     'Replace deleted line with this line of code

            'If first Part# is deleted then rngTemp becomes the column header and will not be numeric and cause an error.

            TotUnits = rngTemp.Offset(0, 1).Value     'Assign the value in the Cell to right of Part# (#Full Units Required) to the Funtion

        Else

            TotUnits = 0    'Set to zero if first Part# is deleted

        End If

    End With

End Function

Answer 2

Aussie Mac to the rescue....worked perfectly...thanks again. I'll hopefully not need to make and more changes to this part of the programing. You've been a big help and I've started to see that there's been a lot of changes since I last looked into Excel. Again thanks.

Answer 3

Good Morning Aussie Mac...I bet you had to think twice about becoming involved "again". I had added the alpha to the table and the programing brought up he components as hoped for. The problem exists when I try to use the Totals for the parts needed....

Column E is the total boards required and it renders the correct value when the orginal table has just numeric numbers in column A. When I updated the table with "Alpha" then I get the above. Take the "Alpha" out and it works perfect again. That's why it appeared to me that the programing was looking for a "numeric" value only in column A.  As usual I'm grateful for any insight you can share...Thanks.

Answer 4

It was I who answered your previous question. If you want to add an alpha character to the number then you need to edit the lookup table and add the records with the alpha characters because they become individual values for the Lookup to work..

eg. if 44141 is now 44141A, 44141B  and 44141C then all the part numbers must be in the lookup table like the following screen shot.