Why does NumberFormat work in 'ThisWorkbook' but not in a Worksheet?

Question

This VBA script in Excel works perfectly when entered in 'ThisWorkbook', but when I enter it in a specific WorkSheet (where I want to link it to a button), it doesn't work, it gives an error on this line:

ActiveSheet.Range(Cells(intStartRow, cell.Column), Cells(lonLowRow, cell.Column)).NumberFormat = "m/d/yyyy"

The error given is: 1004

Does anybody have an idea why and how to remedy it? Thank you!!

Sub proefje()

Dim strTitleRow As String
Dim arrDates() As String
Dim arrTimes() As String
Dim intStartRow As Integer
Dim lonLowRow As Long

strTitleRow = "D5:AW5"
arrDates = Split("Last update,Last recovery test,Date installed,Key valid until", ",")
arrTimes = Split("Time", ",") 
intStartRow = 6
lonLowRow = ActiveSheet.Range("BB3").Value + ActiveSheet.Range("BE3").Value

'Date/Time job start
    ActiveSheet.Range(strTitleRow).Select
    For Each cell In Selection
        If IsInArray(cell.Value, arrDates) Then
          ActiveSheet.Range(Cells(intStartRow, cell.Column), Cells(lonLowRow, cell.Column)).NumberFormat = "m/d/yyyy"
        End If
        If IsInArray(cell.Value, arrTimes) Then
          ActiveSheet.Range(Cells(intStartRow, cell.Column), Cells(lonLowRow, cell.Column)).NumberFormat = "[$-F400]h:mm:ss AM/PM"
        End If
    Next cell

End Sub


Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Please check which value has `lonLowRow` if the error occurs. — Axel Richter, Apr 21 '15 at 13:35
Which sheet is *active* when you execute this code? If it's not the sheet which contains the code, you may get a 1004 for unqualified range objects. — David Zemens, Apr 21 '15 at 14:04
@DavidZemens The script is part of a much bigger script, which cycles through many different sheets. It first selects a sheet, before hitting this part of the code. Also, I'm running it line by line with a good testing sheet active. Even so, changing the code to Sheets(indexnumber). doesn't do the trick. Same error on same line. — Tommy Hebb, Apr 21 '15 at 14:11
Can you change the `ActiveSheet` reference to a specific `ThisWorkbook.Sheets(<"sheet name" or number>)` reference? — FreeMan, Apr 21 '15 at 14:17
@DavidZemens The first line of code of the Date/Time job start = selecting a range on the active sheet and I can see it do that on the sheet that is opened in Excel, so the script knows what the active sheet is. — Tommy Hebb, Apr 21 '15 at 14:18

score 2 · Accepted Answer · edited May 23 '17 at 10:10

In the context of a Worksheet module, the Cells object (unless otherwise qualified) refers to Me.Cells, i.e., the worksheet wherein the code resides. See this simple example which I put in the Sheet1 module, which will raise the 1004 error when Sheet2 is the ActiveSheet.

Sub sheet1Macro()
    MsgBox ActiveSheet.Range(Cells(1, 1), Cells(1, 2)).Address
End Sub

You have a construct like this, which uses the ActiveSheet, which may (or may not) be the same sheet. If Me and ActiveSheet are not the same sheet, then the error is expected, since you can't define a range which spans multiple worksheets like this.

ActiveSheet.Range(Cells(intStartRow, cell.Column), Cells(lonLowRow, cell.Column)).NumberFormat = "m/d/yyyy"
    End If

Resolution: put the code in a standard module, or, fully qualify your range objects like:

With ActiveSheet
    .Range(.Cells(intStartRow, cell.Column), .Cells(lonLowRow, cell.Column)).NumberFormat = "m/d/yyyy"
End With

Why does NumberFormat work in 'ThisWorkbook' but not in a Worksheet?

1 Answers1