Checking for name in VBA with wildcard?

  • Thread starter Thread starter mrtoemoss
  • Start date Start date
M

mrtoemoss

I've got a column where occasionaly appears text of the following form:
Zone 1, Zone 2 etc.
I want to clear the cell to the right of any cell that contains this text;
however, I can't figure out how to write the code so that it will look for
Zone 1, then Zone 2 etc. Here's what I've got so far, try not to laugh, I'm
a beginner :)

Sub ClearEmpty()
Dim rMyCell As Range
Dim i As Integer
For i = 1 To 60
For Each rMyCell In Range("A1:A60")
If rMyCell.Value = "Zone" & i Then
rMyCell.Select
ActiveCell.Offset(0, 1).Value = ""
End If
Next rMyCell
i = i + 1
Next i
End Sub
 
Sub ClearEmpty()
Dim rMyCell As Range
For Each rMyCell In Range("A1:A60")
If Left(rMyCell.Value, 4) = "Zone" Then
If IsNumeric(Right(rMyCell.Value, 4,Len(rMyCell.Value, 4)-4)) Then
If Right(rMyCell.Value, 4,Len(rMyCell.Value, 4)-4) < 61 Then
rMyCell.Select
ActiveCell.Offset(0, 1).Value = ""
End If
End If
End If
Next rMyCell
End Sub
 
Thanks for the help, getting an error with the IsNumeric statement though,
I'm trying to troubleshoot.
 
This might work ok.

Option Explicit
Sub ClearEmpty2()
Dim rMyCell As Range
Dim myStr As String

For Each rMyCell In Range("A1:A60")
myStr = rMyCell.Value
If LCase(Left(rMyCell, 5)) = "zone " Then
myStr = Mid(rMyCell, 6)
If IsNumeric(myStr) Then
If CLng(myStr) < 61 Then
rMyCell.Offset(0, 1).Value = ""
End If
End If
End If
Next rMyCell
End Sub


Do you have any values like "Zone 32.3923"? If you do, then this isn't quite
right. It just uses the integer portion of that 32.3923.
 
Correction

Sub ClearEmpty()
Dim rMyCell As Range
For Each rMyCell In Range("A1:A60")
If Left(rMyCell.Value, 4) = "Zone" Then
If IsNumeric(Right(rMyCell.Value, Len(rMyCell.Value) - 4)) Then
If Val(Right(rMyCell.Value, Len(rMyCell.Value) - 4)) < 61 Then
rMyCell.Select
ActiveCell.Offset(0, 1).Value = ""
End If
End If
End If
Next rMyCell
End Sub
 
Back
Top