• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 109
  • Last Modified:

VBA EXCEL - If only 3 digit in a cell add a 0 in the the start

Hi Guys,

I got code block! so please help/advise

I have a cell that does a vlookup from another sheet and paste the value, but sadly the source is text formatet and is shown correctly and like this 0889 but after the paste of value its only 889 is there a way to change this?

I have done the formatting and that works but when i need to use the cell for other formulas where it 0889 is need it only uses the 3 digits ( 889 )

so i need to insert a 0 in the front if there is only 3 digits..


please advise

ps. sorry if this is confusing.

here is the code:
Sub Get_regnr_byuser()

    Dim username As String
    Dim regnr As String
    Dim system As String
    
    On Error GoTo Errmsg:
    
    'username = Range("user")
    username = Worksheets("Admin").Range("user").Value
    system = Worksheets("Admin").Range("System").Value
    
    
If Left(username, 3) = "b37" Then

    ' FIT

    'Set myrange = Worksheets("Medarbejder_Liste").Range("E:G")
     Set myrange = Range("Medarbejder_Liste!E:G")

    regnr = Application.WorksheetFunction.VLookup(username, myrange, 3, False)

Else

    ' HITS
    
    'Set myrange = Worksheets("Medarbejder_Liste").Range("D:G")
    Set myrange = Range("Medarbejder_Liste!D:G")
      
    regnr = Application.WorksheetFunction.VLookup(username, myrange, 4, False)
    

End If
    
    Worksheets("Admin").Range("reg").Value = regnr
    'Worksheets("Admin").Range("reg").NumberFormat = "0####" 'This formats the cell
    
    If regnr = "" Then
        GoTo Errmsg
    End If
    
    Exit Sub
    
Errmsg:


    Call err_exit
    

End Sub

Open in new window

0
Hakum
Asked:
Hakum
  • 2
2 Solutions
 
Rob HensonFinance AnalystCommented:
When referring to a cell containing the 3 digits use the TEXT function to make it 4 digits:

=TEXT(A2,"0000")

Thanks
Rob
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this.....

Worksheets("Admin").Range("reg").NumberFormat = "@"
Worksheets("Admin").Range("reg").Value = regnr

Open in new window

0
 
HakumAuthor Commented:
Thank alot guys!!!! works like a charm Neeraj!
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Hakum! Glad to help.
And thanks for the feedback.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now