I am attempting to write code that will add an input number of leading 0s to strings inside of the range currently selected by the user when the macro is run. I keep encountering errors where instead of returning the expected result, the evaluation of the formula is returning the #Value error and I honestly don't know where to start. I have looked into several solutions using the debugging assistance docs provided by Microsoft but I either don't understand the solution or it isn't there.
Sub Add_leading_00()
Dim rng As Range
Dim Area As Range
Dim leading_count As Long
leading_count = Application.InputBox(Prompt:="Enter an integer:", Type:=1)
If Selection.Cells.Count = 1 Then
Set rng = Selection
Else
Set rng = Selection.SpecialCells(xlCellTypeConstants)
End If
For Each Area In rng.Areas
Area.Value = Evaluate("Rept(0, " & leading_count & ")& & Area.Address & ")
Next Area
End Sub
I do have a function that returns similar results but I have been unsuccessful in my attempts to call that function just on the range selected by the user at the time of the run, and that is necessary for the planned functionality of the macro.
Function AddLeadingZeroes(ref As Range, Length As Integer)
Dim i As Integer
Dim Result As String
Dim StrLen As Integer
StrLen = Len(ref)
For i = 1 To Length
If i <= StrLen Then
Result = Result & Mid(ref, i, 1)
Else
Result = "0" & Result
End If
Next i
AddLeadingZeroes = Result
End Function
There are several discussions on topics adjacent to this one as well that I have found and read through but I am not sure if they're relevant to my error or not. I suspect that I am missing something extremely simple.
