Pasting string with tabs into row so that is goes across columns. 1

[Stoffel24]

I have a string that is going to be the headings for my columns in an excel spreadsheet. I have created the string with the headings separated by tabs. I know that if you try to paste a string with tabs from say notepad into a cell in column 1, the string is pasted across the columns (ie the tabs cause the components of the string to be split so that each one goes into a separate column.)


How can I paste a string into a cell and get it to behave as I want?

strHdr = "Date" & Chr(9) & "Average(X)" & Chr(9) & "LCL(X)" & Chr(9) & "UCL(X)" & Chr(9) & _
"Variance" & Chr(9) & "LCL(R)" & Chr(9) & "UCL(R)" & Chr(9) & "Average(R)" & Chr(9) & _
"Certified Value"

Sheets(2).Cells(StartRow, 1).Value = strHdr

This puts the entire string into the first cell.
Thanks

 

[xlbo]

Suggest you paste it into 1 cell and then record yourself using the Data>TextTocolumns function - this should do the trick for you

Other than that, you could try using an array

Dim HeadArr(8) as variant
HeadArr = array("Date","Average(X)","LCL(X)" etc etc)
for i = 0 to 8
Range(cells(1,i+1),cells(1,i+1)).formula = HeadArr(i)
next i

HTH
~Geoff~

 

[Stoffel24]

Thanks Geoff. Your code got me on the right track. I had to make some minor corrections as shown below.


Dim strHdr As Variant
strHdr = Array("Date", "Average(X)", "LCL(X)", "UCL(X)", "Variance", "LCL(R)", "UCL(R)", "Average(R)", "Certified Value&quot

For p = 1 To 9
Sheets(2).Cells(StartRow + 2, p).Value = strHdr(p - 1)

Next