Need help on VB code

[journey]

I have a macro code I need help to modify to my needs.

This is what it does:

The macro will find data that will be entered in a cell in a worksheet. In other words the data entered will be changeable.
The macro searches the array for the data, and if present in the array it goes to the cell containing the data and erase it.

Assume the data to be located in the array is in Sheet 1, cell A1 and the array is in sheet 2 cells A1 thru D200.

Type the data into sheet1 cell
A1, then activate the macro and have it locate the matching data in sheet2 cells A1 thru D200, then clear the matching data from cells A1 thru D200.

Here is what I need it to modify to do:

I need to expand the data cell A1 on sheet 1 to A1:A200

Also I need to expand Sheet 2 array for A1 thru cz300 or entire Sheet 2

Here is the code that needs to be modified:


Sub FindIt()
Dim strFindMe As String, rngData As Range
Dim rngFound As Range, firstaddress As String

strFindMe = Worksheets("Sheet1").Range("A1").Value
'the entry To find

With Worksheets("Sheet2")
Set rngData = .Range("A1:D200")
'the range To search
Set rngFound = rngData.Find(strFindMe, LookIn:=xlValues)
If Not rngFound Is Nothing Then
firstaddress = rngFound.Address
Do
rngFound.Clear
'clear data In the matching cell
Set rngFound = rngData.FindNext(rngFound)
If rngFound Is Nothing Then Exit Do
Loop While rngFound.Address <> firstaddress
End If
End With

End Sub


your help would be greatly appreciated.

Thanks.

 

[bensonsearch]

the first page easy

strFindMe = Worksheets("Sheet1").Range("A1").Value
'the entry To find

i would set up a count

dim count as long

count = 1

do until count = 201
'all your code
loop

when you set the above change to

strFindMe = Worksheets("Sheet1").Range("A" & count).Value

the second sheet change

Set rngData = .Range("A1:D200")

to

Set rngData = .Range("A1:CZ300")

 

[journey]

Thank you for your response.

But I am really new at VB. Can you please fix the code so that I can
what you mean?

Thanks for your time and patience.

 

[bensonsearch]

Sub FindIt() 
Dim strFindMe As String, rngData As Range 
Dim rngFound As Range, firstaddress As String 
dim count as long
 
count = 1

do until count = 201


strFindMe = Worksheets("Sheet1").Range("A" & count).Value 
'the entry To find 

With Worksheets("Sheet2") 
 Set rngData = .Range("A1:CZ300") 
'the range To search 
Set rngFound = rngData.Find(strFindMe, LookIn:=xlValues) 
If Not rngFound Is Nothing Then 
firstaddress = rngFound.Address 
Do 
rngFound.Clear 
'clear data In the matching cell 
Set rngFound = rngData.FindNext(rngFound) 
If rngFound Is Nothing Then Exit Do 
Loop While rngFound.Address <> firstaddress 
End If 
End With 

loop

End Sub

 

[journey]

Thanks Benson,

I tried the revised version you sent, but it did not function properly.

The program ran in an endless loop.

-------------------------------------------------
Sub FindIt()
Dim strFindMe As String, rngData As Range
Dim rngFound As Range, firstaddress As String
Dim count As Long
count = 1

Do Until count = 201


strFindMe = Worksheets("Sheet1").Range("A" & count).Value
'the entry To find

With Worksheets("Sheet2")
Set rngData = .Range("A1:CZ300")
'the range To search
Set rngFound = rngData.Find(strFindMe, LookIn:=xlValues)
If Not rngFound Is Nothing Then
firstaddress = rngFound.Address
Do
rngFound.Clear
'clear data In the matching cell
Set rngFound = rngData.FindNext(rngFound)
If rngFound Is Nothing Then Exit Do
Loop While rngFound.Address <> firstaddress
End If
End With

Loop

Please help

 

[bensonsearch]

my appologies add the below

If rngFound Is Nothing Then Exit Do
Loop While rngFound.Address <> firstaddress
End If
End With

count = count + 1

Loop

 

[journey]

Thanks, but it seems to keep running after the data disappears.

It seems to lock up the system.

Does this suppose to happen?

I had to do a ctrl alt delete to unlock the run.

Thanks.

 

[bensonsearch]

It may take abit for it to run through that much data (seeing as we now make it go to CZ)

is there data in all those areas?

 

[journey]

I only did a test run for about 4 cells of data.

I guess it takes time for it to search all the cells even though there
may not be any data in most of them.

 

[bensonsearch]

correct, it has to run through each and every row/coloum