|
Simple "If" statement ?
If Range("F4").Value < ""Then
'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF |
Simple "If" statement ?
Do you want to pick up all of the items in column F? Something like this
With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF |
Simple "If" statement ?
I get an error message that says:
Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF |
Simple "If" statement ?
Sorry, I forgot to tell you on which line the error occurred. This is the
line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF |
Simple "If" statement ?
You could try:
Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson |
Simple "If" statement ?
Dave,
This works great except that it only inserts the data in column "F", the date done column. I need it to insert the whole row (actually columns A through G). The "F" column only tells the macro which rows need to be moved to "Done". This is an example of what the "Jobs" sheet looks like. The 2 rows (all cells in each of those 2 rows) with the date "1/13" need to be inserted into "Done", not just the "Date Done" column. This is an example of what worksheet "Jobs" looks like: 2007 Projects Property# Address Job Description Date Done Worker JM-04 2201 Elm fix entry door JM-09 4324 Oak re-grout tile entry 1/13 Lynn JM-09 4324 Oak re-grout tile kitchen JM-09 4324 Oak re-grout tile bath1 1/13 Lynn JM-09 4324 Oak re-grout tile bath2 "Dave Peterson" wrote: You could try: Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson |
Simple "If" statement ?
Oops, I tried to copy directly from the spreadsheet and the headings and data
don't line up. Sorry, but you should be able to get an idea of what it really looks like. Note: the "Address" heading is a merge of cells B3 and C3, the "Job Description" heading is a merge of cells D3 and E3. All the data cells below those 2 headings are likewise merged. "BluzDude" wrote: Dave, This works great except that it only inserts the data in column "F", the date done column. I need it to insert the whole row (actually columns A through G). The "F" column only tells the macro which rows need to be moved to "Done". This is an example of what the "Jobs" sheet looks like. The 2 rows (all cells in each of those 2 rows) with the date "1/13" need to be inserted into "Done", not just the "Date Done" column. This is an example of what worksheet "Jobs" looks like: 2007 Projects Property# Address Job Description Date Done Worker JM-04 2201 Elm fix entry door JM-09 4324 Oak re-grout tile entry 1/13 Lynn JM-09 4324 Oak re-grout tile kitchen JM-09 4324 Oak re-grout tile bath1 1/13 Lynn JM-09 4324 Oak re-grout tile bath2 "Dave Peterson" wrote: You could try: Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson |
Simple "If" statement ?
Try:
..rows(i).Copy .parent.Worksheets("Done").Range("A1") BluzDude wrote: Dave, This works great except that it only inserts the data in column "F", the date done column. I need it to insert the whole row (actually columns A through G). The "F" column only tells the macro which rows need to be moved to "Done". This is an example of what the "Jobs" sheet looks like. The 2 rows (all cells in each of those 2 rows) with the date "1/13" need to be inserted into "Done", not just the "Date Done" column. This is an example of what worksheet "Jobs" looks like: 2007 Projects Property# Address Job Description Date Done Worker JM-04 2201 Elm fix entry door JM-09 4324 Oak re-grout tile entry 1/13 Lynn JM-09 4324 Oak re-grout tile kitchen JM-09 4324 Oak re-grout tile bath1 1/13 Lynn JM-09 4324 Oak re-grout tile bath2 "Dave Peterson" wrote: You could try: Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson -- Dave Peterson |
Simple "If" statement ?
Dave, that did it! Works like a dream. Thank you very much! Oh I changed the
("A1") to ("A4") so that it inserts all new data in row 4. "Dave Peterson" wrote: Try: ..rows(i).Copy .parent.Worksheets("Done").Range("A1") BluzDude wrote: Dave, This works great except that it only inserts the data in column "F", the date done column. I need it to insert the whole row (actually columns A through G). The "F" column only tells the macro which rows need to be moved to "Done". This is an example of what the "Jobs" sheet looks like. The 2 rows (all cells in each of those 2 rows) with the date "1/13" need to be inserted into "Done", not just the "Date Done" column. This is an example of what worksheet "Jobs" looks like: 2007 Projects Property# Address Job Description Date Done Worker JM-04 2201 Elm fix entry door JM-09 4324 Oak re-grout tile entry 1/13 Lynn JM-09 4324 Oak re-grout tile kitchen JM-09 4324 Oak re-grout tile bath1 1/13 Lynn JM-09 4324 Oak re-grout tile bath2 "Dave Peterson" wrote: You could try: Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson -- Dave Peterson |
Simple "If" statement ?
Good to hear.
BluzDude wrote: Dave, that did it! Works like a dream. Thank you very much! Oh I changed the ("A1") to ("A4") so that it inserts all new data in row 4. "Dave Peterson" wrote: Try: ..rows(i).Copy .parent.Worksheets("Done").Range("A1") BluzDude wrote: Dave, This works great except that it only inserts the data in column "F", the date done column. I need it to insert the whole row (actually columns A through G). The "F" column only tells the macro which rows need to be moved to "Done". This is an example of what the "Jobs" sheet looks like. The 2 rows (all cells in each of those 2 rows) with the date "1/13" need to be inserted into "Done", not just the "Date Done" column. This is an example of what worksheet "Jobs" looks like: 2007 Projects Property# Address Job Description Date Done Worker JM-04 2201 Elm fix entry door JM-09 4324 Oak re-grout tile entry 1/13 Lynn JM-09 4324 Oak re-grout tile kitchen JM-09 4324 Oak re-grout tile bath1 1/13 Lynn JM-09 4324 Oak re-grout tile bath2 "Dave Peterson" wrote: You could try: Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy Worksheets("Done").Range("A1") Note the absense of the dot in front of Worksheets("Done"). Or even .parent.Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .parent.Worksheets("Done").Range("A1") Your preceding With statement is: with worksheets("Jobs") ..parent will refer to the workbook that contains the Jobs Worksheet. BluzDude wrote: Sorry, I forgot to tell you on which line the error occurred. This is the line that is highlighted: .Worksheets("Done").Rows(1).Insert "BluzDude" wrote: I get an error message that says: Run-time error '438': Object doesn't support this property or method Also, the data starts on row 4 on both sheets. This is what I put in the macro, substituting "Jobs" for "Sheet1" and "Done" for "Sheet2". Public Sub CutToDone() With Worksheets("Jobs") iLastRow = .Cells(.Rows.Count, "F").End(xlUp).Row For i = iLastRow To 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Done").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Done").Range("A1") .Rows(i).Delete End If Next i End With End Sub "Bob Phillips" wrote: Do you want to pick up all of the items in column F? Something like this With Worksheets("Sheet1") iLastRow = .Cells(.Rows.Count,"F").End(xlUp).Row For i = iLastRow to 1 Step -1 If .Cells(i, "F").Value < "" Then .Worksheets("Sheet2").Rows(1).Insert .Cells(i, "F").Copy .Worksheets("Sheet2").Range("A1") .Rows(i).delete End If Next i End With -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Thanks a group Bob! Now, what I am doing is moving data from one spreadsheet to another spreadsheet if there is a date in the "F" column, and I can have up to 30 rows of data that might or might not have a date in column "F". I'm doing a "cut/insert" operation in order to move completed jobs from a "jobs" sheet to a "done" sheet which means that I am deleting each row from the "jobs" sheet that gets moved to the "done" sheet. What is happening is that every time the macro encounters a row that gets moved, the procedure ends and I have to run it again to get to the next one that needs to be moved. I need to re-run this macro automatically until it has run it 30 times. Is this possible with a some kind of counter like a "for...next" statement? If so, could you show me how to write it and if I need to do anything else to your original code? I know this stuff is really simple if you know VB but I don't know much VB. I'm pretty good with formulas in Excel but can't get the syntax right in VB. Thanks for your time, I really appreciate it. I've been messing with this thing for 4 days now and it's frustrating the heck out of me. "Bob Phillips" wrote: If Range("F4").Value < ""Then 'Code if cell "F4" has a date in it ElseIf Range("F5").Value < ""Then 'Code if cell "F5" has a date in it END IF -- --- HTH Bob (change the xxxx to gmail if mailing direct) "BluzDude" wrote in message ... Can someone tell me how to write an "IF" statement that looks at a cell (F4) and if that cell has a date in it, it executes the code following, if the cell is empty it goes to the next "IF" statement and repeats the same procedure on the next row (F5), etc., etc.? I've tried this and it doesn't work: IF NOT Range("F4") IS NOTHING THEN 'Code if cell "F4" has a date in it ELSEIF NOT Range("F5") IS NOTHING THEN 'Code if cell "F5" has a date in it END IF -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com