How do I loop this code using Excel 2007 VBA?

[SirSchiz]

Hi All,

Thanks for reading. I have a silly question. For the life of me I can not figure this simple code out. How would I state it to loop through each worksheet in the active workbook?

This code Finds a blank cell in the Used Range (Always A1:A) then for each empty cell it simply insert a value of 1.

Any help is appreciated!

Sub FindABlank()

For Each D In ActiveSheet.UsedRange
If IsEmpty(D) Then
D.Value = "1"
End If
Next
End Sub

 

[nigelk]

You probably don't even need to loop.

Try:

    Columns("A:A").SpecialCells(xlCellTypeBlanks).Select
    Selection.Value = 1

 

[Krishnakumar]

Hi,

try

Sub kTest()
    
    Dim i   As Long
    
    Application.ScreenUpdating = 0
    For i = 1 To Worksheets.Count
        With Worksheets(i).UsedRange
            On Error Resume Next
            .Columns(1).SpecialCells(4).Value = 1
        End With
    Next
    Application.ScreenUpdating = 1
    
End Sub

HTH

 

[SirSchiz]

Thanks for the reply. I've seen this before, but due to my ignorance with VBA I stumble on how to apply this?

 

[Scott R]

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
 ...

Next ws

So your code would be:

Sub FindABlank()

Dim ws As Worksheet
Dim d As Range

For Each ws In ActiveWorkbook.Worksheets
    For Each d In ws.UsedRange
        If IsEmpty(d) Then
            d.Value = "1"
        End If
    Next d
Next ws

End Sub

 

[SirSchiz]

Kris,

Perfect! Thanks! You just saved me a long day of tedious work. I hate to ask another question, but if you don't mine? How would I could I incorporate this into another Macro? I have a larger one I use to sort a different workbook, it creates a new one, and this new one is where I would run this. How can I call it to continue the process without having to run this new macro after the first completes?

 

[SirSchiz]

Scott..

Thanks, this also works very nicely! Now I'm just trying to get this macro to play nicely with another one I run before this one. Any ideas?

 

[SirSchiz]

Another question: What if the used range is already selected? Could I alter this like so?

 Columns("A:A").SpecialCells(xlCellTypeBlanks).Selection.Value = 1

 

[Krishnakumar]

Hi,

something like..

Sub Put1OnEmptyCells(ByRef OtherWbk As Workbook)
    
    Dim i   As Long
    
    Application.ScreenUpdating = 0
    For i = 1 To OtherWbk.Worksheets.Count
        With OtherWbk.Worksheets(i).UsedRange
            On Error Resume Next
            .Columns(1).SpecialCells(4).Value = 1
        End With
    Next
    Application.ScreenUpdating = 1
    
End Sub

Sub YourAnotherMacro()

    'call the procedure
    
    Put1OnEmptyCells Workbooks("OtherWorkbookName.xls")

End Sub

Note: The other workbook must be opened before run the macro

HTH