Usage Of Array function

[snoopies]

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

[Justinlabenne]

See if this works for you, it seems to do what you are asking:


Sub Testa2()
Dim FirstAddress As String
Dim myArr As Variant
Dim rng As Range
Dim i As Long
Dim myArea As Variant
Dim main As String
Dim x() As String
Application.ScreenUpdating = False
main = InputBox("Please enter values separated by commas and no spaces(E.g. A,B,C)")
x = Split(main, ",")
With Selection.EntireColumn
For i = LBound(x) To UBound(x)
Set rng = .Find(what:=x(i), _
After:=.Range("A" & Rows.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If 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
Application.ScreenUpdating = True
End Sub

[Jacob Hilderbrand]

I made a few corrections to your code. Try this. Let me know if you have any questions.



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
Application.ScreenUpdating = False
main = InputBox("Please enter values separated by commas and no spaces(E.g. A,B,C)")
myArr = Split(main, ",")
With Selection.EntireColumn
For i = LBound(myArr) To UBound(myArr)
Set rng = .Cells.Find(what:=myArr(i), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rng Is Nothing Then
'clear cells in col A for values without a or b
FirstAddress = rng.Address
Do
rng.Offset(0, -1).ClearContents
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> FirstAddress
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[snoopies]

Hi all,

Both codes work but .... opposite to the way that I desire ...

If I input a,b, the result should be...

Original:

1|a
2|b
3|aa
4|bb
5|c
6|a
7|b

Output:
1|a
2|b
|aa
|bb
|c
6|a
7|b

It keeps everything with values a,b in col B..
Hope this is clear :) How should I work it out?

Thanks!

[Jacob Hilderbrand]

Try this macro.


Option Explicit

Sub Testa()
Dim Main As String
Dim MyArr As Variant
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim DelRow As Boolean
Application.ScreenUpdating = False
Main = InputBox("Please enter values separated by commas and no spaces(E.g. A,B,C)")
MyArr = Split(Main, ",")
With Selection.EntireColumn
LastRow = Cells(65536, ActiveCell.Column).End(xlUp).Row
For i = 1 To LastRow
DelRow = True
For j = LBound(MyArr) To UBound(MyArr)
If Cells(i, ActiveCell.Column).Value = MyArr(j) Then
DelRow = False
Exit For
End If
Next j
If DelRow = True Then
Cells(i, ActiveCell.Column - 1).ClearContents
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[snoopies]

Very impressed! It works great, thank you so much:)