Finding a number and debug problem

  • Thread starter Thread starter jay
  • Start date Start date
I'm confused. Are there just numbers or do you really have a leading alphabetic
character?

C3944 or 3944???

I'm still confused on what you're doing.

How are you doing this--via code or worksheet functions?

You may want to post the formula or snippet of code that you're using.

And
 
Dave said:
I'm confused. Are there just numbers or do you really have a leading
alphabetic character?

C3944 or 3944???
Click to expand...

Yes they all have leading alphabetic character.

C9382
Z8382
q9877

up to 3000 rows.

The problem is in the copy and paste of column A.
Column B works with copy and paste. I can search and find names with
column B.
Only the column A with the C9388 format will not work with any thing I
have tried.

Dave I had two years of computer programming in my college days and work
with DOS Batch Programming. I have learned allot so far with VB to move
cells around. I learned allot just watching the code run from examples.

I will put my macro together and post it.






I'm still confused on what you're doing.

How are you doing this--via code or worksheet functions?

You may want to post the formula or snippet of code that you're using.

And
Click to expand...






I don't know what these A characters are or why they are
there. It must be internet traffic junk.
 
Step1
This is the Employee excel file that is copied and
pasted to my main spreadsheet called "EmployeeDat"'
This Sheet is used to search in.
A column is formated with custom V-3000 when
given to me.

A B C D
C05565 MORRIS 03/03/04 2.9
C12429 SMITH 06/28/04 2.8
C13247 JACKSON 06/04/04 7.2
Z33497 SMITH 06/28/04 7.6
C24302 MINGO 03/08/04 6.4
X48962 SYKES 05/13/04 12.9

This column goes up to 3000 records.





Step2
This is the "lookup" column where I enter numbers
to lookup in EmployeeDat Sheet. These numbers are
for Employee Joe. I have 30 employees to work with.
A
C12429
X99130
Z52679
X33309
Z27065
Z36356
X90879
X59433
X88291
C77441
C41371
Q01375
Q82278

This column goes to 20 or 50 rows.
These numbers are copied and pasted from a delimited text file.
This file is formated as text when given to me.
I select this column "A" to search in EmployeeDat.
I tried several formats with this to no avial.




Step3
Sub RosterEmployee()
ChDir "C:\Documents and Settings\myuseraccount\Desktop"
Workbooks.Open Filename:="C:\Documents and
Settings\myuseraccount\Desktop\Employee.xls"
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("A:A").Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized

ActiveWindow.WindowState = xlMaximized
Sheets("EmployeeDat").Select
Cells.Select
ActiveSheet.Paste
Columns("A:B").Select
'Here I tried formating to no avail.
Selection.NumberFormat = "@"
Range("A1").Select




