Need help with problem

  • Thread starter Thread starter jay
  • Start date Start date
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.
 
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
 
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.
 
Jay,

I should mention that search list (sheet 2) contains your Q59367, w31111
etc. The list being searched (Sheet 1) is your records being compared and
changed.
 
Earl said:
Jay,

I should mention that search list (sheet 2) contains your Q59367, w31111
etc. The list being searched (Sheet 1) is your records being compared and
changed.
Click to expand...


Ok, lets look at it a different way.

Column A

1
3
4
6
7
8
9
10

I want to insert a row between 1 and 3.
I want to insert a row between 3 and 5.

If A1 < 1 THEN INSERT ROW
ELSE NOTHING

NEXT

IF A2 < 2 THEN INSERT ROW
ELSE NOTHING

NEXT
IF A3 < 3 THEN INSERT ROW
ELSE NOTHING

NEXT

ALL THE WAY TO 54.

THEN I WOULD HAVE A SEQUENCE OF 1 TO 54.
I can get my data, but I only really need is to
insert the missing rows.
My problem is I don't understand a simple IF and
Else commands in the macro.
I have looked at many examples, but when I go to
to do it I get errors.
If I can get those missing rows, I think I could do
what I need to do for now.
 
Earl said:
Jay,

I should mention that search list (sheet 2) contains your Q59367, w31111
etc. The list being searched (Sheet 1) is your records being compared and
changed.
Click to expand...


Here is an idea I had.

Column A
3
4
5
6
7
8
9
10



If A1 < 1 Then
Rows(1).Select
Selection.Entire.Row.Insert
Else
Rows(1).Select
End If

If A2 < 2 Then
Rows(2).Select
Selection.Entire.Row.Insert
Else
Rows(2).Select
End If

If A3 < 3 Then
Rows(3).Select
Selection.Entire.Row.Insert
Else
Rows(3).Select
End If

This is what I have at this time.

A1 and A2 work find. A3 has 3. It is
not less than 3, but it inserts a row.
Why does it not jump to the Else statement?
I thought I had it till this happened.

The 3rd If ...... A3 < 3 should not insert
a row.
 
Jay,

Do you mean?
If A1 > 1 THEN INSERT ROW

Try this:

Sub FillEmpty()
Dim MyCell As Range
Dim i As Long
For i = 1 To 54
If Cells(i, 1) > i Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1) = i
End If
Next i
End Sub

Step through it with F8. Alt-F11 back to Excel and watch as you step.
 
Earl said:
Jay,

Do you mean?
If A1 > 1 THEN INSERT ROW

Try this:

Sub FillEmpty()
Dim MyCell As Range
Dim i As Long
For i = 1 To 54
If Cells(i, 1) > i Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1) = i
End If
Next i
End Sub

Step through it with F8. Alt-F11 back to Excel and watch as you step.
Click to expand...


Yes. Your right.
If A1 > 1 then
insert row
Else do nothing

If A2 > 2 then
insert row
Else do nothing

And so on.

I will try to make it work. Thanks.
 
Earl said:
Jay,

Do you mean?
If A1 > 1 THEN INSERT ROW

Try this:

Sub FillEmpty()
Dim MyCell As Range
Dim i As Long
For i = 1 To 54
If Cells(i, 1) > i Then
Cells(i, 1).EntireRow.Insert
Cells(i, 1) = i
End If
Next i
End Sub

Step through it with F8. Alt-F11 back to Excel and watch as you step.
Click to expand...


I tried and I get a compilation error
Expected in End Sub.

It stops right before the Sub FillEmpty()
I tried inserting an End Sub above it and nope.
It splits the screen with a black line in the middle and it only
runs the top. It won't run the bottom half.
 
jay said:
Here is an idea I had.

Column A
3
4
5
6
7
8
9
10



If A1 < 1 Then
Rows(1).Select
Selection.Entire.Row.Insert
Else
Rows(1).Select
End If

If A2 < 2 Then
Rows(2).Select
Selection.Entire.Row.Insert
Else
Rows(2).Select
End If

If A3 < 3 Then
Rows(3).Select
Selection.Entire.Row.Insert
Else
Rows(3).Select
End If

This is what I have at this time.

A1 and A2 work find. A3 has 3. It is
not less than 3, but it inserts a row.
Why does it not jump to the Else statement?
I thought I had it till this happened.

The 3rd If ...... A3 < 3 should not insert
a row.
Click to expand...






I may have solved my problem.
If you know a better way, let me know.

' Macro1 Macro
' Macro recorded 22/6/2004
'

'



If Not Cells(1, 1) = "1" Then
Range("A1").Select
Selection.EntireRow.Insert
End If
If Not Cells(2, 1) = "2" Then
Range("A2").Select
Selection.EntireRow.Insert
End If
If Not Cells(3, 1) = "3" Then
Range("A3").Select
Selection.EntireRow.Insert
End If
If Not Cells(4, 1) = "4" Then
Range("A4").Select
Selection.EntireRow.Insert
End If
If Not Cells(5, 1) = "5" Then
Range("A5").Select
Selection.EntireRow.Insert
End If
If Not Cells(6, 1) = "6" Then
Range("A6").Select
Selection.EntireRow.Insert
End If
If Not Cells(7, 1) = "7" Then
Range("A7").Select
Selection.EntireRow.Insert
End If
If Not Cells(8, 1) = "8" Then
Range("A8").Select
Selection.EntireRow.Insert
End If
If Not Cells(9, 1) = "9" Then
Range("A9").Select
Selection.EntireRow.Insert
End If
If Not Cells(10, 1) = "10" Then
Range("A10").Select
Selection.EntireRow.Insert
End If


upto record 54


I do have a problem with the 9
jumping to the next record which
places all numbers after it one record
out of alignment. Except the last one.
Record 54.

Strange.

If I step through it withe F8, it works
perfectly.
If I run the macro, I think the computer
speed is doing something strange.

My next question is, "Is there a function
that will slow the macro speed down?".
 
Jay,

I don't know why it should work any differently stepping or running. Be
absolutely sure you've started with the same data.

DId not the FillEmpty macro I sent you do what you wanted?
 
Earl said:
Jay,

I don't know why it should work any differently stepping or running. Be
absolutely sure you've started with the same data.

DId not the FillEmpty macro I sent you do what you wanted?
Click to expand...



It is working all day today. That was strange.
It is doing just what I needed to do.
I will play around with the fillempty macro later and
respond when I dig deep into it.
I need to read a good book on macros.
I got a good one off microsoft website for office 97.
I think it is close enough to office 2000.
I want to take break from it.
I finally got the if statement working. I will
use that for other issues.


Later and Thanks.
 
Back
Top