Copy + Paste column formulas, ignoring #N/A

[jamescooper]

Hello,

I need VBA code please that will copy + paste the formulas (removing the formulas), except for cells with "#N/A".

Many thanks.

 

[Fluff]

Not sure if I've understood you correctly, but how about this

Sub RemoveFormulae()

   With Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers)
      .Value = .Value
   End With
End Sub

This will convert the formulae in col C to values, ignoring the #N/A

 

[jamescooper]

Not sure if I've understood you correctly, but how about this

Sub RemoveFormulae()

   With Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers)
      .Value = .Value
   End With
End Sub

This will convert the formulae in col C to values, ignoring the #N/A

Hello Fluff,

Seems to work many thanks for this.

 

[Fluff]

Glad to help & thanks for the feedback

 

[jamescooper]

Glad to help & thanks for the feedback

No worries, just encountered a slight problem.

When it gets to the first set of #N/As, following that it then pastes what was in the first row - row 3.



So for example:

151
151
151
151
#N/A
#N/A
#N/A
826 (should have been this, but actually returns 151).

Not sure why it is doing that?

Thanks for any help?

 

[Fluff]

My mistake, try this

Sub RemoveFormulae()

   Dim Rng As Range
   For Each Rng In Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers).Areas
      Rng.Value = Rng.Value
   Next Rng
End Sub

 

[jamescooper]

My mistake, try this

Sub RemoveFormulae()

   Dim Rng As Range
   For Each Rng In Columns(3).SpecialCells(xlFormulas, xlTextValues + xlNumbers).Areas
      Rng.Value = Rng.Value
   Next Rng
End Sub

Thanks Fluff, seems to work, would it be quicker if I specified a range too? Say C3:C100000?

Many thanks.

 

[Fluff]

Give it a go & see.
I wouldn't expect any real difference in speed as SpecialCells only works on the UsedRange.