Populate cell with value where empty

[dpmaki]

I need to come up with some code that will allow me to put in a certain value "555555" where any values in column N are empty. I need to use M as the driver here meaning that I need to xlup on column M and offset by 1 so that I can apply the 555555 value to all the empty cells in N down to the last populated cell in column M. Anyone have any idea on how to do this?

 

[tigeravatar]

dpmaki,

Give this a try:

Sub tgr()
    
    On Error Resume Next
    Intersect(ActiveSheet.UsedRange, Columns("N")).SpecialCells(xlCellTypeBlanks).Value = 55555
    
End Sub

 

[Rick Rothstein]

This should do what you asked for...

Range("N1:N" & Cells(Rows.Count, "M").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = 55555

 

[Rick Rothstein]

dpmaki,

Give this a try:

Sub tgr()
 
    On Error Resume Next
    Intersect(ActiveSheet.[COLOR=red]UsedRange[/COLOR], Columns("N")).SpecialCells(xlCellTypeBlanks).Value = 55555
 
End Sub

The use of UsedRange could possibly overfill Column N (one, if its last row is not set correctly because of cell editing or, two, if other columns have more data than Column M, say, because of a Total cell at the bottom of some column).

 

[dpmaki]

The use of UsedRange could possibly overfill Column N (one, if its last row is not set correctly because of cell editing or, two, if other columns have more data than Column M, say, because of a Total cell at the bottom of some column).

It's trying to work, but the contents of column N where they appear NULL are actually formulas.

Example - this is what resides in N4: =IF(K4>0,Rebates!I4,"")
Would there be a better way to right the if statement so it's truly putting a NULL in there?

 

[Jonmo1]

Perhaps change that formula to

=IF(K4>0,Rebates!I4,555555)

 

[Rick Rothstein]

It's trying to work, but the contents of column N where they appear NULL are actually formulas.

Example - this is what resides in N4: =IF(K4>0,Rebates!I4,"")
Would there be a better way to right the if statement so it's truly putting a NULL in there?

Let me get this straight... you have formula in your cells show the empty string ("") and you want to overwrite the formula with a numerical constant (thus removing the formula from the cell), is that correct? If so, what is in the cells in Column N that are not blank... formulas or constants (that is, if the cell is not blank, is that because you overwrote the formula that was there originally with a numerical constants)?

 

[dpmaki]

Let me get this straight... you have formula in your cells show the empty string ("") and you want to overwrite the formula with a numerical constant (thus removing the formula from the cell), is that correct? If so, what is in the cells in Column N that are not blank... formulas or constants (that is, if the cell is not blank, is that because you overwrote the formula that was there originally with a numerical constants)?

Correct - I have formulas in column N, but not all of column N - the formula has filled those cells already that need to be filled. The thought of the if,than crossed my mind, but not all cells in the column have formulas in them. So I suppose I could use a combo of the if/and plus the code you already gave me.

 

[Rick Rothstein]

Correct - I have formulas in column N, but not all of column N - the formula has filled those cells already that need to be filled. The thought of the if,than crossed my mind, but not all cells in the column have formulas in them. So I suppose I could use a combo of the if/and plus the code you already gave me.

The cells that the formulas already filled in... do they have to stay as formulas or would it be alright to convert them to constants? If it would be alright to convert them to constants, then you could use this code...

  With Range("N1:N" & Cells(Rows.Count, "M").End(xlUp).Row)
    .Value = .Value
    .SpecialCells(xlCellTypeBlanks).Value = 55555
  End With