Find & replace hard-coded cells only

[merry_fay]

Hi,

I'm writing a macro to add new items to a list then copy down the formula from the row above. This far I'm fine, my problem is that the first few columns, depending on the month will be hard-coded & so I want to set these as 0 for the new item.

I have a month control, but in the instance someone has a blonde moment & sets it to the wrong one, any new items added would have the wrong number of columns of 0's & formulae.

Is there a way of doing a find & replace to find cells which could be any number & aren't formulae, then replacing them with a 0, or am I just looking at this completely the wrong way?

Thanks
merry_fay

 

[Andrew Poulsom]

Look at Edit|Go To|Special|Constants|Numbers. Use the macro recorder to get the code.

 

[VoG]

Hi. Try like this

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).Value = 0

 

[merry_fay]

Hi. Try like this

ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlNumbers).Value = 0

This is great thanks, but I'm just having problems applying it to just the selected range & not the entire sheet. What does .UsedRange need to be changed to?

Thanks
merry_fay

Got there, need to change ActiveSheet.UsedRange to Selection.

THANKYOU

 

[VoG]

Try

Selection.SpecialCells(xlCellTypeConstants, xlNumbers).Value = 0