-2

I have search high and low, but I am after a vba code to search a range, ie A1:A1000, and if it finds a cell that contains a space, then to pop up with a message box.

Thanks

Community
  • 1
  • 1
TAB1980
  • 1
  • 2
  • 1
    Googling "excel vba find blank cells": [this](http://stackoverflow.com/a/14962028/1726522). Googling "excel vba message box": [this](https://msdn.microsoft.com/en-us/library/aa445082(v=vs.60).aspx). Just join both. – CMArg Jan 25 '17 at 12:53

2 Answers2

0

How about..

Set r = Range("A1:A1000").Find(What:=" ", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
If r Is Nothing Then
' code to run if nothing found
Else
 MsgBox "Space found in cell " & r.Address
End If

EDIT - removed the After: parameter as this caused issues when cell beyond search area was active.

CLR
  • 11,284
  • 1
  • 11
  • 29
0

Just an example: 

Dim cell As Range

Set cell = Range("A1:A1000").Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    Stop 'do something
Else
    Stop 'do something else
End If

More info: https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73