Coding help needed

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

I can't get the portion of below code **"test and provide for blank cells"
**
to work properly. Can someone assist?
TIA,


Sub demo()

Dim rSource As Range ' the area to be "viewed"
Dim rCell As Range ' for looping through the view
Dim msg As String ' body of the message
Dim title As String ' title of the message

Set rSource = Range("D5:H15")

For Each rCell In rSource

' initialise message variables
title = "row: %R"
msg = "Address: %A " & Chr(10) & "Value: %V"

' populate message variables
title = Replace(title, "%R", rCell.Row)
msg = Replace(msg, "%A", rCell.Address)
msg = Replace(msg, "%V", rCell.Value)

' test and provide for blank cells
If rCell.Value = "" Then
msg = Replace(msg, rCell.Value, "The Cell is Blank")
End If

' show message & test for user cancel
If MsgBox(msg, vbOKCancel, title) = _
vbCancel Then
Exit For
End If
Next
End Sub
 
JMay said:
I can't get the portion of below code **"test and provide for blank cells"
**
to work properly. Can someone assist?
TIA,

' test and provide for blank cells
If rCell.Value = "" Then
msg = Replace(msg, rCell.Value, "The Cell is Blank")
End If
Click to expand...

You are trying to replace nothing with something. Nothing is nothing, so it
won't be found, therefore you cannot replace it. Just use this code

' test and provide for blank cells
If rCell.Value = "" Then
msg = msg & "The Cell is Blank"
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob for the explanation - it has added to my
<<sorely-needed>>understanding of programming (which is fairly new to me).
JMay
 
Back
Top