VBA - Attempt to Create A String From A Static Text String Added To A Number

[Ark68]

I am trying to create a string from a vvlookup result returning a number and a static text string.

trc_sb = "5"
.Cells(i, 8) = Application.WorksheetFunction.VLookup(en, ws_roster.Range("A2:B45"), 2, False) & "-" & trc_sb

The values in ws_roster.range A2:B45 are numeric. Example: 5.
Example trc_sb = "5"
Column 8 is by default custom formatted as "000" so that without the static string added, the result would be 005.

What I am trying to accomplish with my formula, using the example values would be "005-5"
What I am getting instead through testing is 43956

Looking for a solution to accomplish the result I seek.

 

[Legacy 456155]

have you tried either of these?

.Cells(i, 8).Text = Application.WorksheetFunction.VLookup(en, ws_roster.Range("A2:B45"), 2, False) & "-" & trc_sb

OR

.Cells(i, 8) = CSTR(Application.WorksheetFunction.VLookup(en, ws_roster.Range("A2:B45"), 2, False)) & "-" & trc_sb

Unfortunately, those may simply return "43956 " instead of 43956. Don't know, I haven't tried it.

 

[GWteB]

Use

.Cells(i, 8).Formula = CStr(Application.WorksheetFunction.VLookup(en, ws_roster.Range("A2:B45"), 2, False) & "-" & trc_sb)

to circumvent Excel's auto behavior, otherwise it will interpret your outcome as a date. The number 43956 is actualy (within Excel) equal to May 5th, 2020.

 

[Ark68]

Thanks guys. I appreciate all the effort. GWteB's solution, similar to dataluver's did the trick.
However, I need to have it formatted as "000-0". With th esolution provided, I am getting 5-5, whe n I am looking for 005-5. I'm still pretty shaky with the proper format function for numbers and I certainly wouldn't know where to apply the format in this formula.

 

[Fluff]

How about

   Dim Res As Variant
   trc_sb = "5"
   Res = Application.VLookup(en, ws_roster.Range("A2:B45"), 2, False)
   If Not IsError(Res) Then
      .Cells(i, 8).Value = Format(Res, "000") & "-" & trc_sb
   End If

 

[Ark68]

Thank you Fluff. Excatly what was needed.

 

[Fluff]

Glad we could help & thanks for the feedback