Macro to hardcode a row based on formula output

[Antonia2345]

Hi,

I have a workbook that contains an excel worksheet, in which each cell in column A (starting from row 16) is a consistent formula returning "Yes" or "No" based on conditions within other worksheets.

I am trying but failing to write a macro that:

a) searches column A for rows containing the formula output "Yes" (I can search successfully for the hardcoded value "Yes", but I can't work out how to search for the formula output "Yes"), and then where it finds "Yes" in Column A, then selects that row and hardcodes the whole row

b) is triggered automatically whenever the output of a formula in column A changes to "Yes"

Can this be done?

Please help!

 

[Antonia2345]

Yes! Thank you - this is working nicely and it is fast. Problem solved, I really appreciate your help

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[stacey_t]

Hi, I know I'm coming at this late in the game but I receive an error using this code and I don't know enough VBA to troubleshoot. Says Compile error: Argument not optional

Sub CopyPasteCompleted()
Dim Cl As Range, Rng As Range

On Error Resume Next
Set Rng = Range("A:A").SpecialCells(xlFormulas)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
For Each Cl In Range
If Cl.Value = "Complete" Then
Cl.EntireRow.Value = Cl.EntireRow.Value
End If
Next Cl
End Sub

 

[offthelip]

This line is wrong:

For Each Cl In Range

Range is function that requires an argument of which there are many options , I think what you really mean is to loop through the range you have just set up in the variable Rng, so try:

For Each Cl In Rng

 

[stacey_t]

This line is wrong:

For Each Cl In Range

Range is function that requires an argument of which there are many options , I think what you really mean is to loop through the range you have just set up in the variable Rng, so try:

For Each Cl In Rng

This does resolve the error for me but the macro doesn't seem to be working as designed. My "Complete" rows still have formulas.

 

[offthelip]

I have understood what you are trying to do, your code as it stands doesn't work because you set the range in column A just to include where you have got a formula so when you test for the text "Complete" it never finds it. Try this which checks every line for complete. I have detected the last line with values in it because it is much faster than doing the entire column:

Sub CopyPasteCompleted()
Dim Cl As Range, Rng As Range

On Error Resume Next
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Set Rng = Range("A1:A" & lastrow)
On Error GoTo 0
If Rng Is Nothing Then Exit Sub
For Each Cl In Rng
If Cl.Value = "Complete" Then
Cl.EntireRow.Value = Cl.EntireRow.Value
End If
Next Cl
End Sub

 

[Joe4]

stacey_t question continued here: Copy/paste special values of Excel table based on cell value
Please continue on there.

stacey_t,
In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

I am locking this thread now.