Modify data that a vba macro copies between sheets

[happohippo]

Hi. I am having some issues with VBA. I use an Excel formula found on this forum to find the Nth to last word in a cell. The formula is:

TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(CELL REFERENCE);" ";REPT(" ";60));60*n);60))

My VBA script finds specific strings from Sheet1 and copies these to their respective places in Sheet2.


I have been trying to apply this trimming function through VBA after extracting the data to Sheet2. This doesn't seem to work that well and requires me to use dummy sheet or dummy column to hold the formula. What I would like to do is to trim the output before copying to to Sheet2. My vba code is as follows:

Sub FindData()


    Dim datasheet As Worksheet
    Dim reportsheet As Worksheet


    Dim SearchString As String
    Dim i As Integer
    Dim j As Integer


    Set datasheet = Sheet1
    Set reportsheet = Sheet2


    Dim chNum As String
    Dim rptNum As String
    Dim ChangeDictionary As New Dictionary


    Dim dictKey1 As Variant
    Dim dictKey2 As Variant


    reportsheet.Range("A1:H200").ClearContents
    finalrow = datasheet.Cells(datasheet.Rows.Count, 1).End(xlUp).Row


    For i = 1 To finalrow
        SearchString = datasheet.Range("A" & i)


        If InStr(1, SearchString, "Change Number") Then
            chNum = datasheet.Cells(i, 1)
            ChangeDictionary.Add chNum, New Dictionary 'For report numbers
        ElseIf InStr(1, SearchString, "Report-") Then
            rptNum = datasheet.Cells(i, 1)
            ChangeDictionary.Item(chNum).Add rptNum, New Dictionary 'For details


            For j = 0 To 2
                ChangeDictionary.Item(chNum).Item(rptNum).Add j, datasheet.Cells(i, 1).Offset(j, 1) ' the details
            Next j
        End If
    Next i


    i = 1
    For Each dictKey1 In ChangeDictionary.Keys
        reportsheet.Cells(i, 1) = dictKey1


        If ChangeDictionary.Item(dictKey1).Count > 0 Then
            For Each dictKey2 In ChangeDictionary.Item(dictKey1).Keys
                reportsheet.Cells(i, 2) = dictKey2


                For j = 0 To 2
                    reportsheet.Cells(i, 4 + j) = ChangeDictionary.Item(dictKey1).Item(dictKey2).Item(j)
                Next j
                i = i + 1 'moves to new row for new report (or next change number
            Next dictKey2
        Else
            i = i + 1 'no reports, so moves down to prevent overwriting change number
        End If
    Next dictKey1
End Sub

I have tried a few things but mostly I get stuck with my inexperience with VBA. I tried trimming it both after it is saved and before it is printed but couldnt seem to figure it out. I used this code inside and it works okay after I figured that I can't just use the same formula directly (A1 is referenced just to test):

Worksheet3.Range("A1").Formula = "=TRIM(LEFT(RIGHT(char(32) & SUBSTITUTE(TRIM(reportsheet!A1), char(32), REPT(char(32),60)),180),60))"

Do you have any tips on where to start with this?

 

[wideboydixon]

Nth word of a string separated by spaces can be done in VBA much simpler like this:

wordNumber = 3
thirdWord = Split(Sheets("reportsheet").Range("A1").Value, " ")(wordNumber - 1)

WBD