Need help with problem

J

jay

I would like to have some help on this
problem.


Here is a sample of the columns

  A         B             C       D
          M78753        PREECE   F611
          X38050        SAILOR   F813
          I09186        MARTIN   F804
          Q59367        COLLINS  F612
          V35010        KINGMAN  F611
          U27944        COOKS    F713
          S94262        FORTIN   F904


I run record macro and do a Find and replace.
I want to search for Q59367 in column B
If it finds the number then change that number Q59367 to 1

Next

I do a Find and Replace for w31111 in column B
If it finds the number then change that number w31111 to 2

I do a Find and Replace for S94262 in column B
If it finds the number then change that number S94262 to 3

And so on upto 54 numbers.

Then I do an ascending sort for column B.
This puts all records, in sequence, from 1 to 54 at the top of the
spreadsheet. I then can select 1 to 54, copy and paste where ever.


However, If it fails to find a number it does not place
a record where the next sequence would be.
The result is

A         B              C      D
       1          COLLINS  F612
       3          FORTIN   F904






1.......54

I can insert a row above the 3 which would give me the
sequence 1 to 54.  That is a pain because many times the Find and
Replace does not find a number and I have to insert many rows.

I know there are many ways to cook this chicken.
I almost have it working.  I know that the IF and Then
statements must come into play here, but I am having a
hard time understanding what goes where.

I have tried the formulas given to me and nothing yet.
I have been reading lots of examples too and I am just not getting
it yet. I will keep at it tell I get it.

What I need is:

IF number found change number to 2
If not then insert row and number cell A2 to 2

Hope this makes it better to understand.
 
J

jay

jay said:
I would like to have some help on this
problem.


Here is a sample of the columns

A         B             C       D
M78753        PREECE   F611
X38050        SAILOR   F813
I09186        MARTIN   F804
Q59367        COLLINS  F612
V35010        KINGMAN  F611
U27944        COOKS    F713
S94262        FORTIN   F904


I run record macro and do a Find and replace.
I want to search for Q59367 in column B
If it finds the number then change that number Q59367 to 1

Next

I do a Find and Replace for w31111 in column B
If it finds the number then change that number w31111 to 2

I do a Find and Replace for S94262 in column B
If it finds the number then change that number S94262 to 3

And so on upto 54 numbers.

Then I do an ascending sort for column B.
This puts all records, in sequence, from 1 to 54 at the top of the
spreadsheet. I then can select 1 to 54, copy and paste where ever.


However, If it fails to find a number it does not place
a record where the next sequence would be.
The result is

A         B              C      D
1          COLLINS  F612
3          FORTIN   F904






1.......54

I can insert a row above the 3 which would give me the
sequence 1 to 54.  That is a pain because many times the Find and
Replace does not find a number and I have to insert many rows.

I know there are many ways to cook this chicken.
I almost have it working.  I know that the IF and Then
statements must come into play here, but I am having a
hard time understanding what goes where.

I have tried the formulas given to me and nothing yet.
I have been reading lots of examples too and I am just not getting
it yet. I will keep at it tell I get it.

What I need is:

IF number found change number to 2
If not then insert row and number cell A2 to 2

Hope this makes it better to understand.
Click to expand...


Another option would be to sort ascending column B so I get a
sequence by inserting blank rows where. I removed column A because the
it did not align propertly.

B              C       D
1          COLLINS   F612
2
3          FORTIN    F904
 
E

Earl Kiosterud

Jay,

Do you need the sequential numbers? That is, do you need to know which was
the record that matched your first search, etc? If not, a formula beside
column A could mark the matching records, which you could then sort (putting
them together, but in the original sequence). You could now copy/paste or
cut\paste them elsewhere.

For the macro solution. I've put your search list in sheet2 in A2 and down.
The list being searched is Sheet1 starting in A2. You can step through it
with F8 and watch it work. Or mess up. :)

Sub CompareCells()
Dim CompareListCell As Range, CompareCell As Range
Dim n As Long

Set CompareListCell = Sheets("Sheet2").Range("A2")
Set CompareCell = Sheets("Sheet1").Range("A2")
Do While CompareListCell <> ""
Do While CompareCell <> ""
If CompareListCell = CompareCell Then
n = n + 1
CompareCell = n
Exit Do
End If
Set CompareCell = CompareCell.Offset(1, 0) ' move down
Loop
Set CompareListCell = CompareListCell.Offset(1, 0) ' move down
Set CompareCell = Range("A2") ' start over
Loop

End Sub

This isn't very thoroughly tested, but maybe it'll give you a good start.
 
Top