?
Solved

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

Posted on 2016-08-09
4
Medium Priority
?
83 Views
Last Modified: 2016-08-09
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
Comment
Question by:Hakum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 1000 total points
ID: 41748878
When referring to a cell containing the 3 digits use the TEXT function to make it 4 digits:

=TEXT(A2,"0000")

Thanks
Rob
0
 
LVL 33

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 1000 total points
ID: 41748883
Please try this.....

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

Open in new window

0
 
LVL 1

Author Closing Comment

by:Hakum
ID: 41748888
Thank alot guys!!!! works like a charm Neeraj!
0
 
LVL 33

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41749040
You're welcome Hakum! Glad to help.
And thanks for the feedback.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question