Finding a number and debug problem

[jay]

I recorded a macro and fails.
When I do a find or a search and replace, I notice that if
it can't find the number it goes into debug.
Is there away to search for a number in a column and
select the row it is in so I can copy the record with out
the debug running when it can't find a number?

 

[Dave Peterson]

It's usually better to check to see if it's found in your code first.

dim FoundCell as range
dim myVal as long
myval = 23 'or something
with activesheet.range("a:a")
Set FoundCell = .Find(What:=myVal, After:=.Cells(.cells.count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if foundcell is nothing then
'it wasn't found!
else
foundcell.entirerow.copy _
destination:=whereyouwantitogo!
end if
end with

 

[jay]

It's usually better to check to see if it's found in your code first.

dim FoundCell as range
dim myVal as long
myval = 23 'or something
with activesheet.range("a:a")
Set FoundCell = .Find(What:=myVal, After:=.Cells(.cells.count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if foundcell is nothing then
'it wasn't found!
else
foundcell.entirerow.copy _
destination:=whereyouwantitogo!
end if
end with

I tried it and it does not find any numbers.
The number is in column A and 8183.
It gets to if foundcell is nothing then and jumps to end if.
I see the number in the A column.

Don't know why it is not finding them.
I tried changing the format of the A column to text and numbers.
Nothing.
What could it be?
Why does the cell not jump to the number?

 

[jay]

I tried it and it does not find any numbers.
The number is in column A and 8183.
It gets to if foundcell is nothing then and jumps to end if.
I see the number in the A column.

Don't know why it is not finding them.
I tried changing the format of the A column to text and numbers.
Nothing.
What could it be?
Why does the cell not jump to the number?

It is now working.
The solution was....

myval = "23"

my search numbers are in the form as a39393, x32232, i23222 and so on.

Not only that but I have 40 numbers to change ever day and now I have
a variable list of p1 to p40 instead of the myVal.
It has been done with very little code. Very simple

Thank you.

 

[Dave Peterson]

I think if I had to do 40 changes, I'd use a macro that uses replace.

I'd create a new worksheet (named Table).

Column A contains the From strings.
column B contains the To strings.

Then activate your real sheet and run this:

Option Explicit
Sub testme02()

Dim myTable As Variant
Dim iCtr As Long

With ThisWorkbook.Worksheets("Table")
myTable = .Range("a1:B" & _
.Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
.Replace What:=myTable(iCtr, 1), _
Replacement:=myTable(iCtr, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End With

End Sub

(Watch the xlWhole stuff. I'm not sure if you need that or xlPart.)

 

[Dave Peterson]

There was another post in google where Jay wrote that he got it working.

But mentioned that he had to do a bunch of replaces.

 

[jay]

I ran across another problem.
I can manually select a row and copy from sheet1 to
sheet2, but when I try to run a macro to copy
and paste between sheets it pops up the old "Debug".
I would think this would be a simple procedure, but
no. It took all day trying to get a simple copy and
paste.

Would you know how to do it?

 

[Dave Peterson]

It could be lots of things. You should post your code and indicate the
offending line.

 

[jay]

FoundCell.EntireRow.Copy
Sheets("sheet2").Select
Range("A1").EntireRow.Select
Selection.Paste

I can see the sheet1 row copy and
the sheet2 row get selected, but when
it goes to paste the debug goes.
Maybe there is another paste method.

 

[Dave Peterson]

How about just:

foundcell.entirerow.copy _
destination:=worksheets("sheet2").range("a1")

But you could .pastespecial to a cell or you could paste to a sheet:

FoundCell.EntireRow.Copy
Sheets("sheet2").Select
Range("A1").EntireRow.Select
ActiveSheet.Paste
'(too much selecting!)

But the first version is what I'd use.

 

[jay]

My last problem.
Is there away to use a variable like i and replace
Set foundCell = .Find(What:=p1 with pi

and

Destination:=Worksheets("Sheet2").Range("A2") with ("Ai")
and loop it so pi/Ai will cycle from p1 to p40?
This would reduce my code to a very small amount.
Otherwise, I would have to reproduce the code 40 times!
I tried the variable i with a loop and it failed.
All i need now is to cycle from 1 to 40.



Sub Macro1()
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\user\Desktop\data.txt",
Destination:=Range("A1" _
))
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1)
.Refresh BackgroundQuery:=False
End With

Dim FoundCell
'Update this List of numbers as needed.
p1 = "o305"
p2 = "o983"
p3 = "o606"
Upto p40



With ActiveSheet.Range("a:a")
Set FoundCell = .Find(What:=p1, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'can't find the number
Else
FoundCell.EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A2")
End If
End With



End Sub

 

[Dave Peterson]

I posted this earlier.

I think if I had to do 40 changes, I'd use a macro that uses replace.

I'd create a new worksheet (named Table).

Column A contains the From strings.
column B contains the To strings.

Then activate your real sheet and run this:

Option Explicit
Sub testme02()

Dim myTable As Variant
Dim iCtr As Long

With ThisWorkbook.Worksheets("Table")
myTable = .Range("a1:B" & _
.Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
.Replace What:=myTable(iCtr, 1), _
Replacement:=myTable(iCtr, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
End With

End Sub

(Watch the xlWhole stuff. I'm not sure if you need that or xlPart.)

===========

Put all your from's/to's in columns A:B of a worksheet in the workbook that
contains the macro.

Then you can pick up all the values in those two columns and cycle through them.

Notice that you're now using .Replace instead of .Find.

 

[jay]

It is working now. I could not get it to work before, but
I know more now. Thanks


Do you know how to edit this section and make it select
copy and paste to another sheet?
Instead of replacing the cell, copy the row it is in and
paste to other sheet in the order of the row it was found.
This would be the best way.


With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
.Replace What:=myTable(iCtr, 1), _
Replacement:=myTable(iCtr, 2), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)




I tried this but it fails.


With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
Cells.Find(What:=myTable(iCtr, 1),After:=ActiveCell,
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False).Copy, _
Destination:=Worksheets("Sheet2").Range("aiCtr")
'instead of a1 use aiCtr

 

[Dave Peterson]

I'm not sure what aiCtr is, but I bet it doesn't want double quotes around it!

maybe...

Dim FoundCell as range
dim iCtr as long

With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
set foundcell = .Cells.Find(What:=myTable(iCtr, 1), _
After:=.cells(.cells.count), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)
if foundcell is nothing then
'it wasn't found
else
foundcell.entirerow.copy _
Destination:=Worksheets("Sheet2").Range("a" & iCtr)
end if
next ictr
end with

(composed in the email--so watch out for typos!)

 

[jay]

It works great. Thanks.
I sent a response and apearantly it did not get here.

 

[Dave Peterson]

Got it now. Glad it worked.

It works great. Thanks.
I sent a response and apearantly it did not get here.

<<snipped>>

 

[jay]

Just one more time.
I think I can loop this and make it a smaller macro that is
doing a huge amount of work.
Is there a way to reset the z variable? I don't know how.



GoTo MyLoop1
1:
z = "Joe"
Dim FoundCell As Range
Dim myTable As Variant
Dim iCtr As Long
GoTo Loop1
2:
z = "George"
GoTo Loop1
3:
z = "Judy"
GoTo Loop1

and so on up to 48 employees in my case.


MyLoop1:
GoTo 1
Loop1:

    With ThisWorkbook.Worksheets("Table")
    myTable = .Range("a1:B" & _
                .Cells(.Rows.Count, "A").End(xlUp).Row).Value
    End With

     With ActiveSheet.Range("a:a")
         For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
            set foundcell = .Cells.Find(What:=myTable(iCtr, 1), _
                  After:=.cells(.cells.count), _
                  LookAt:=xlWhole, SearchOrder:=xlByRows, _
                  MatchCase:=False)
            if foundcell is nothing then
                'it wasn't found
            else                
                foundcell.entirerow.copy _
                  Destination:=Worksheets("z").Range("a" & iCtr)
            end if
          next ictr
     end with

GoTo 2

End Sub

 

[jay]

Just one more time.
I think I can loop this and make it a smaller macro that is
doing a huge amount of work.
Is there a way to reset the z variable? I don't know how.



GoTo MyLoop1
1:
z = "Joe"
Dim FoundCell As Range
Dim myTable As Variant
Dim iCtr As Long
GoTo Loop1
2:
z = "George"
GoTo Loop1
3:
z = "Judy"
GoTo Loop1

and so on up to 48 employees in my case.


MyLoop1:

Sorry I meant the Dim iCtr
Is there a way to reset the iCtr?

 

[Dave Peterson]

You could put all 48 names in an array and loop through them:

dim myNames as Variant
dim iCtr as long

mynames = array("joe","jim","helen","mary")

with yourRange
for ictr = lbound(mynames) to ubound(mynames)
'do your stuff against each name
set foundcell = .cells.find(what:=mynames(ictr), ...
'and continue doing what you want
next ictr
end with

But for 40 names, I think I'd use a worksheet and just put them in a column. I
think it would be easier to update when things change.

That's how that earlier post in this thread worked with the replace:

(older post modified)

Option Explicit
Sub testme02()

Dim myTable As Variant
Dim iCtr As Long
Dim FoundCell as Range

With ThisWorkbook.Worksheets("Table")
myTable = .Range("a1:A" & _
.Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)
set foundcell:=.cells.find(What:=myTable(iCtr, 1), 'and so on
'and keep going
Next iCtr
End With

End Sub

<<snipped>>

 

[jay]

Ok I will work on it.
What about the Dim iCtr?
I put each employee's lookup numbers in columns in the table sheet.
Ictr changes with each loop.
Maybe put their myTable = .Range("a1:A" & _ in a column next to
there names and use another array to change their name and iCtr?
That would be better.

joe a1:A
jim b1:B
helen c1:C
mary d1

Then just edit these two columns.
Could this be done too?