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

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

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

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

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,
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

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

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, 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

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