1

Code :

Option Explicit

Sub LeadingZeros()
Dim cel As Range
Dim rg As Range
Dim mx As Integer: mx = 0
Dim cl As Variant
For Each cl In Selection
    If Len(cl) > mx Then mx = Len(cl)
Next

rg.NumberFormat = "0"   'this is showing error

Dim i As Integer
For i = 0 To mx - 2
    If mx = 1 Then Exit Sub
    rg.NumberFormat = rg.NumberFormat & "0"
Next
End Sub


Excel Column :
enter image description here

This is a code for filling up the values in cells with leading zeros. It worked just once, but after that not working. I didn't change the code.
Error :
enter image description here

Community
  • 1
  • 1
Aman Devrath
  • 335
  • 2
  • 12
  • The error message says it all. No value is being set to `rg` therefore, error message... – JayV Jun 25 '18 at 08:10
  • So what do I have to do? Do I have to assign rg to selection? – Aman Devrath Jun 25 '18 at 08:12
  • Possible duplicate of [What is a NullReferenceException, and how do I fix it?](https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) – JayV Jun 25 '18 at 08:13
  • Yes, you have to assign a value to `rg`. No programming language will magically know what your variables are meant to refer to. This MSDN article will help you out: https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.range.aspx – JayV Jun 25 '18 at 08:15

1 Answers1

1

You have to assign the variable rg to a range. This is done with the word Set. In this example, the rg is assigned to A1:A5 and it works further:

Sub LeadingZeros()
    Dim cel As Range
    Dim rg As Range
    Dim mx As Integer: mx = 0
    Dim cl As Variant
    For Each cl In Selection
        If Len(cl) > mx Then mx = Len(cl)
    Next

    Set rg = Range("A1:A5")
    rg.NumberFormat = "0"   'this is showing error

    Dim i As Integer
    For i = 0 To mx - 2
        If mx = 1 Then Exit Sub
        rg.NumberFormat = rg.NumberFormat & "0"
    Next
End Sub
Vityata
  • 39,812
  • 7
  • 40
  • 77
  • Thank you for your answer. I did it on my own. Matched it with your answer, it was right. :) – Aman Devrath Jun 25 '18 at 09:59
  • @AmanDevrath - welcome. :) In general, whenever you are working with ranges,it is a good practice to mention the worksheet as well. Thus, `Set rg = Worksheets("Sheet1").Range("A1:A5")`, otherwise it uses the `ActiveSheet` and this is not always what you want. – Vityata Jun 25 '18 at 10:05