Having problems with a simple macro

dan345

New Member
Joined
Mar 6, 2011
Messages
19
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi. Try

Code:
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
 
Upvote 0
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.
 
Upvote 0
Try

Code:
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
 
Upvote 0
Try

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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
Back
Top