conditional find and replace

bluebird1927

New Member
Joined
Aug 17, 2007
Messages
1
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
Hello Mike, welcome to the board.
Assuming you're looking for a vba solution, here's somethng you can try.
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,452
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top