IF Statement and Else Needed

  • Thread starter Thread starter jay
  • Start date Start date
J

jay

I am new to micro coding in excel.

I am in need of an IF statement with an Else.
I run a macro that does a search in excel for a number n32455.
If the number is found, I move to a column next
to it and place a 1.


The macro does another search for a number w3444.
If the number is found, I move to a column next
to it and place a 2.

and continue to 54.

Then I do a sort which brings all numbers to the
top of the spreadsheet.

All supposed to be in order 1 to 54.

However, If a number or two are not found, the
sequence is not 1 to 54. There are rows missing.
I then have to insert all of the messing rows to
get 1 to 54.

What I would like to do is use an IF statement if the
search fails to find a number, then I insert a row and number it.
That is a blank inserted row with the next sequence number.
Then an Else statement would continue on with the next search.

I tried to write this the best way I could.
Hope it is enough.
 
Briefly :-

Dim foundcell as Object
Set foundcell = ........ Findstatement
If foundcell is Nothing The
 
BrianB said:
Briefly :-

Dim foundcell as Object
Set foundcell = ........ Findstatement
If foundcell is Nothing Then
Click to expand...


I don't know what to put in the
Set foundcell = .......... Findstatement

I tried many ways all have failed to work.

Set foundcell = Cells.Replace What:="W43491", Replacement:="1",
LookAt:=xlPart, _SearchOrder:=xlByColumns, MatchCase:=False


Set foundcell = Replace What:="W43491", Replacement:="1",
LookAt:=xlPart, _SearchOrder:=xlByColumns, MatchCase:=False

Set foundcell = Find What:="W43491", Replacement:="1",
LookAt:=xlPart, _SearchOrder:=xlByColumns, MatchCase:=False


If foundcell is Nothing Then


If I could just get this one working, then I would be able to do great
things.
 
try this where column M has the numbers, col N is the search col and col O
is where you want the numbers.

Sub putnum()
counter = 0
x=cells(rows.count,"m").end(xlup).row
For Each c In Range("m1:m" & x)
Cells(Range("n1:n100").Find(c).Row, "o") = counter + 1
counter = counter + 1
Next c
End Sub
 
Don said:
try this where column M has the numbers, col N is the search col and col O
is where you want the numbers.

Sub putnum()
counter = 0
x=cells(rows.count,"m").end(xlup).row
For Each c In Range("m1:m" & x)
Cells(Range("n1:n100").Find(c).Row, "o") = counter + 1
counter = counter + 1
Next c
End Sub
Click to expand...

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 A.
This puts all records, in sequence, from 1 to 54 at the top of the
spreadsheet. I then can 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 Q59367 COLLINS F612
3 S94262 FORTIN F904
4
5
6
7
8
9
10.......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.
 
Back
Top