Is CompDate a string containing say H

If sourceRange.Cells(1,CompDate).Value < ""

or a range containg the column H,

If sourceRange.Cells(1,Comp Date.Column).Value < ""


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DavidW" wrote in
message ...

In the code below I am stepping through all the cells on the worksheet
"Master" and moving certain rows to a worksheet "Complete". Only the
rows that have a value in the H column called "Comp Date" will be moved
to the other sheet. How would I specify the column in my If statement
below? This line is incorrect - If sourceRange.Column(Comp Date). That
should give you an idea of what I need.


Code:
--------------------
Dim Bcell As Range
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
' I omitted some code that works
For Each Bcell In Worksheets("Master").Range("A2", LastCell)
Set sourceRange = ActiveCell.EntireRow
' the following line is wrong - it needs to refer to column H /Comp Date
If sourceRange.Column(Comp Date) < "" Then 'this line is wrong
Set destrange = Sheets("Complete").Rows(Lr + 1)
sourceRange.Copy destrange
sourceRange.EntireRow.Delete
End If
Next Bcell
--------------------


Thanks for any help.


--
DavidW
------------------------------------------------------------------------
DavidW's Profile:
http://www.excelforum.com/member.php...o&userid=32630
View this thread: http://www.excelforum.com/showthread...hreadid=574275