Converting Cell to Text Question

[ummjay]

Hi there! I have a vba which adds a formula in I9:last row "=G9&J9". For some reason this formula is not working properly. When I change J9 to Text, then go back into the formula and hit enter it works. Is there a way to resolve this? Or do I need to change J9:last row to Text? If that's the fix, any suggestions how I can do that? I tried the below and visually it changed to text, but didnt correct my formula issues, and i'm calling the conversion before I insert the formula macro:

'change Contact to Text
        ActiveSheet.Range("J9:J" & lastrow).NumberFormat = "@"

ActiveSheet.Range("I9:I" & lastrow).Formula = "=G9&J9"

Thanks!

 

[Joe4]

Please show us a sample of your data and expected output.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ummjay]

See below of example worksheet. I want column I to have the formula to combine G1 and J1. Issue is, I pulls G1, but not J1 into the formula after running. If I change J1 to Text, click in the I1 formula and hit enter, it then populates as expected TextGtextJ23. Not sure what I need to fix here. thanks!

A	B	C	D	E	F	G	H	I	J
1						textG	textH	=G1&J1	textJ23
2
3

 

[Joe4]

I want to see your ACTUAL data and what it is actually doing, not some oversimplified version of what you are doing.
Often times, "the devil is in the details", so it is important to see the actual details, instead of some over-generalization.

So if you could post an accurate depiction of the problem, or upload the workbook to somewhere where we can download it, it would be most helpful.