Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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" wrote: 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" wrote: 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("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 "computers hate me" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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" wrote: 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" wrote: 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" wrote: 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("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 "computers hate me" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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("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 "computers hate me" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
stuck at a loop
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 :D THANKS "Joel" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stuck in an error loop | Excel Discussion (Misc queries) | |||
Stuck in a Sub Loop | Excel Worksheet Functions | |||
Stuck in endless loop, HELP! | Excel Programming | |||
Stuck in a loop | Excel Discussion (Misc queries) | |||
HELP - Stuck in loop | Excel Programming |