Posts Tagged ‘excel’

build a string in excel vba and paste to cell range

Tuesday, June 29th, 2010

In cases where a cell range is looped through to create a filtered version , or re-ordered content.

I used to assign the string value to a textbox in a hidden form, then copy/paste that to a cell to get a result that spanned multiple cells and didn’t just all dump into one cell.

instead I now do this using the Form datatobject

thisString = OUT_HEADER + thisString
Dim DataObj As New MSForms.DataObject
DataObj.SetText thisString
DataObj.PutInClipboard
            thisString = OUT_HEADER + thisString

            Dim DataObj As New MSForms.DataObject

            DataObj.SetText thisString

            DataObj.PutInClipboard
then paste it somewhere
            Range("A1").Select
            Sheets(startSheet).Paste
esentially the same thing simplified and without an actual form