moving to next cell in range

All,

I thought I had this, but....
What I need is to remove the data in cell A1 if in cell D1 it
states;"Would you like to add any comments?"...then move to the next
row/cell until cell A(whatever) contains no data.

Sub Test()
Dim Cell As Range
For Each Cell In Range("D:D")
If Range("D1") = "Would you like to add any comments?" Then
Range("A1") = ""
Next
End Sub


Any help is appreciated

Hans

Sub Test()
Dim Cell As Range
For Each Cell In Range("D:D")
If cell = "Would you like to add any comments?" Then
cell.offset(0,-3).ClearContents
End if
Next
End Sub

However, that would take forever

Sub Test
Dim cell as Range, rng as Range
set rng = .Range(cells(1,"D"),cells(rows.count,"D").End(xlup))
for each cell in rng
If cell = "Would you like to add any comments?" Then
cell.offset(0,-3).ClearContents
end if
Next
End sub

there are other ways, such as using find.

--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> All,
>
> I thought I had this, but....
> What I need is to remove the data in cell A1 if in cell D1 it
> states;"Would you like to add any comments?"...then move to the next
> row/cell until cell A(whatever) contains no data.
>
> Sub Test()
> Dim Cell As Range
> For Each Cell In Range("D:D")
> If Range("D1") = "Would you like to add any comments?" Then
> Range("A1") = ""
> Next
> End Sub
>
>
> Any help is appreciated
>
> Hans
>

Tom,

Thanks for the help; I am new to the VB side...How would you use "find"
in a situation like this?


Thanks,

Hans

Look at the VBA help example for FindNext

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Tom,
>
> Thanks for the help; I am new to the VB side...How would you use "find"
> in a situation like this?
>
>
> Thanks,
>
> Hans
>

Hans,

Try:

Sub Test()
Dim aCell As Range

For Each aCell In Range("D:D")
If aCell = "Would you like to add any comments?" Then
aCell.Offset(0, -3).ClearContents
End If
Next aCell
Set aCell = Nothing
End Sub

HTH

Tim


<[email protected]> wrote in message
news:[email protected]...
> All,
>
> I thought I had this, but....
> What I need is to remove the data in cell A1 if in cell D1 it
> states;"Would you like to add any comments?"...then move to the next
> row/cell until cell A(whatever) contains no data.
>
> Sub Test()
> Dim Cell As Range
> For Each Cell In Range("D:D")
> If Range("D1") = "Would you like to add any comments?" Then
> Range("A1") = ""
> Next
> End Sub
>
>
> Any help is appreciated
>
> Hans
>

Gentlemen....thanks for your help, it is working like I need now!

Hans