Do Until Loop Dilemma

Hi All!

I have some code that will loop through a range of cells in column "F"
to find blank cells and if there are any to populate that cell with a
"0" value which it does BUT.. I am not sure how to add into my code
that I want the loop only to continue as long as the cell value in col.
"A" is not blank. At the moment the code will loop continuously until
all blank cells in col "F" are populated with a "0" which takes ages!!

Please help as I am unsure of the code needed here!! Thanks!!

Dim StartRow1 As Long
Dim myRng As Range
Dim FoundCell As Range
Dim WhatToFind As String
WhatToFind = ""

With ActiveSheet
StartRow1 = 2
Set myRng = .Range("F:F")

With myRng
Do

Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do 'done
End If

FoundCell = "0"

Loop
End With
End With

You can use select a range and do Edit|goto|special|blanks to get that range of
empty cells.

In code it could look like:

Option Explicit
Sub testme()
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
'using column A to get the last used cell
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("F1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells in column F!"
Exit Sub
End If

myRng.Value = 0
End With

End Sub

Or if you only wanted the blank cells in column F that had something in column
A:

Option Explicit
Sub testme2()
Dim myRng As Range
Dim wks As Worksheet
Dim myCell As Range

Set wks = ActiveSheet
With wks
'using column A to get the last used cell
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("F1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells in column F!"
Exit Sub
End If

For Each myCell In myRng.Cells
If IsEmpty(.Cells(myCell.Row, "A").Value) Then
'do nothing
Else
myCell.Value = 0
End If
Next myCell
End With

End Sub

(I was kind of confused about which you wanted.)


mellowe wrote:
>
> Hi All!
>
> I have some code that will loop through a range of cells in column "F"
> to find blank cells and if there are any to populate that cell with a
> "0" value which it does BUT.. I am not sure how to add into my code
> that I want the loop only to continue as long as the cell value in col.
> "A" is not blank. At the moment the code will loop continuously until
> all blank cells in col "F" are populated with a "0" which takes ages!!
>
> Please help as I am unsure of the code needed here!! Thanks!!
>
> Dim StartRow1 As Long
> Dim myRng As Range
> Dim FoundCell As Range
> Dim WhatToFind As String
> WhatToFind = ""
>
> With ActiveSheet
> StartRow1 = 2
> Set myRng = .Range("F:F")
>
> With myRng
> Do
>
> Set FoundCell = .Cells.Find(What:=WhatToFind, _
> After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
> LookAt:=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False)
>
> If FoundCell Is Nothing Then
> Exit Do 'done
> End If
>
> FoundCell = "0"
>
> Loop
> End With
> End With

--

Dave Peterson

Brilliant!! Thanks Dave wanted the blank cells in column F that had
something in column
A so the latter code worked perfect!