| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code - Repost
Hi again:
Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi
you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi Frank:
Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi
------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi Frank:
Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi
just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Sorry Frank, but no result...maybe something is missing...
Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi
if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
The sample file that I sent is a similar one...
Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi Dan
I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
I did e-mailed you an Excel file...no, the NO is in
column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi Dan
no file was attached. Just the column/row data in the email body :-) -- Regards Frank Kabel Frankfurt, Germany Dan wrote: I did e-mailed you an Excel file...no, the NO is in column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
I did it again...
Thanks, Dan -----Original Message----- Hi Dan no file was attached. Just the column/row data in the email body :-) -- Regards Frank Kabel Frankfurt, Germany Dan wrote: I did e-mailed you an Excel file...no, the NO is in column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize (1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . . |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi
changing the comparison from If Cells(row_index + 1, "C").Value = "No" Then to If LCase(Cells(row_index + 1, "C").Value) = "no" Then does the trick -- Regards Frank Kabel Frankfurt, Germany "Dan" schrieb im Newsbeitrag ... I did it again... Thanks, Dan -----Original Message----- Hi Dan no file was attached. Just the column/row data in the email body :-) -- Regards Frank Kabel Frankfurt, Germany Dan wrote: I did e-mailed you an Excel file...no, the NO is in column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize (1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . . |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Sounds like you are going in circles - back where you started <g
-- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi changing the comparison from If Cells(row_index + 1, "C").Value = "No" Then to If LCase(Cells(row_index + 1, "C").Value) = "no" Then does the trick -- Regards Frank Kabel Frankfurt, Germany "Dan" schrieb im Newsbeitrag ... I did it again... Thanks, Dan -----Original Message----- Hi Dan no file was attached. Just the column/row data in the email body :-) -- Regards Frank Kabel Frankfurt, Germany Dan wrote: I did e-mailed you an Excel file...no, the NO is in column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize (1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . . |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code - Repost
Hi Tom
sigh, yes indeed but now it seems to work for Dan (finally <vbg) -- Regards Frank Kabel Frankfurt, Germany "Tom Ogilvy" schrieb im Newsbeitrag ... Sounds like you are going in circles - back where you started <g -- Regards, Tom Ogilvy "Frank Kabel" wrote in message ... Hi changing the comparison from If Cells(row_index + 1, "C").Value = "No" Then to If LCase(Cells(row_index + 1, "C").Value) = "no" Then does the trick -- Regards Frank Kabel Frankfurt, Germany "Dan" schrieb im Newsbeitrag ... I did it again... Thanks, Dan -----Original Message----- Hi Dan no file was attached. Just the column/row data in the email body :-) -- Regards Frank Kabel Frankfurt, Germany Dan wrote: I did e-mailed you an Excel file...no, the NO is in column "C" Thanks, Dan -----Original Message----- Hi Dan I meant an Excel file :-) But looking at your text file it seems the 'No' statement is in column B and not C. So try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "B").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "B").Value ="No" Then Cells(row_index + 1, "A").resize(1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: The sample file that I sent is a similar one... Thanks, Dan -----Original Message----- Hi if you like email me an example file -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Sorry Frank, but no result...maybe something is missing... Thanks, Dan -----Original Message----- Hi just tested it again and it works without a problem. It inserts only cell in columns A-C if column C contains 'No'. And it inserts it above the 'no' -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Does not do anything.... The last one was working, but inserted the whole row, this one: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "C").EntireRow.Insert (xlShiftDown) End If Next End Sub -----Original Message----- Hi ------------------- Hi try: Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "C").End (xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index+1, "C").Value ="No" Then Cells(row_index + 1, "A").resize (1,3).Insert (xlShiftDown) End If Next End Sub -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi Frank: Sorry, but I did not see any... Thanks, Dan -----Original Message----- Hi you have received some answers to this (laso dealing with only inserting the cells). Have you tried them? -- Regards Frank Kabel Frankfurt, Germany Dan wrote: Hi again: Please let me know, because I did not get an answer, what would be the code for: A B C D 1 1 OK a 2 2 OK b 3 4 NO c d If No is found higlight the cels a, b,c, and insert a row above - Not insert the entire row-! Thank you and I appreciate, Dan . . . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Had to repost it!!! | Excel Discussion (Misc queries) | |||
| Repost of VBA Code/Lookup question | Excel Worksheet Functions | |||
| Repost with code - Runtime error '1004' | Excel Programming | |||
| REPOST for Tom (or anyone else that can help) | Excel Programming | |||
| Code for Buttons (repost) | Excel Programming | |||
Linear Mode
