Run-Time error '1004'

Question

I am new to VBA. I'm working on a project which will verify the length and format of data entered into the sheet. I'm going to expand the code to include validation for all potential headers (Phone numbers, addresses, suffixes, etc.). However, I am receiving run-time error 1004 on this line of code, "Columns(col).NumberFormat = "0"" in the phonenumbers sub. If I comment that line out, I receive the same error on the next line. Seeking help/explanation on why this is occuring and how to resolve.

Thanks!

Option Explicit

Sub DataVerification()
   Dim i As Long
   Dim rw As Long
   Dim col As Long
   Dim rng As Range

'Set cell background color to nothing
ActiveSheet.Cells.Interior.ColorIndex = xlNone

'loop through header row A10:F10 to determine which column needs validation
 For i = 1 To 6
   With Sheets("Sheet2")
      If UCase(.Cells(10, i).Value) = "PHONE NUMBERS" Then
         Call PhoneNumbers
      ElseIf UCase(.Cells(10, i).Value) = "ADDRESSES" Then
         Call Addresses
      ElseIf UCase(.Cells(10, i).Value) = "SUFFIXES" Then
         Call Suffixes
      Else
         MsgBox ("No data entered")
         Exit For
      End If
   End With
 Next i


'Phone numbers text found?
If col = 0 Then
   MsgBox "Phone Numbers Header not found"
   Exit Sub
End If

'Set column format to number
Columns(col).NumberFormat = "0"


'set up the start range, loop until we find an empty cell


'tidy up
Set rng = Nothing

 End Sub

 Sub PhoneNumbers()
Dim i As Long
Dim rw As Long
Dim col As Long
Dim rng As Range


 col = i

 'Set column format to number
 Columns(col).NumberFormat = "0"

 Set rng = Sheets("Sheet2").Cells(11, col)
 Do Until rng = ""
   If Not IsNumeric(rng.Value) Or Len(rng.Value) <> 11 Then

      'highlight cell
      rng.Interior.ColorIndex = 3 'red
   End If

   'get next row
   Set rng = rng.Offset(1, 0)
  Loop
 End Sub

it's because you are using col = i, but i at the moment equal to 0 (because you haven't initialized it). And there is no Column with index 0 — Dmitry Pavliv
To fix it you could modify your sub to accept parametr: Sub PhoneNumbers(i as Long) and then call it in loop as follows Call PhoneNumbers(i). But don't forget to remove Dim i As Long from PhoneNumbers sub — Dmitry Pavliv

Pedrumj Pedrumj · Accepted Answer · 2014-02-19T15:48:37

In your code Col is zero. Columns start from the index 1, Try this:

  Col = 1
'Set column format to number
Columns(col).NumberFormat = "0"

Run-Time error '1004'

1 Answers