Fill first group of blank cells with "A"+incremented number, fill second group with "X"+incremented number

Question

I am looking for direction on VBA code for Excel.

I have information exported from a schematic and parts list. There are usually 3-5 items at the beginning of the parts list that do not have reference designators (ref des), and several items at the end of the parts list that don't have ref des assigned.

The first blank group (beginning of parts list) I would like to assign the ref des as "A" and an incremented number (ie A1, A2, A3).

I would like to assign the next group of blank items found at the end of the list as "X" plus the number (X1, X2, X3, X4).

The only thing I have been able to find is filling with all the same text, or just numbers. I have found no way to designate the different groups.

Example of file data:
    Pos RefDes  Part Number 
    1          1-234 
    2          2-345
    3          3-456 
    20   C1    5-678
    21   C2    6-789 
    22   C3    7-345 
    158  U14   8-456 
    159  U18   8-058 
    167        9-176-1
    168        9-272-1 
    169       10-349-1 
    171       10-883-1 
    172       11-1441-1

So it would be logic like: if info in column A (Pos) but not in B (RefDes) the B="A1", next incremented number until B (RefDes) is not empty, then next empty B (Ref Des) cell where A (Pos) is populated then (RefDes) B="X1" incremented until A (Pos) is empty (end of range)

Hope this is enough information.

This is an example of what I was using but didn't work for the second group and I don't want to have to search for specific text as it could be slightly different.

Sub AddRefDes_IfBlank()

Dim X As String
Dim n As Integer

On Error Resume Next

    Cells.Find(What:="PWA", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
    ActiveCell(1, -1).Select
    ActiveCell.FormulaR1C1 = "A1"

'more of the same then End Sub

Answer 1

Your example data shows one header row followed by three consecutive columns of data starting from cell A2. I can not follow what the code snippet you show is trying to achieve, if I'm honest it looks unrelated to your question.

I have interpreted your question to be that: -

1. You need empty RefDes to be filled with AN (where N is an incrementing number starting from 1) from the top down.

2. You need empty RefDes to be filled with XN (where N is an incrementing number starting from 1) from the bottom up.

That is what I plan to educate to and show you, if I have missed something because it is not in the question, I have still answered the question and a new follow up question should be submitted IF you can't succeed with the changes without support.

You need two loops to occur here one to take care of the top down route, and the other to take care of the bottom up route, you'll also want a variable to hold our incrementing number.

I have added comments in the example code to show you what is happening.

Public Sub Test()
Dim LngCounter  As Long
Dim LngRow      As Long
Dim WkSht       As Excel.Worksheet

'First we connect to the worksheet we will be working ing
Set WkSht = ThisWorkbook.Worksheets("Sheet1")

    'Loop 1 - Top down
    'Our row number it the first row of data
    LngRow = 2

    'This is the incrementing counter
    LngCounter = 1

    'This loop is processing every cell from B2 down until it gets to a non-blank one
    Do Until WkSht.Cells(LngRow, 2) <> ""

        'Update the cell with A and the number
        WkSht.Cells(LngRow, 2) = "A" & LngCounter

        'Prep the next number
        LngCounter = LngCounter + 1

        'Move to the next cell down
        LngRow = LngRow + 1
    Loop


    'Loop 2 - Bottom up
    'Our row number is worked out based on the last populated row from column A
    LngRow = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row

    'This is the incrementing counter
    LngCounter = 1

    'This loop is processing every cell from the column B and last row in
    'the table upwards until it gets to a non-blank one
    Do Until WkSht.Cells(LngRow, 2) <> ""

        'Update the cell
        WkSht.Cells(LngRow, 2) = "X" & LngCounter

        'Prep the next number
        LngCounter = LngCounter + 1

        'Move to the next cell up
        LngRow = LngRow - 1
    Loop

'When we are finished we disconnect from the worksheet (free resources)
'This is good practice
Set WkSht = Nothing

End Sub

I'd like to explain WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row as it is a good line but confusing. It is finding the row number of the last populated cell from the bottom of the worksheet looking up.

Go to the bottom of the sheet (Column A): WkSht.Cells(WkSht.Rows.Count, 1)

Find the the end of blank cells (i.e. find content): .End

By looking upwards: (xlUp)

Return the row number of that find: .Row