Searching a column based of off text

[theduckman16]

Hello -

I am having an issue with run-time 91 errors trying to search for text in a column.

Here is what I am trying to do and where my problems are occurring:
Copy text in sheet 1
go to sheet 2 and search for that text
if the text is found, my macro goes to another column, copies that value and pastes it in sheet 1 in another column.
if the text is not found, I get an error.

I have an clear error section right now, but if I get two consecutive errors the macro will stop.

Here is my code:

Sub Macro1()
'
' Macro1 Macro
'

'
For i = 2 To Rows.Count
    Dim j As String
    Dim k As Range
    
On Error GoTo Err_Clk

    If ActiveCell.Value = "" Then
    ActiveCell.Offset(1, 0).Select
    
    End If
    j = ActiveCell.Value
    Selection.Copy
    ActiveSheet.Next.Select
    Range("A1").Select
    Cells.Find(What:=j, After:=ActiveCell _
        , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate 

    ActiveCell.Offset(0, 7).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ActiveSheet.Previous.Select
    ActiveCell.Offset(0, -9).Select

    ActiveSheet.Paste
    ActiveCell.Offset(1, 9).Select
    
    
Err_Clk:
If Err.Number <> 0 Then
    Err.Clear
    ActiveSheet.Previous.Select
    ActiveCell.Offset(1, 0).Select

End If

    
Next i

End Sub

Thank you for your help

 

[theduckman16]

Is there a small fix to this or do I have re-organize my entire code?

 

[theduckman16]

bump

 

[theduckman16]

Any ideas? Help?

 

[Peter_SSs]

... do I have re-organize my entire code?

That is what I would do. Selecting sheets and cells is a relatively slow process so slows your code. It is rare in vba that you actually have to select something to work with it.

Also, processing every row (For i = 2 to Rows.Count) in the whole sheet will be wasting a lot of time unless you are using all or nearly all the rows - which again is rare.

There are a couple of things I wasn't sure about what you are trying to do but hopefully this will get you close.

1. I am assuming that you are working with one particular column in Sheet1. I have guessed column K but that is easy to change in my code.

2. I have assumed you are really searching in one particular column in Sheet2 and I have guessed column A. Again easy to change. If you are actually searching the whole sheet, a further slight variation will be required.

3. Note the method to deal with the text not being found. Instead of trying to activate the cell, set a range variable (Found in my code) to the Find result and then test to see if that Range variable is 'Nothing' or not.

Anyway, give this a try in a copy of your workbook.

I suggest stepping through the code with F8 so you can get an idea of what is happening and that will give you a better chance of making any changes required. However, if you need further help, post back with more details.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> theduckman()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRwSh1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range, Cel <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> myColSh1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "K"  <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> FirstRwSh1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN> = 2    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> myColSh2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A"  <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1")<br>        LastRwSh1 = .Range(myColSh1 & .Rows.Count).End(xlUp).Row<br>        <SPAN style="color:#00007F">Set</SPAN> myRange = .Range(myColSh1 & FirstRwSh1 & ":" & myColSh1 & LastRwSh1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2").Columns(myColSh2)<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> myRange<br>            j = Cel.Value<br>            <SPAN style="color:#00007F">If</SPAN> Len(j) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=j, After:=.Cells(1, 1), _<br>                    LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext, _<br>                    MatchCase:=False, SearchFormat:=False)<br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    Cel.Offset(0, -9).Value = Found.Offset(0, 7).Value<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Dryver14]

Cel ? Peter

are you saying the last L is not required... I could save seconds...

Just kidding.

I mean it though...the last L is not required?

 

[Peter_SSs]

Cel is just a variable name that I happened to choose.
Just like LastRwSh1 is a variable name that didn't need to be LastRowSheet1.

I often use Cel just to keep it a bit further away from the reserved word Cells

 

[Dryver14]

I see,

I thought you were using it in place of the word cell or does it not exist and I ahve always been using a variable and did not know it as you seem to use cel where i use cell

 

[Peter_SSs]

Cell is not a special word in vba so it sounds like you may have been using Cell as a variable without knowing it. If that is the case then it would seem that you do not specifically declare all your variables with Dim statements.

In my opinion that is a bad idea and I always have the statement 'Option Explicit' at the start of all my modules. That forces you to declare all variables and has the added advantage that if you subsequently accidenatlly mis-spell one in your code, the de-bugger will quickly point that out to you.

You can have Option Explicit automatically appear at the start of your modules by (in the VB window)
Tools|Options|Editor tab|Require Variable Declaration

Note, though that Cell is a perfectly valid variable name, it is just one I generally choose not to use.

More good information about this here: http://www.cpearson.com/excel/DeclaringVariables.aspx

 

[Dryver14]

Peter,

I do declare variables as I use option explicit as instructed by my MR Excel DVD's

So I normally declare it as a range,

It just never crossed my mind it was a variable I literally thought it meant CELL !!

Another day of my life not wasted as I have learned something.