my VBA Vlookup function pasting number instead of the original text

[cceze]

I need help (first timer!).

I have a little vlookup VBA to paste a text but it keeps pasting a number.

(Application.WorksheetFunction.VLookup(Range("C1").Offset(i, 0), Sheets("example").Range("A:I"), 5, 0)


example -- C1 has the lookup 4000 and the column 5 has a text 044356

the vlookup displays 44,356

any help will be appreciated

 

[Joe4]

Welcome to the Board!

column 5 has a text 044356

Are you sure it is really text, and not a number with a custom format?
I suggest checking to see how column I is formatted.

 

[cceze]

I tried to do a text to column conversion manually but when i run the macros, it still give me the number. Is there a way to change the custom format through VBA?

 

[Joe4]

Did you do what I asked?
Is column I a number with a Custom format?

If it is, and you want the value you are bringing back to be formatted in the same manner, then you will either need to apply the same custom format to the cell returning the value, or change the result to text with the custom format right in the formula (using TEXT function in Excel formula, or using FORMAT function in VBA).

 

[cceze]

oh yes it it is custom format.

 

[Joe4]

VLOOKUP only returns value, not formatting.

If you remove the formatting from column I, you will see that the value really is 44356.

If you have trouble applying the solutions mentioned in my previous post and need help applying it, please post the whole relevant block of VBA code (and not just the one line).

 

[cceze]

here is the VBA. I really appreciate the help. been trying it all yesterday

Sub text()


Sheets("Sheet1").Range("b1").Offset(1, 0) = Application.WorksheetFunction.VLookup(Range("a1").Offset(1, 0), Sheets("Sheet2").Range("A:E"), 5, 0)


End Sub

 

[Joe4]

Let's just apply the same Custom formatting to the cell we are pasting to as in the cell we are pulling the information from:

[COLOR=#333333]Sub text()[/COLOR]

[COLOR=#333333]    Sheets("Sheet1").Range("b1").Offset(1, 0) = Application.WorksheetFunction.VLookup(Range("a1").Offset(1, 0), Sheets("Sheet2").Range("A:E"), 5, 0)[/COLOR]
[COLOR=#333333]    Sheets("Sheet1").Range("b1").Offset(1, 0)[/COLOR].NumberFormat = "000000"

[COLOR=#333333]End Sub[/COLOR]

 

[cceze]

It worked! thanks! I can now apply it to all my VBA!

 

[Joe4]

You are welcome!