Step4
'Joe's numbers are in column A of worksheet "Lookup".
'I can make many duplicates of this procedure by changing
'the z to b,c,d columns. If this one procedure works
'then all the others will too.
z = "a1:A"
'This is where rows are copied to.
Sheets("Result").Select
Cells.Select
Selection.ClearContents
'used to get rows from which is the active sheet range.
Sheets("EmployeeDat").Select
'used to lookup rows.
With ThisWorkbook.Worksheets("Lookup")
myTable = .Range(z & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With
With ActiveSheet.Range("A:A")
For i = LBound(myTable, 1) To UBound(myTable, 1)
Set FoundCell = .Cells.Find(What:=myTable(i, 1), _
After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
Else
FoundCell.EntireRow.Copy _
Destination:=Worksheets("Result").Range("a" & i)
End If
Next i
End With




'Here I can make a procedure for each employee







End Sub
 
First, thing: What does this mean?
A column is formated with custom V-3000 when
given to me.
Click to expand...

If that column contains text, then the custom format won't affect anything. If
the column contains just plain old numbers, you won't see things like C05565--it
would look more like V-35565 (using that custom format).

A couple things I'd check. First, I wouldn't trust Activesheet anywhere (but
I'd start here):

With ActiveSheet.Range("A:A")
would become
With workbooks("whateverthenameis").worksheets("wksnamehere").Range("A:A")

Maybe you were looking at the wrong sheet??? You did say you used Chip
Pearson's CellView Addin to check the contents and they matched character for
character (no extra spaces, no extra characters at all???).

If there were an extra space in the range to look through, like "C05565 ", then
this portion:
LookAt:=xlWhole
would cause it to fail (LookAt:=xlPart maybe a quick fix).

If that's the case, David McRitchie has some code that will remove those extra
spaces (and even extra non-breaking spaces from HTML stuff at:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

One more warning. Just because you change the format of a cell to Text, doesn't
mean that the numeric entries are changed. It just means that the the cell will
change to text the next time you change that cell.

But that shouldn't have any affect on cells that contain: c05565.

(What do you see in the cell and what do you see in the formulabar when that
cell is selected? If it's the same, then no problem--but if there is a
difference, then maybe the cell does contain just numbers and it's the
formatting that's bothering you.)

This is my take on what you are doing. But since you activated stuff by
minimizing the active window, I could be completely wrong:

Option Explicit
Sub RosterEmployee()

Dim Z As String

Dim EmplWks As Worksheet
Dim CurWks As Worksheet
Dim oRow As Long
Dim myTable As Variant
Dim i As Long
Dim FoundCell As Range

Set CurWks = ActiveSheet 'not results???

Set EmplWks = Workbooks.Open _
(Filename:="C:\Documents and Settings\myuseraccount" _
& "\Desktop\Employee.xls").Worksheets("employeedat")

With EmplWks
.Columns("A:A").Insert
.Columns("c:c").Copy _
Destination:=.Range("a1")
.Columns("C:c").Delete
.Cells.Copy _
Destination:=CurWks.Range("a1")
End With

Z = "a1:A"

Sheets("Result").Cells.ClearContents

With ThisWorkbook.Worksheets("Lookup")
myTable = .Range(Z & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

oRow = 0
With EmplWks.Range("A:A")
For i = LBound(myTable, 1) To UBound(myTable, 1)
Set FoundCell = .Cells.Find(What:=myTable(i, 1), _
After:=.Cells(.Cells.Count), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
oRow = oRow + 1
FoundCell.EntireRow.Copy _
Destination:=Worksheets("Result").Range("a" & oRow)
End If
Next i
End With
End Sub

I'm at a loss. It looks to me like it's a data problem--not a code problem.
 
I believe I have the answer.

Changing the format between Text and Number will have no effect on data
already entered, but reentry of data will be changed if valid.

This is why if I type in the data it will find what What I am looking for.
So I will have to enter the data manually.

Do you know away to force a column of data to reenter it's self?
Is there a macro that will find a cell then retype it like I would then
go to the next cell and retype the data that is there?
That would be very good solve the problem.
 
Are you converting from Text numbers to number numbers?

If yes, then you can select your offending range and run this:

Option Explicit
Sub testme()

Dim myEmptyCell As Range

With ActiveSheet
Set myEmptyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1,1)
myEmptyCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
End With

Application.CutCopyMode = False

End Sub


It's the equivalent of copying an empty cell, selecting the range and
edit|pastespecial|and checking Add.
 
Dave said:
Are you converting from Text numbers to number numbers?

If yes, then you can select your offending range and run this:

Option Explicit
Sub testme()

Dim myEmptyCell As Range

With ActiveSheet
Set myEmptyCell =
.Cells.SpecialCells(xlCellTypeLastCell).Offset(1,1)
myEmptyCell.Copy Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd
End With

Application.CutCopyMode = False

End Sub


It's the equivalent of copying an empty cell, selecting the range and
edit|pastespecial|and checking Add.
Click to expand...


No it is the other way around. From numbers to text.
Well I am not sure now. The data is in form of Z2343 with a alphabet.
When the data comes to me there is nothing for the formating. I don't see
anything that lets me know what is the format. I believe it is text.
I know now that it is in this column of alphanumeric data that I must
manually type in and then and only then does it find cells in the other
column.

Do you know of a macro that will select this column and re type the data
just like if I retyped it? A macro that will run down the column retyping
the data that is in each cell.
 
When you select the cell and look at the formula bar, do you see that leading
letter (the Z in Z2343)?

If you do, then the cell is already text because it has non-numeric characters
in it.

If you don't, then the cell could have a custom format of something like:
"z"0000

Converting a cell that's already text (contains that alphabetic character) won't
help.

dim myRng as range
dim myCell as range

set myrng = selection
for each mycell in myrng.cells
with mycell
.value = "'" & .value
'or
'.value = "'" & .text
end with
next mycell

(It puts an apostrophe at the beginning of each cell.)
 
Back
Top