Add a word or sentence to every blank cell

[atditiljazi]

Hi all,

I'm after a macro to add "is it still on track" in every blank cell in column L. I am really struggling so I am hoping for some help.

I am also after a macro that will fill in every blank cell in column M with the data in column H

Many thanks

 

[Legacy 143009]

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 8).End(xlUp).Row 'Get the last row assuming your main data is on Column H.
 
  For i = 2 To lRow
    If Cells(i, 12).Value = "" Then Cells(i, 12).Value = "is it still on track"
    If Cells(i, 13).Value = "" Then Cells(i, 13).Value = Cells(i, 8).Value
  Next
End Sub

 

[Peter_SSs]

About how many rows of data is there likely to be?

 

[atditiljazi]

About how many rows of data is there likely to be?

hi Peter,

roughly 750 rows. the number of rows changes weekly

 

[Peter_SSs]

roughly 750 rows. the number of rows changes weekly

Thanks. That is not a very big number for vba to deal with so looping a row at a time should not be a problem.
I was asking because it would be possible to do all the cells in a column at once rather than one at a time but for that smallish number it is hardly worth it.

 

[atditiljazi]

Thanks. That is not a very big number for vba to deal with so looping a row at a time should not be a problem.
I was asking because it would be possible to do all the cells in a column at once rather than one at a time but for that smallish number it is hardly worth it.

The rows can go up to 2000+, at the moment it's at 750 but it will dramatically go up in the upcoming weeks.

 

[Peter_SSs]

Well, you haven't actually said whether the code in post #2 does what you want. I'm sure @Flashbond would appreciate some feedback on that.
Assuming it does work, try it and if it ends up being slow than post back.
I would suggest adding these couple of lines to the code.

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 8).End(xlUp).Row 'Get the last row assuming your main data is on Column H.
  
  Application.ScreenUpdating = False
  For i = 2 To lRow
    If Cells(i, 12).Value = "" Then Cells(i, 12).Value = "is it still on track"
    If Cells(i, 13).Value = "" Then Cells(i, 13).Value = Cells(i, 8).Value
  Next
  Application.ScreenUpdating = True
End Sub

 

[HaHoBe]

Hi,

what about this one:

Sub FillBlankCellsColL_M()
  Dim lngLRow As Long
  With ActiveSheet
    lngLRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    With .Range("L2:L" & lngLRow)
      .Value = Evaluate("=IF(" & .Address & "="""",  ""is it still on track"" ," & .Address & ")")
    End With
    With .Range("M2:M" & lngLRow)
      .Value = Evaluate("=IF(" & .Address & "=""""" & "," & .Offset(0, -5).Address & "," & .Address & ")")
    End With
  End With
End Sub

Holger

 

[atditiljazi]

Well, you haven't actually said whether the code in post #2 does what you want. I'm sure @Flashbond would appreciate some feedback on that.
Assuming it does work, try it and if it ends up being slow than post back.
I would suggest adding these couple of lines to the code.

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 8).End(xlUp).Row 'Get the last row assuming your main data is on Column H.
 
  Application.ScreenUpdating = False
  For i = 2 To lRow
    If Cells(i, 12).Value = "" Then Cells(i, 12).Value = "is it still on track"
    If Cells(i, 13).Value = "" Then Cells(i, 13).Value = Cells(i, 8).Value
  Next
  Application.ScreenUpdating = True
End Sub

Sorry, the code from Flashbond did not work. I did not get any error code, but nothing happened to my spreadsheet when i used the macro.

 

[atditiljazi]

Sub test()
  Dim lRow As Long
  lRow = Cells(Rows.Count, 8).End(xlUp).Row 'Get the last row assuming your main data is on Column H.
 
  For i = 2 To lRow
    If Cells(i, 12).Value = "" Then Cells(i, 12).Value = "is it still on track"
    If Cells(i, 13).Value = "" Then Cells(i, 13).Value = Cells(i, 8).Value
  Next
End Sub

hi Flashbond, this didn't work. nothing changed in my spreadsheet. i didn't get any error codes either