stuck at a loop

[computers hate me]

Ok so i wrote a macro that compares a list of alarms from sheet 1 on column
d and compares it to a list of "master alarms" in sheet 2 in column a.

If there an alarm found in the first sheet comlumn d but its not in the
"master alarms" then it pasts this alarm at the end of the "master alarms"
list.

But my macro seems to get stuck in sheet 1 cell( D9). it just keeps coping
the same cell over and over again to the end of my "master alarms" and it
never stops i dont knwo why
here is the macro

Public row1, row2 As Integer
Public row3 As Integer
Public alarm1, alarm2 As String




Sub Alarms()

row1 = 8
row2 = 1
row3 = 1

Do Until Sheets("Data 14").Cells(row1, 4) = ""

Do Until Sheets("Identified Bf 14 alarms").Cells(row2, 1) = ""
Sheets("Data 14").Select
alarm1 = Cells(row1, 14)
alarm2 = Sheets("Identified Bf 14 alarms").Cells(row2, 1)
If alarm1 <> alarm2 Then
row2 = row2 + 1

If Cells(row2, 1) = "" Then
Sheets("Data 14").Select
Cells(row1, 14).Select
Selection.Copy
Sheets("Identified Bf 14 alarms").Select
FindEmptyRow
Cells(row3, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Data 14").Select
Cells(row1, 4).Select
Selection.Copy
Sheets("Identified Bf 14 alarms").Select
FindEmptyRow
Cells(row3, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Else: row1 = row1 + 1
End If
End If
Loop

Loop



End Sub

 

[Joel]

Her is code that I just tested

Sub IdentNewAlarm()

Set StartCell = ActiveCell

'Find Last Row in column B
With Sheets("BF4 AlarmsCells")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With Sheets("Data 14")
RowCount = 1
Do While .Cells(RowCount, "N").Value <> ""
ID = .Cells(RowCount, "N").Value
With Sheets("BF4 AlarmsCells")
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
.Cells(NewRow, "D").Value = _
Sheets("Data 14").Cells(RowCount, "N")

NewRow = NewRow + 1
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[computers hate me]

ok i dont know what im doing wrong

I put the macro in and pressed run and NOTHING not an error message. Just
nothing happend. Do i need to be at a certain cell before i begin the macro
or what could it be?

 

[Joel]

The macro is running put the wrong cells are being looked or copied. Here is
what the code is doing. I was having problems following your code.


1) The code is looking at every cell in column N on sheet "Data 14"

2) It take the value found in Column N and searches column D on sheet BF4
AlarmsCells. If it doesn't find the value it adds the column N data to
column N

change A to D below

from
Set c = .Columns("A").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
to
Set c = .Columns("D").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)

 

[computers hate me]

Ok YOU got it that is exactly what i want the macro to do ;D

I think in your last sentence you meant to say that if not found it ads the
column N data to column D.

it still doesnt work tho. So, i wanna see why. cause that is exactly what i
am looking for.

So ,my list in the BF4 Alarms is in column A not D but I changed it to D to
test the macro but it still didnt work.

Why is the macro searching for the last cell in column "B" for "Bf 14 Alarms"
Column B has nothing

I guess that could make the difference.
The data in "data 14" Is on column N but the data on "Bf 14 Alarms" is on
column A

here is the code with my sheet names

Sub IdentNewAlarm()

Set StartCell = ActiveCell

'Find Last Row in column B
With Sheets("Bf 14 Alarms")
LastRow = .Range("B" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With Sheets("Data 14")
RowCount = 1
Do While .Cells(RowCount, "N").Value <> ""
ID = .Cells(RowCount, "N").Value
With Sheets("Bf 14 Alarms")
Set c = .Columns("D").Find(what:=ID, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
.Cells(NewRow, "D").Value = _
Sheets("Data 14").Cells(RowCount, "N")

NewRow = NewRow + 1
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[Joel]

I just changed the column letters in the code. It was hard to figure out
which columns you really wanted looking at both your description and code.
I thought it was better to use my own techniques which has worked for a lot
of people.

the periods in front of .RANGE, and .COLUMN reference the with statement
immediately before the statement. You can have nested with statments. I
removed one line of code that wasn't doing anything. Using variables with
descriptive names makes the code easier to understand.

Sub IdentNewAlarm()


'Find Last Row in column A where new data is being added
With Sheets("Bf 14 Alarms")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
NewRow = LastRow + 1
End With

With Sheets("Data 14")
RowCount = 1
'get each alarm on sheet "Data 14" column Nand compare with alarms
' on sheet Bf 14 Alarms" column D
Do While .Cells(RowCount, "N").Value <> ""
Alarms = .Cells(RowCount, "N").Value
With Sheets("Bf 14 Alarms")
Set c = .Columns("A").Find(what:=Alarms, _
LookIn:=xlValues, _
lookat:=xlWhole)
If c Is Nothing Then
'add alarm to first empty row on sheet "Bf 14 Alarms"
.Cells(NewRow, "A").Value = _
Sheets("Data 14").Cells(RowCount, "N")

NewRow = NewRow + 1
End If
End With
RowCount = RowCount + 1
Loop
End With

End Sub

 

[computers hate me]

OHHHH thank you soooooo much. sorry i gave u so much trouble
Just that i am pretty bad and new at this
But the code works it really work

THANKS