Finding a number and debug problem

  • Thread starter Thread starter jay
  • Start date Start date
It fails at myTable1 = .Range(myColumn & _


'I am using 3 columns o,p,q for names,
'myTable is column O with 48 employees names used for sheet names.
'myTable1 is used
'myColumn is column P with the ranges

Dim myTable As Variant
Dim myTable1 As Variant
Dim myColumn As Variant
Dim myColumn1 As Vairant 'is column Q used for changing row for counting.
Dim iCtr As Long
Dim i As Long 'used for inside loop to copy found records to employee sheet.
Dim FoundCell As Range

With ThisWorkbook.Worksheets("Table")
myTable = .Range("o1:o" & _
.Cells(.Rows.Count, "O").End(xlUp).Row).Value
myColumn = .Range("p1:P" & _
.Cells(.Rows.Count, "P").End(xlUp).Row).Value
myColumn1 = .Range("q1:Q" & _
.Cells(.Rows.Count, "Q").End(xlUp).Row).Value
End With
With ActiveSheet.Range("a:a")
For iCtr = LBound(myTable, 1) To UBound(myTable, 1)


With ActiveSheet.Range("H:H")

'Sheets(myTable).Select
Cells.Select
' Selection.ClearContents
Sheets("Sheet1").Select
With ThisWorkbook.Worksheets("Table")
myTable1 = .Range(myColumn & _
.Cells(.Rows.Count, "mycolumn1").End(xlUp).Row).Value
End With
With ActiveSheet.Range("H:H")
For i = LBound(myTable1, 1) To UBound(myTable1, 1)
Set FoundCell1 = .Cells.Find(What:=myTable1(iCtr, 1), _
After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCell1 Is Nothing Then
Else
FoundCell1.EntireRow.Copy _
Destination:=Worksheets(myTable).Range("a" & iCtr)
End If
Next i
'Next iCtr
End With
 
I'm kind of lost at what you're trying to do.

Maybe just explaining what your data looks like and what you're trying to do
would help.

<<snipped>>
 
This has been a very hard procedure to explain.
I have to learn the VB codes at the same time and
try to figure out how these procedures work.
They are very interesting.
I guess what I am trying to get at is a Loop with
an inner Loop.
The first loop you gave me was great it works
However, I have to make 12 to 40 of them. One for
each employee.
I thought of doing an outer loop with an inter loop to
reduce it to one procedure for all.
That would be cool, but if not you got it working for
me.
I would think if this last problem will work, it will
be the smallest code doing a large amount of work.

I don't think it could get any smaller than at this
point.




THIS IS THE IMPORT SHEET WHERE THE DATA GOES
Sheet1
A B C
X1323 JOE 123 10TH AVE
Y5422 SUE 45 SATURN BLVD
a1922 JIM 8988 CENTER CT.

up to 3000 records




THIS IS THE EMPLOYEES WITH THEIR OWN COLUMN WITH DATA
TO BE LOOKED UP.

sheet Table
JOE SUE JIM

X3485 A7766 X9993
X4885 X9986 Y2322
Y5969 Y9333 X2333

UP TO 12 SHEETS FOR RIGHT NOW




THIS IS THE SHEETS WHERE EACH RECORD IS TO BE COPIED.
SHEET JOE


SHEET SUE


SHEET JIM
 
It looks like you could just use the second column of the input range to
determine where to move the data:

A B C
X1323 JOE 123 10TH AVE
Y5422 SUE 45 SATURN BLVD
a1922 JIM 8988 CENTER CT.

I don't see why you'd have to look at the data in column A at all.

If that's true, you could some kind of filter (like data|autofilter) to show
just the "Joe's" and copy those visible cells to the bottom of worksheet Joe.

In fact, Debra Dalgleish has a couple of workbooks that use advanced filter to
do this kind of thing at:
http://www.contextures.com/excelfiles.html

Look for:

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- creates a list of unique items,
creates a sheet for each item, then replaces old data with current.
AdvFilterCity.xls 46 kb

and

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

======
And as a learning exercise, this would be pretty good. You'll find that this
way of lifting and separating data is pretty darn fast--much faster than looping
through a bunch of finds.

Or did I miss the point (again!).
 
jay said:
Sorry I meant the Dim iCtr
Is there a way to reset the iCtr?
Click to expand...
Dave I ran into another problem.
I troubleshooted it down to the lookup column.

The following macro works with everything, but
one issue.

If I type in manually the the data into "mycolumn1" Column
it will copy and paste record rows.
If I import from another excel file it will not copy
and paste records.
I had changed the format to general, txt, and numbers and
all have failed to copy and paste

It finds the data, but it will not do this command line.
Only if I type manually the data into the "mycolumn1" Column.
Do you have any idea what it could be?

     Destination:=Worksheets(myTable).Range("a" & iCtr)






Sheets(myTable).Select
         Cells.Select
'         Selection.ClearContents
         Sheets("Sheet1").Select
With ThisWorkbook.Worksheets("Table")
         myTable1 = .Range(myColumn & _
               .Cells(.Rows.Count, "mycolumn1").End(xlUp).Row).Value
    End With
     With ActiveSheet.Range("H:H")
         For i = LBound(myTable1, 1) To UBound(myTable1, 1)
            Set FoundCell1 = .Cells.Find(What:=myTable1(iCtr, 1), _
                  After:=.Cells(.Cells.Count), _
                  LookAt:=xlWhole, SearchOrder:=xlByRows, _
                  MatchCase:=False)
            If FoundCell1 Is Nothing Then
            Else
                  FoundCell1.EntireRow.Copy _
                  Destination:=Worksheets(myTable).Range("a" & iCtr)
            End If
        Next i
'Next iCtr
End With






 
First, what are those  characters.

It's difficult to tell from just this portion of code, but...

This looks funny:

myTable1=.Range(myColumn & _
.Cells(.Rows.Count,"mycolumn1").End(xlUp).Row).Value

I bet mycolumn1 doesn't belong in double quotes. It's a variable in your code.

but I'm not sure what they represent.


Destination:=Worksheets(myTable).Range("a"&iCtr)

Sheets(myTable).Select
Cells.Select
'Selection.ClearContents
Sheets("Sheet1").Select
With ThisWorkbook.Worksheets("Table")
myTable1=.Range(myColumn&_
.Cells(.Rows.Count,"mycolumn1").End(xlUp).Row).Value
EndWith
With ActiveSheet.Range("H:H")
For i= LBound(myTable1,1) To UBound(myTable1,1)
SetFoundCell1 = .Cells.Find(What:=myTable1(iCtr,1),_
After:=.Cells(.Cells.Count),_
LookAt:=xlWhole,SearchOrder:=xlByRows,_
MatchCase:=False)
If FoundCell1 Is Nothing Then
Else
FoundCell1.EntireRow.Copy_
Destination:=Worksheets(myTable).Range("a"&iCtr)
End If
Next i
'Next iCtr
End With
Dave I ran into another problem.
I troubleshooted it down to the lookup column.

The following macro works with everything, but
one issue.

If I type in manually the the data into "mycolumn1" Column
it will copy and paste record rows.
If I import from another excel file it will not copy
and paste records.
I had changed the format to general, txt, and numbers and
all have failed to copy and paste

It finds the data, but it will not do this command line.
Only if I type manually the data into the "mycolumn1" Column.
Do you have any idea what it could be?
Click to expand...
<<snipped>>
 
Dave said:
First, what are those  characters.

It's difficult to tell from just this portion of code, but...

This looks funny:

myTable1=.Range(myColumn & _
.Cells(.Rows.Count,"mycolumn1").End(xlUp).Row).Value

I bet mycolumn1 doesn't belong in double quotes. It's a variable in your
code.

but I'm not sure what they represent.


Destination:=Worksheets(myTable).Range("a"&iCtr)

Sheets(myTable).Select
Cells.Select
'Selection.ClearContents
Sheets("Sheet1").Select
With ThisWorkbook.Worksheets("Table")
myTable1=.Range(myColumn&_
.Cells(.Rows.Count,"mycolumn1").End(xlUp).Row).Value
EndWith
With ActiveSheet.Range("H:H")
For i= LBound(myTable1,1) To UBound(myTable1,1)
SetFoundCell1 = .Cells.Find(What:=myTable1(iCtr,1),_
After:=.Cells(.Cells.Count),_
LookAt:=xlWhole,SearchOrder:=xlByRows,_
MatchCase:=False)
If FoundCell1 Is Nothing Then
Else
FoundCell1.EntireRow.Copy_
Destination:=Worksheets(myTable).Range("a"&iCtr)
End If
Next i
'Next iCtr
End With

<<snipped>>
Click to expand...





I have a spreedsheet with 10 columns
I import this spreadsheet into my main excel file that has
several macros to do several jobs.
I run macro to lookup cells in sheet1 column C.
After those records are copied to another sheet I use column A
to lookup cells in the first sheet.
Everything works except I have to type in the numbers to column A to
look them up in sheet1. It will work.
My question is why does it not copy records with out me having to
type them in?
Why does it not copy records without me having to type the numbers
myself?
 
I'm not sure what your question is, but if it's a lookup not working, many times
it's because in one spot, it's text and in the other, it's a real number.

'123 is different than 123

And typing numbers might be changing them to numbers.
 
That is what is happening, but I changed the whole spreadsheet to
Text and it still will not go.
I will keep trying to get it to work.
 
Just changing the format isn't enough. It doesn't change the existing numeric
entries to text.

One way to change them:

Select your range
and run this macro:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants,
xlNumbers))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "select a range with real numbers!"
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
.Value = "'" & .Value
'.value = "'" & .text
End With
Next myCell

