Problem with pasting a concatenate value using VBA

[wongsh]

Hey guys, I am trying to debug the VBA script of a recorded macro (As shown below).

I should be expecting the result as "text1 / text2 / text3", but instead it returns as "text1text2text3". Can anyone enlighten me on that?

Appreciate your help! (I am very new to VBA)
:yes
Range("J95").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = " / "
Range("J95").Select
Selection.FillDown
Selection.End(xlDown).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.End(xlUp).Select
Range("K95").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"
Range("K95").Select
Selection.Copy
Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[SamT]

The first step is just to make it more readable

Range("J95").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = " / "

Range("J95").Select
Selection.FillDown
Selection.End(xlDown).Select
Selection.ClearContents
Selection.End(xlUp).Select
Selection.End(xlUp).Select

Range("K95").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"

Range("K95").Select
Selection.Copy

Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[SamT]

Then we can work on the separate part of the code


Range("J95").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = " / "Becomes

Range("J95").Value = " / "


With Comments
'Clear J95 to bottom
Range("J95").Select
Selection.FillDown 'Pressed Ctrl+D
Selection.End(xlDown).Select 'Oops. Pressed Ctrl+ Down Arrow
Selection.ClearContents
Selection.End(xlUp).Select 'Pressed Ctrl+ Up Arrow
Selection.End(xlUp).Select 'Pressed Ctrl+ Up Arrow
Becomes

Range(Range("J95"), Range("J95").End(xlDown).ClearContents


Range("K95").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"

Range("K95").Select
Selection.Copy
Becomes

Range("K95").FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"

Range("K95").Copy


Range("I4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Becomes

Range("I4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

[SamT]

putting the cleaned up parts together, we get


Range("J95").Value = " / "
Range(Range("J95"), Range("J95").End(xlDown).ClearContents

Range("K95").FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"

Range("K95").Copy
Range("I4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("J95").Value = " / " is wrong because we immediately clear it. That is why you're not getting the slashes. You can put them into the Concatenation, but you might run into the limit. CONCATENATE(RC[-2]," /",RC[-1]," /",R[1]C[-2]," /", etc


The next line is better written Range(Range("J95"), Cells(Rows.Count, "J").End(xlUp)).ClearContents

The I4 paste operation doesn't need all the parameters: Range("I4").PasteSpecial Paste:=xlPasteValues

The result is, minus the slashes
Range(Range("J95"), Cells(Rows.Count, "J").End(xlUp)).ClearContents

Range("K95").FormulaR1C1 = _
"=CONCATENATE(RC[-2],RC[-1],R[1]C[-2],R[1]C[-1],R[2]C[-2],R[2]C[-1],R[3]C[-2],R[3]C[-1],R[4]C[-2],R[4]C[-1],R[5]C[-2],R[5]C[-1],R[6]C[-2],R[6]C[-1],R[7]C[-2],R[7]C[-1],R[8]C[-2],R[8]C[-1],R[9]C[-2],R[9]C[-1])"

Range("K95").Copy
Range("I4").PasteSpecial Paste:=xlPasteValues

[SamT]

Fortunately VBA has no small Concatenation Limit, or at least, not doing it this way

Const sl As String = " / "
Dim tmp As String

With Range("K95")
Tmp = .Offset(, -2) & sl & .Offset(, -1) _
& sl & .Offset(1, -2) & sl & .Offset(1, -1) _
& sl & .Offset(2, -2) & sl & .Offset(2, -1) _
& sl & .Offset(3, -2) & .Offset(3, -1) _
& sl & .Offset(4, -2) & sl & .Offset(4, -1) _
& sl & .Offset(4, -2) & sl & .Offset(5, -1) _
& sl & .Offset(6, -2) & sl & .Offset(6, -1) _
& sl & .Offset(7, -2) & sl & .Offset(7, -1) _
& sl & .Offset(8, -2) & sl & .Offset(8, -1) _
& sl & .Offset(9, -2) & sl & .Offset(9, -1)
End With

Range("I4") = tmp
Hmmnnn. I might have an error or two in there.

BTW, that replaces all the previous code in this thread.

I had to insert the errors because I only wrote the first two lines, then copied the second line down 8 times and then edited the row numbers. This was to demonstrate "self checking" code structures.

[SamT]

And finally we can perfect the code

Const sl As String = " / "
Dim tmp As String

With Range("I95")
Tmp = .Value & sl & .Offset(0, 1) _
& sl & .Offset(1) & sl & .Offset(1, 1) _
& sl & .Offset(2) & sl & .Offset(2, 1) _
& sl & .Offset(3) & sl & .Offset(3, 1) _
& sl & .Offset(4) & sl & .Offset(4, 1) _
& sl & .Offset(5) & sl & .Offset(5, 1) _
& sl & .Offset(6) & sl & .Offset(6, 1) _
& sl & .Offset(7) & sl & .Offset(7, 1) _
& sl & .Offset(8) & sl & .Offset(8, 1) _
& sl & .Offset(9) & sl & .Offset(9, 1)
End With

Range("I4") = tmp

[SamT]

With experience in how VBA and Excel work to manage Cells, we can put it all in a loop

Dim tmp As String
Dim i as Long

With Range("I95:J104")
for i = 1 to 19
tmp = tmp & .Cells(i).Value & " / "
Next i
tmp = tmp & .Cells(20).Value
End With

Range("I4") = tmp

[wongsh]

Thanks for your great help and thanks for taking up your time to show step-by-step SamT!!!

Sorry but I have one more question here, my intention is that the "/" will end before the last value that I input.

E.g. if I only key in 5 values in the 10 cells, the result should be "text1 / text2 / text3 / text4 / text5", but now it is showing "text1 / text2 / text3 / text4 / text5 / / / / /"

Could you pls help me on that? Sorry that I did not specify this in my post earlier on!

[SamT]

Count to the rescue! Count and CountA return the number of cells in a range that contain something. They are different, but in this case, Count will do.

This will fail if there is only one cell with something in it, because that would make c = 0.

Dim tmp As String
Dim i As Long
Dim c As Long

With Range("I95:J104")
c = Count(.Cells)
For i = 1 To c - 1
tmp = tmp & .Cells(i).Value & " / "
Next i
tmp = tmp & .Cells(c).Value
End With

Range("I4") = tmp

[wongsh]

Wow great!! Thanks so much again for your help!!!
Noted on that but there will always be more than one populated cell ;)

Once again thanks so much!!! Really appreciate your help!!!