help with macro

[Tim]

Hi
I need help to create a macro which will look in
column "Z"
and if any cell in this column has value 1 then do this:
1) On the entire row with offset put in column "K"
current date
2) Entire row Copy-Paste Special-Values
3) Entire row change value 1 in column "Z" with the
formula: =IF(sub!C1=6,1,"")
Any help is highly appreciated.

 

[Don Guillett]

Is this homework?

 

[Tim]

I need your help to create this macro.
Please help me if you can Dave!!!
I know you cando this.

 

[Don Guillett]

How about we get you started by suggesting you look in VBA HELP index for
FIND or FIND NEXT for part 1 of your question. Part 2 can be resolved by
using the recorder. Some of your question is not very clear.

 

[Guest]

it is not homework.
I ned you to help me if you can Don

 

[Tom Ogilvy]

Sub ProcZ()
Dim cell as Range, rng as Range
set rng = Range(cells(1,"Z"),cells(rows.count,"Z").End(xlup))
for each cell in rng
if cell.Value = 1 then
cells(cell.row,"K").Value = Date
cell.Entirerow.Copy
cells(cell.row,1).EntireRow.Pastespecial xlValues
cell.Formula = "=If(Sub!C1=6,1,"""")"
end if
Next
End Sub

 

[Tim]

Hi
I need a help to create a macro which will look in
column "Z"
and if any cell in this column has value "1" the macro
should do this:
1) On the entire row (which cell in column "Z" is=1)
with offset put in column "K" current date
2) Entire row(which cell in column "Z" is=1) Copy-
Paste Special-Values.
3) On the Entire row (which cell in column "Z"
is=1) change the value 1 in column "Z" with the formula:
=IF(sub!C1=6,1,"") (replace number "1" in column"Z" of
the entire row(which cell in column "Z" is=1) with the
next formula: =IF(sub!C1=6,1,"")
Any help is highly appreciated.

 

[Tom Ogilvy]

I gave you an answer that does that - do you have a problem with it or just
prefer to work with Don?

 

[Tim]

As always perfect answer from you.
Tahk you Tom!!!