Issue with HorizontalAlignment

[yoditron]

Hello everyone, today my VBA knowledge was put to the test... and I failed. Please help if you can..... pretty pleeeease!
I have around 20 rows of data with 7 columns.
All columns have multi-line cells; on one, I have first name on the first line and last name on the second; all values populate from a different sheet as part of a fairly large Sub.
Here's how I populate it: wsTargetWorksheet.Cells(1, 1) = wsSourceWorksheet.Cells(1, 1) & vbCrLf & wsSourceWorksheet.Cells(1, 2), where wsTargetWorksheet and wsSourceWorksheet are handles for the Source and Target files. I replaced most variables with numbers from this statement for easy reading.

After I populate all multi-line cells with data, I apply horizontal alignment: wsTargetWorksheet.Range(Cells(1, 1), Cells(20, 7)).HorizontalAlignment = xlCenter (replaced all variables with numbers for easy reading)

The weird thing is, xlCenter only applies to the last line on each cell, so it centers the Last Name but it does not center the First Name... all cells on the sheet suffer from the same issue.

More info:
-I applied the same HorizontalAlignment with xlCenter statement on different parts of the Sub. The result is the same, it centers the second line on a cell, but not the first. Also tried applying HorizontalAlignment with xlCenter to only 1 column, to no avail.
-If, after the Sub is done, I apply other formats to the data, manually (clicking the mouse), in Excel like Align Left, it works, aligning both lines to the Left. If I immediately click on Align Center, it goes back to the first line not centering and the second line centering...
-When I tried these 2 lines of code (populating the cell and horizontally aligning it) on a new sheet, recreating the same scenario on a smaller environment, it actually works and it centers both lines on the cell. Go figure.
-If, after the Sub is done, I double-click on a cell to edit it, it immediately corrects the issue, centering both lines on the cell. Weeeeeird!
-Not sure if it matters but the first 2 lines of the Sheet are headers and they are Merged and Centered.

Could it be a memory problem? Is there a way to "refresh" the format on a cell through VBA? Is there a better way to Horizontally Align multi-lined cells? Any other workaround? Although I would prefer that somebody tells me I suck at VBA and I am missing something sooo obvious.....

 

[myall_blues]

It looks to me possibly that one or other or both of your cells that go into the multi-line input have leading or trailing spaces, causing it to appear unaligned. For example, consider image below where I have replaced spaces with underscores for visibility.

Try trimming your data into the multi-line like so:

wsTargetWorksheet.Cells(1, 1) = Trim(wsSourceWorksheet.Cells(1, 1)) & vbCrLf & Trim(wsSourceWorksheet.Cells(1, 2))

 

[yoditron]

It looks to me possibly that one or other or both of your cells that go into the multi-line input have leading or trailing spaces, causing it to appear unaligned. For example, consider image below where I have replaced spaces with underscores for visibility.
View attachment 115849

Try trimming your data into the multi-line like so:

wsTargetWorksheet.Cells(1, 1) = Trim(wsSourceWorksheet.Cells(1, 1)) & vbCrLf & Trim(wsSourceWorksheet.Cells(1, 2))

Thank you, myall_blues. I checked for leading/trailing spaces, could not find any, even so I tried adding Trim to my code as you suggested, but the result is the same. It is weird because as soon as I double-click on the cell, it center-aligns immediately without me doing anything else, that is why I think it is a memory issue.

 

[jolivanes]

If you use Value, does that make a difference?

wsTargetWorksheet.Cells(1, 1).Value = wsSourceWorksheet.Cells(1, 1).Value & vbCrLf & wsSourceWorksheet.Cells(1, 2).Value

Or try adding calculate after above line.

 

[Peter_SSs]

Try changing vbCrLf to vbLf

wsTargetWorksheet.Range(Cells(1, 1), Cells(20, 7)).HorizontalAlignment = xlCenter

Also note that if that is your actual line of code, it will error if wsTargetWorksheet is not the active sheet when that line is run

 

[yoditron]

If you use Value, does that make a difference?

wsTargetWorksheet.Cells(1, 1).Value = wsSourceWorksheet.Cells(1, 1).Value & vbCrLf & wsSourceWorksheet.Cells(1, 2).Value

Or try adding calculate after above line.

I was trying to look for something like that, thank you Jolivanes, unfortunately Calculate did not do the trick...

 

[yoditron]

I was trying to look for something like that, thank you Jolivanes, unfortunately Calculate did not do the trick...

also tried .Value... no luck.

 

[yoditron]

Try changing vbCrLf to vbLf



Also note that if that is your actual line of code, it will error if wsTargetWorksheet is not the active sheet when that line is run

OMG!!! Finally!!!! This worked like a charm!!!! Thank you, thank you, thank you Peter_SSs! I know it is not a big issue but my OCD was preventing me from moving on with my life!!

 

[Peter_SSs]

You're welcome. Glad to help.