Macro to find spaces?

[Lostguy]

Hello.

Hello!

My spreadsheet error-checking macro didn't pass the test today. Here's
the situation:

There are several cells in the range where I tell the user to leave
them blank if they do not apply. So for the formulas for these, I can
just use If(d2=""...).

But if the user hits the space bar, then the cell is no longer blank,
even though it looks like it is.

So, the macro needs to check the range of cells (D234). If any of
the cells have only spaces and no other characters, then end the macro
and put up a message box that says "The following cells are not blank:
d3, d16. Please delete the spaces and then press the Print button."


Any help with this macro appreciated!

VR/Lost

 

[Lostguy]

All,

I think this formula works for one cell, but getting it into a macro
for a range of cells with the right syntax is where I have the
problem:

=IF(LEN(TRIM(D2))<>0,"Good","Bad")

VR/Lost

 

[Dave Peterson]

I wouldn't bother the user (except to chastise them!).

Instead I'd just fix those cells.

Option Explicit
Sub testme()
Dim myRng As Range
Dim iCtr As Long
Dim MaxSpacesToFix As Long

MaxSpacesToFix = 10

With Worksheets("Sheet1")
Set myRng = .Cells 'or some specific range like .Range("A1:X99")
End With

For iCtr = 1 To MaxSpacesToFix
myRng.Replace what:=Space(iCtr), _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlNext, _
MatchCase:=False
Next iCtr

End Sub

Adjust the maxspacestofix to what you know(?) it can't exceed.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Lostguy]

Sir,

That works, but like you said, it's the program fixing the problem,
but for this application, the user needs to go back to that cell and
fix it. I just wanted to help the user out by letting them know which
cell(s) has the problem.

Here's my start, but it doesn't work.

Sub Button1_Click()

Dim rng As Range
Set rng = Range("D234")
For Each cell In rng
If Len(Trim(cell)) <> 0 Then
MsgBox "Bad"
GoTo enditall
End If
Next
enditall:
End Sub

 

[Lostguy]

All,

This is what I have so far, but it doesn't list the offending cell
number in the msgbox
??

Sub Button1_Click()
Dim cell As Range
For Each cell In Range("D234")
If Len(Trim(cell.Value)) = 0 Then
MsgBox "bad"
cell.Select
GoTo enditall
End If
Next
enditall:
End Sub

VR/Lost

 

[Ron Rosenfeld]

All,

This is what I have so far, but it doesn't list the offending cell
number in the msgbox
??

Sub Button1_Click()
Dim cell As Range
For Each cell In Range("D234")
If Len(Trim(cell.Value)) = 0 Then
MsgBox "bad"
cell.Select
GoTo enditall
End If
Next
enditall:
End Sub

VR/Lost

MsgBox("Bad Cell " & cell.address)

But I would just either use Data Validation to make sure the correct entry is
made; and/or conditional formatting to "light up" the one's with bad entries.
--ron

 

[Dave Peterson]

I still would just correct the problem. I don't see what's gained by asking the
user to fix it manually.

Option Explicit
Sub Button1_Click()

Dim rng As Range
Dim Cell As Range

Set rng = ActiveSheet.Range("D234")

For Each Cell In rng.Cells
If Len(Cell.Value) > 0 Then
'there's something in it
If Len(Trim(Cell.Value)) <> 0 Then
MsgBox "Bad: " & Cell.Address(0, 0)
Exit For
End If
End If
Next Cell

End Sub