Harsh Kumar
asked on
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:
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome Hakum! Glad to help.
And thanks for the feedback.
And thanks for the feedback.
ASKER