End Sub

The apostrophe will mean that xl will treat the cell as text.

If the cells had a special format (not general), you may want to use this line:

..value = "'" & .text
(and comment out the other.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

======
An alternative may be to convert all your text numbers to number numbers.

Copy an empty cell.
Select your range of text numbers
edit|paste special
check the Add operation.

(you could use a custom number format to keep leading 0's.)
 
Watch out for linewrap!

Dave said:
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants,
xlNumbers))
Click to expand...


xlnumbers) should be on the previous line
 
The Rang I use do not have Real Numbers.

A

C3924
X9867
Q5677
AND SO ON.....


That must me the problem.
I can not use real numbers because the first
digits are text.
Do I have to use real numbers?
Can I use Real Text?
 
The Column that I am searching in has the format
Custom V-3000


Column
A
C3924
X9867
Q5677
AND SO ON.....


I ran the procedure and it only works
if I type in the real number with no first digit text.
I have to have it in real text I believe.
I can't believe it has to be this hard just to do a simple
lookup.
 
If your lookup value looks like "c3924" and the range you're search through
looks like "c-3924", then maybe you could use this kind of worksheet formula:

=vlookup(left(c2,1)&"-"&mid(c2,2,255),sheet2!$a:$z,3,false)

or looking back at the thread...
SetFoundCell1 = .Cells.Find(What:=myTable1(iCtr,1),_
After:=.Cells(.Cells.Count),_
LookAt:=xlWhole,SearchOrder:=xlByRows,_
MatchCase:=False)
Click to expand...

