Having problems with a simple macro

[dan345]

Hi,

I was wondering if you could help on this problem im having. basically, i creating a very simple macro to allow the user to pick a range and count for the number of blank cells and then replace the selected range of blank cells with 0.

here is my code so far. at this moment, my code can select the range and count for blank cells, however, it cannot fill the selected range of blank cells to 0. what it does is turns all the nearest blanks cells to 0 or even outside the range.

Sub miss1()

On Error GoTo ErrorHandler

Dim Answer As String
Dim MyNote As String
Dim Holder As Object
Dim Answer2 As Integer

Set Holder = _
Application.InputBox("Input or highlight the range to check for blanks", _
"Blank Cell Counter", Type:=8)

' Set the counter to 0.
Answer2 = 0

' Count each blank cell.
For Each x In Holder
If IsEmpty(x.Value) Then Answer2 = Answer2 + 1
Next x

' Displays answer in a message box.
MsgBox "There are " & Answer2 & " blank cell(s) in this range."

Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "WARNING")

If Answer = vbNo Then
MsgBox "Missing values have been highlighted but have not been replaced!"
Else

MyNote = "Since missing values have been found, Do you want to replace?"
Range("").SpecialCells(xlCellTypeBlanks).Value = 0

End If

Exit Sub

ErrorHandler:

MsgBox "No missing data was found!"

End Sub

thanks

 

[VoG]

Hi. Try

Sub miss1()
Dim r As Range, x
On Error Resume Next
Set r = Application.InputBox("Select the range", Type:=8)
If r Is Nothing Then Exit Sub
On Error GoTo 0
x = WorksheetFunction.CountBlank(r)
If x > 0 Then
    If MsgBox("Range contains " & x & " blanks" & vbNewLine & "Replace with 0", vbQuestion + vbYesNo) = vbYes Then
        r.SpecialCells(xlCellTypeBlanks).Value = 0
    End If
Else
    MsgBox "No blanks found", vbInformation
End If
End Sub

 

[dan345]

Hi. Try

[/code]

Amazing! thank you very much.

I was also thinking, how would you allow the user to enter what they want for the replacement value.

for example, instead of replacing blank cell with 0, ask the user what they want the blank cell to have?

thanks again.

 

[VoG]

Try

Sub miss1()
Dim r As Range, x
On Error Resume Next
Set r = Application.InputBox("Select the range", Type:=8)
If r Is Nothing Then Exit Sub
On Error GoTo 0
x = WorksheetFunction.CountBlank(r)
If x > 0 Then
    If MsgBox("Range contains " & x & " blanks" & vbNewLine & "Replace with ?", vbQuestion + vbYesNo) = vbYes Then
        r.SpecialCells(xlCellTypeBlanks).Value = InputBox("Enter value")
    End If
Else
    MsgBox "No blanks found", vbInformation
End If
End Sub

 

[dan345]

Works perfectly, thanks again. :D

 

[dan345]

Try

Hi,

I have a question on this macro, how do you add colour to the cells that have been replaced by the default value, for example highlight them as yellow?

thanks.

 

[VoG]

Try

Sub miss1()
Dim r As Range, x
On Error Resume Next
Set r = Application.InputBox("Select the range", Type:=8)
If r Is Nothing Then Exit Sub
On Error GoTo 0
x = WorksheetFunction.CountBlank(r)
If x > 0 Then
    If MsgBox("Range contains " & x & " blanks" & vbNewLine & "Replace with ?", vbQuestion + vbYesNo) = vbYes Then
        With r.SpecialCells(xlCellTypeBlanks)
            .Value = InputBox("Enter value")
            .Interior.ColorIndex = 6
        End With
    End If
Else
    MsgBox "No blanks found", vbInformation
End If
End Sub

 

[dan345]

thanks :D

 

[dan345]

Try

[/code]

Hi, Im struggle with modifying the macro a little.

basically when the user selects no for entering a default value. i want the macro to just highlight the cell yellow.

thanks

 

[VoG]

Sorry, I don't understand.