Macro that converts today()+3 in to text on 2 columns

[phish3rz]

Hi all,

I had a perfectly working macro for 1 column which when the work sheet is saved, the macro converts any formulas on a column that has today()+3 in, into a text, so when it saves, the date remains static.

I now need this macro to do the same job within 2 columns, however, it fails to pick up on the second column. This is my macro so far:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range, cell2 As Range, DateRng As Range, DateRng2 As Range

Set DateRng = Sheets("January").Range("L5:L36665")
Set DateRng2 = Sheets("January").Range("B5:B36665")
For Each cell In DateRng
If IsDate(cell.Value) Then
    cell.Copy
    cell.PasteSpecial Paste:=xlPasteValues
End If
Next cell

For Each cell2 In DateRng2
If IsDate(cell2.Value) Then
    cell2.Copy
    cell2.PasteSpecial Paste:=xlPasteValues
End If
Next cell2

End Sub

Any help would be greatly appreciated it.

The original macro that works perfectly on 1 column is:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim cell As Range, DateRng As Range

Set DateRng = Sheets("January").Range("L5:L36665")
For Each cell In DateRng
If IsDate(cell.Value) Then
    cell.Copy
    cell.PasteSpecial Paste:=xlPasteValues
End If
Next cell

End Sub

[Andrew-R]

The code looks fine - is it throwing up any errors? Have you tried putting a debug in and stepping through it (perhaps with smaller ranges).

[phish3rz]

It's throwing no errors up unfortunately. The weird thing what's happening, is that only DateRng2 is working, it appears to ignore the first for loop which I require for the L column..but then moves onto the second for loop and works fine!

[kvsrinivasamurthy]

Does the ranges ("B5:B36665") and ("L5:L36665") contains date?

[phish3rz]

Not all of them. If a user fills out A1, then B1 is filled with a today(). If a user fills out K1, then L1 is filled in with today()+3, so that macros intention is to convert the formula into text so the date is static. It does this correctly, how ever it's ignoring the first for loop.

[Andrew-R]

After you've set DateRng just stick in a line:

MsgBox DateRng.Address

And make sure it has the correct range in there (although I can't see any reason why it shouldn't)

If that's OK change the line to:

MsgBox DateRng.Cells.Count

Which should return 36,661.

Out of interest, you've said that not all of the cells contain a date - what's in the cells that don't contain one? Is there any reason you couldn't just use a PasteSpecial for the whole range at once, it would be much, much faster than looping through every cell.

[6StringJazzer]

I don't see a problem with the code, and I tested it my own test workbook and it worked fine. After that test, I modified it to be much simpler (probably faster too). You may want to try this version to see if it works better for you:

Private Sub test()

   Dim cell As Range, DateRng As Range
   
   Set DateRng = Sheets("January").Range("B5:B36665,L5:L36665")
   For Each cell In DateRng
      If IsDate(cell.Value) Then
          cell.Value = cell.Value
      End If
   Next cell
   
End Sub