Excel VBA merging cell values

Question

I have a macro, where I convert text into columns, so that I can use the data from my PDF file.

Althought it works really well, I would like to know if it could be developed.
My data should look something like this:

Invoice    10-12-2017 USD        400,00  
Creditmemo 18-12-2017 USD        205,60  
Invoice    27-12-2017 USD        906,75  
Invoice    29-12,2017 USD        855,00  
Interest   I12391     31-12-2017 USD    105

As you might notice from the above, the "Interest" line has an additional number, which prevents the data from being aligned correctly.

I want it to look more like this:

[Invoice]               10-12-2017 USD    400,00  
[Creditmemo]            18-12-2017 USD    205,60  
[Invoice]               27-12-2017 USD    906,75  
[Invoice]               29-12,2017 USD    855,00  
[Interest I12391]       31-12-2017 USD       105

What I have so far:

My code is this atm:

rng1 = Range("A3:A750")

Range(rng1).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True

Goal:

I want the code to loop through my data, whenever it finds "Interest" it should copy the number and insert it after "Interest". Instead of being two different cells they should merge, and become one as "Interest I12391". Then the data will be alligned correctly, since the "Interest" row has one additional row, than the others

Should I do a .Find.Address on "Interest", and reuse the formula with a .Offset(0,1) and merge those two? Or is there a simpler and more efficient way to do so?

Other than the main question... Is it possible for me to use Range.TextToColums, so it only affects the worksheet I need it to, and not the entire workbook?

What you need is to concatenate values of columns `"A"` and `"B"`, place them in coulmn `"A"`, delete **Interest number** cells, am I right? What is format of column `"B"`? — AntiDrondert, Dec 22 '17 at 13:28
The thing is that I need to merge only the cell of column "A" and "B", whenever the word "Interest" appears, and not the entire column. I'm sorry for explaining badly in the above :) Column B is "Date" format, but whenever it gets to a row with "Interest", the format changes to general, since there is no date. Since the data has been taken from a PDF-file, it separates the text by spaces. But it shouldn't separate "Interest" and "I12391". — Patrick S, Dec 22 '17 at 13:35
My Column headers are: A = Description B = Due date C = Currency D = Amount — Patrick S, Dec 22 '17 at 13:39
You've added a **`VBA`** tag, have you tried anything in **`VBA`**?. Also, your description doesn't match what you are actually asking for.. just curious how they are linked? I would suggest a combination of `FORMULA` and `VBA` but a bit confused as too what kind of help you are looking for — Zac, Dec 22 '17 at 14:40

LetEpsilonBeLessThanZero · Answer 1 · 2017-12-22T16:09:51.540

Can you please try to use the following method and see if it gives you any success? To use it, you select the cells containing the data you're trying to fix and then you run this subroutine:

Public Sub dataFixer()
    Dim selectedDataRange() As Variant
    Dim selectedRow() As Variant
    Dim numberOfRowsInSelection As Integer
    Dim numberOfColsInSelection As Integer

    selectedDataRange = Selection 'Makes a 2D array from the user's selected range.
    numberOfRowsInSelection = UBound(selectedDataRange, 1)
    numberOfColsInSelection = UBound(selectedDataRange, 2)

    For i = 1 To numberOfRowsInSelection
        selectedRow = Application.Index(selectedDataRange, i, 0)     'Selects row i of the selectedDataRange
        If selectedRow(1) = "Interest" Then                          'If the first item in the row is "Interest" then...
            selectedRow(1) = selectedRow(1) & " " & selectedRow(2)   '...combine the first and second item
            For j = 2 To numberOfColsInSelection - 1
                selectedRow(j) = selectedRow(j + 1)                  '...and proceed to shift other items in the row to the left
            Next
        End If
        Selection.Rows(i) = selectedRow                              'Finally, "paste" the selected row to the corresponding row in the user's selected range
    Next                                                             'Proceed to the next row in the 2D array
End Sub

score 0 · Answer 2 · answered Dec 24 '17 at 14:25

Thank you for your answers. I have managed to make a string, which solves the problem afterall...

Dim sht As Worksheet
Dim rng9, rng10, c, Rw As Range
Set sht = Sheets("Kvik Kontoudtog")
Set rng9 = sht.Range(FindDescripOffset, DescripSub2)

    For Each c In rng9.Cells
        If c = "Rente" Then
        CValue = c.Address(False, False, xlA1)
        CValueOffset = Range(CValue).Offset(0, 1).Address(False, False, xlA1)
        Range(CValue).Value = Range(CValue).Value & " " & Range(CValueOffset).Value
        Range(CValueOffset).Delete Shift:=xlToLeft
        End If
    Next c

Excel VBA merging cell values

2 Answers2