snoopies
07-09-2005, 08:25 PM
Hi all,
What I'm now trying to do is...
1> Create a input box, use comma to divide values (e.g. a,b)
2> Store the input values into an array
3> Do searching in col B, for all values other then input values (a,b),
clear all its relative values in col A
Here's my problem.. I find that when I input a,b, the array contains value "b" only..but not a & b...also, it cannot clear the contents of col A..
Please kindly help, thanks a lot!
Option Explicit
Sub Testa()
Dim FirstAddress As String
Dim myArr As Variant
Dim rng As Range
Dim i As Long
Dim myArea As Variant
Dim main As String, x As Variant
Application.ScreenUpdating = False
main = InputBox("Please enter values separated by commas and no spaces(E.g. A,B,C)")
x = Split(main, ",")
myArr = Array(x)
With Selection.EntireColumn
For i = LBound(myArr) To UBound(myArr)
Set rng = .Find(what:=myArr(i), _
After:=.Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Else
'clear cells in col A for values without a or b
FirstAddress = rng.Address
Do
rng.Offset(0, -1).Clear
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Next i
End With
End If
Application.ScreenUpdating = True
End Sub
What I'm now trying to do is...
1> Create a input box, use comma to divide values (e.g. a,b)
2> Store the input values into an array
3> Do searching in col B, for all values other then input values (a,b),
clear all its relative values in col A
Here's my problem.. I find that when I input a,b, the array contains value "b" only..but not a & b...also, it cannot clear the contents of col A..
Please kindly help, thanks a lot!
Option Explicit
Sub Testa()
Dim FirstAddress As String
Dim myArr As Variant
Dim rng As Range
Dim i As Long
Dim myArea As Variant
Dim main As String, x As Variant
Application.ScreenUpdating = False
main = InputBox("Please enter values separated by commas and no spaces(E.g. A,B,C)")
x = Split(main, ",")
myArr = Array(x)
With Selection.EntireColumn
For i = LBound(myArr) To UBound(myArr)
Set rng = .Find(what:=myArr(i), _
After:=.Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
Else
'clear cells in col A for values without a or b
FirstAddress = rng.Address
Do
rng.Offset(0, -1).Clear
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Next i
End With
End If
Application.ScreenUpdating = True
End Sub