Set foundcell = .cells(.find(what:=left(mytable1(ictr,1),1) _
&"-"&mid(mytable1(ictr,1),2),
after:=......

I'm kind of lost on where we are.
 
Dave said:
If your lookup value looks like "c3924" and the range you're search
through looks like "c-3924", then maybe you could use this kind of
worksheet formula:
Click to expand...


No dash. It is C3924 = lookup value
C3924 = Search through

I am importing an excel spreadsheet into my main SpreadSheet and using
the A column in my main spreadsheet to look up in a column in the imported
column in my main spreadsheet.
It is not easy trying to explain all this. It is complicated.

The main problem is I can only find a cell if I type in C3924 and all
other numbers. Man, that is a lot of typing. It is not doable.

I know the problem is not in any formulas I have been given. They work
fine.

I open an excel file, select cells, copy, and paste into my main
spreadsheet. It does not work. It will not find the number unless I
manually type in the C3924.
This is a weird problem. I can't believe I am the only one having this
problem.
 
jay said:
No dash. It is  C3924 = lookup value
C3924 = Search through
Click to expand...
No dash. It is  



C3924 = lookup value I tried all kinds of formats
C3924 = Search through imported column from other spreadsheet
it has formating ...V-3000
I changed it to all kinds of formats

However, you said I can't do it this way. It must be done in a more
difficult way.

Please don't give up on me yet. We came along way to get to this point.
Maybe Office 2000 will not alow me to import and look up this way.
Maybe this is the end of the road.
Do you know if my macros for Office 2000 Excel will work with Office Xp?
My company will be upgrading to Office XP.
 
Chip Pearson has an addin that allows you to inspect a cell and look at it
character by character.

http://www.cpearson.com/excel/CellView.htm

You could use that to see what the differences are (compare the two cells that
you think are the same).

And it looks like you're posting in HTML. It makes reading your details
difficult.

I see a bunch of Â's and I'm not sure what they really mean.

And you may want to double check your formulas/code to verify that you're
checking the right cell against the right column.

And I would think that you'd have very few problems (probably none) when you
upgrade to xl2002. But a problem may occur if you (as a developer) upgrades
first.

There are new features offered in every new version of excel. If by chance, you
develop a tool that uses a new feature, then the xl2k users would be in trouble.

You may want to have both versions loaded or be the last (ouch) to upgrade.
 
Dave said:
Chip Pearson has an addin that allows you to inspect a cell and look at it
character by character.
Click to expand...
Can me send an example of those A's to me?
I am a Suse Linux User. I am using Knode for my newsgroups and I am not
sure what to do about the Html issue. I looked in the configuration and
didn't see any adjustment for html.
 
I used the cellview and there are no special characters, just decimals.
I know I have the column locations right. It looks like general format is
set right.

I copied column A about 10 rows of peoples last names. It worked with the
names.
I copied the column with the C3944 numbers and did a search. it failed.
So, it works with last name cells, but not with the number cells.
Now we know it is in the number column and we know there are no special
characters.
Strange.
 
Back
Top