conditional find and replace

[bluebird1927]

Hi, i'm trying to find and replace all blanks in column B based on the value in column A. When it replaces the blank it will be a few letters and a number that increments e.g AN1 AN2 AN3 etc
When Column A is blank the code stops.

when

For example
______A____ B
1 ___text1 textA - fine move to next row
2 ___text2 textB - fine move to next row
3 ___text3 ( ) - replace this blank with AN1
4 ___text4 ( ) - replace this blank with AN2 and so on
5 ___( ) - when Column A is blank, code stops.

I have tried a few things but cannot get it to loop correctly.

Please help

Mike

 

[HalfAce]

Hello Mike, welcome to the board.
Assuming you're looking for a vba solution, here's somethng you can try.

Sub AnTheBlanksInColB()
Dim LstRwA As Long, AnCount As Long, _
    Blnks As Range, Blnk As Range
AnCount = 1
With Sheets("Sheet1")
  LstRwA = .Cells(Rows.Count, "A").End(xlUp).Row
  On Error GoTo Quit
  Set Blnks = .Range("B1:B" & LstRwA).SpecialCells(xlCellTypeBlanks)
  For Each Blnk In Blnks
    Blnk.Value = "AN" & AnCount
    AnCount = AnCount + 1
  Next Blnk
End With
Quit:
End Sub

Hope it helps.