MID and CONCATENATE in VBA

Question

I'm having the following issue: I recorded a Macro of me using some MID and CONCATENATE formulas after my macro reorders a ton of data. When I run the macro, I get a #REF error, which I understand. However, is there a VBA Code that could remove that or somehow use the MID and CONCATENATE without creating the #REF error? My code is below and any help would be much appreciated.

Sub Macro4()
'
' Macro4 Macro
'

'
    Sheets("Sheet2").Select
    Cells.Select
    Range("D29").Activate
    Selection.ClearContents
    Selection.End(xlUp).Select
    Selection.End(xlToLeft).Select
    Sheets("Sheet1").Select


'
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(16, 1), Array(21, 1), Array(37, 1), _
        Array(42, 1), Array(58, 1), Array(63, 1), Array(79, 1), Array(84, 1), Array(100, 1), Array( _
        105, 1), Array(121, 1), Array(129, 1)), TrailingMinusNumbers:=True
    Rows("1:6").Select
    Selection.Delete Shift:=xlUp
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Delete Shift:=xlToLeft
    Selection.Delete Shift:=xlToLeft

Dim lastRow&, g&
Dim findStr$

findStr = "Planning of"

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For g = lastRow To 1 Step -1 ' change this to 2 if you have headers
    If Cells(g, 1).Value = findStr Then
        'Range(Rows(i), Rows(i - 4)).Select
        Range(Rows(g), Rows(g - 4)).EntireRow.Delete
    End If
Next g

Dim arr() As Variant
Dim p As Integer, i&
Dim ws As Worksheet
Dim tws As Worksheet
Dim t As Integer
Dim c As Long
Dim u As Long



Set ws = ActiveSheet
Set tws = Worksheets("Sheet2")
i = 1
With ws
Do Until i > 100000
    u = 0
    For c = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
        'If c = .Cells(1, .Columns.Count).End(xlToLeft).Column And .Cells(i, c) <> "" Then
        ReDim arr(0) As Variant
        p = 0
        t = 0
            Do Until .Cells(i + p, c) = "" And t = 1
                If .Cells(i + p, c) = "" Then
                    t = 1

                Else
                    arr(UBound(arr)) = .Cells(i + p, c)
                    ReDim Preserve arr(UBound(arr) + 1)
                End If
                p = p + 1
            Loop

        If p > u Then
            u = p

        End If
        If c = .Cells(1, .Columns.Count).End(xlToLeft).Column Then
            If .Cells(i + p, c).End(xlDown).Row > 100000 And .Cells(i + p, 1).End(xlDown).Row < 100000 Then
                i = .Cells(i + u, 1).End(xlDown).Row
            Else
                i = .Cells(i + p, c).End(xlDown).Row
            End If

        End If
        tws.Cells(tws.Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(arr) + 1) = arr

    Next c

Loop
End With
With tws
    .Rows(1).Delete
    For i = .Cells(1, 1).End(xlDown).Row To 2 Step -1
        If Left(.Cells(i, 1), 4) <> Left(.Cells(i - 1, 1), 4) Then
            .Rows(i).EntireRow.Insert
        End If
    Next i
End With

'
' Macro6 Macro
'

'
    'Sheets("Sheet2").Select
    'Range("A1:M67").Select
    'Selection.Copy
    'Sheets("Output").Select
    'Range("A3").Select
    'ActiveSheet.Paste
    'Range("A1").Select

    ActiveCell.FormulaR1C1 = "=IF(Sheet2!RC="""","""",Sheet2!RC)"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[1],5,4)"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC,1,3)"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC,6,3)"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-1],1,4)"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-2],10,4)"
    Range("F2").Select
    Columns("F:F").EntireColumn.AutoFit
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-3])"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-3],5,3)"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-3],6,3)"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-4],1,4)"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-5],10,4)"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-5],6,3)"
    Range("K2").Select
    Columns("K:K").EntireColumn.AutoFit
    Range("G1").Select
    Selection.Copy
    Range("K1").Select
    ActiveSheet.Paste
    Range("L1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-6],6,3)"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-7],1,4)"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-8],10,4)"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("K1").Select
    Selection.Copy
    Range("O1").Select
    ActiveSheet.Paste
    Range("O1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]="""","""",RC[-3])"
    Range("P1").Select
    Columns("P:P").ColumnWidth = 7.71
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-9],6,3)"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-10],1,4)"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-11],10,4)"
    Range("O1").Select
    Selection.Copy
    Range("S1").Select
    ActiveSheet.Paste
    Range("T1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-12],6,3)"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-13],1,4)"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-14],10,3)"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-14],10,4)"
    Range("V2").Select
    Columns("U:U").EntireColumn.AutoFit
    Range("S1").Select
    Selection.Copy
    Range("W1").Select
    ActiveSheet.Paste
    Range("X1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-15],6,3)"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!R[10]C[-16],1,4)"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-16],1,4)"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("W1").Select
    Selection.Copy
    Range("Z1").Select
    ActiveSheet.Paste
    Range("Z1").Select
    Columns("Z:Z").EntireColumn.AutoFit
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-17],10,4)"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("W1").Select
    Selection.Copy
    Range("AA1").Select
    ActiveSheet.Paste
    Range("AB1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-18],6,3)"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "=Sheet2!R[113]C[-19]"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "eet2!J114"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-19],1,4)"
    Range("AA1").Select
    Selection.Copy
    Range("AD1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MID(Sheet2!RC[-20],10,4)"
    Range("W1").Select
    Selection.Copy
    Range("AD1").Select
    ActiveSheet.Paste
    Range("AD1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(OR(RC[-3]="""",AE=""""),"""",RC[-3])"
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "=IF(OR(RC[-3]="""",RC[1]=""""),"""",RC[-3])"
    Range("A1:AD1").Select
    Selection.AutoFill Destination:=Range("A1:AD123"), Type:=xlFillDefault
    Range("A1:AD123").Select
    ActiveWindow.SmallScroll Down:=-108
    Range("V23").Select
    ActiveWindow.SmallScroll Down:=-21
    Columns("AD:AD").Select
    Selection.Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Columns("K:K").Select
    ActiveSheet.Paste
    Columns("O:O").Select
    ActiveSheet.Paste
    Columns("S:S").Select
    ActiveSheet.Paste
    Columns("W:W").Select
    ActiveSheet.Paste
    Columns("AA:AA").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=-27
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.End(xlToLeft).Select
    Sheets("Sheet4").Select
    Range("A3").Select
    ActiveWindow.SmallScroll Down:=-6
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(Sheet3!R[-2]C="""","""",Sheet3!R[-2]C)"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C,"" "",Sheet3!R[-2]C[1],Sheet3!R[-2]C[2],"" "",Sheet3!R[-2]C[3])"
    Range("B3").Select
    Selection.Copy
    Range("C3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[3],"" "",Sheet3!R[-2]C[4],Sheet3!R[-2]C[5],"" "",Sheet3!R[-2]C[6])"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[6],"" "",Sheet3!R[-2]C[7],Sheet3!R[-2]C[8],"" "",Sheet3!R[-2]C[9])"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[9],"" "",Sheet3!R[-2]C[10],Sheet3!R[-2]C[11],"" "",Sheet3!R[-2]C[12])"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[12],"" "",Sheet3!R[-2]C[13],Sheet3!R[-2]C[14],"" "",Sheet3!R[-2]C[15])"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[15],"" "",Sheet3!R[-2]C[16],Sheet3!R[-2]C[17], ,Sheet3!R[-2]C[18])"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[15],"" "",Sheet3!R[-2]C[16],Sheet3!R[-2]C[17],"" "",Sheet3!R[-2]C[18])"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[18],"" "",Sheet3!R[-2]C[19],Sheet3!R[-2]C[20],"" "")"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "=CONCATENATE(Sheet3!R[-2]C[18],"" "",Sheet3!R[-2]C[19],Sheet3!R[-2]C[20],"" "",Sheet3!R[-2]C[21])"
    Range("H4").Select
    Sheets("Sheet3").Select
    ActiveWindow.SmallScroll Down:=-12
    Sheets("Sheet4").Select
    Range("A3:H3").Select
    Selection.AutoFill Destination:=Range("A3:H193"), Type:=xlFillDefault
    Range("A3:H193").Select
    ActiveWindow.SmallScroll Down:=-201
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Output").Select
    Range("A1").Select
    ActiveWindow.SmallScroll Down:=-12
    Sheets("Sheet4").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet4").Select
    ActiveWindow.SmallScroll Down:=-48
    Range("A116").Select
    Selection.End(xlUp).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Output").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Avoid using selection, it will slow your code down you can usually replace the select and selection in the next line, and cobine the two lines of code. For instance you can change: `Columns("A:A").Select Selection.Delete Shift:=xlToLeft` into `Columns("A:A").Delete Shift:=xlToLeft` — Forward Ed, May 17 '16 at 16:40
Also, read through [this thread](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for some great tips. Also, you keep doing `Columns("A:A")...` and repeat with B, C, D, etc. You could just do `Columns("A:G").Delete Shift:=xlToLeft`, saving some space. — BruceWayne, May 17 '16 at 16:47

score 0 · Answer 1 · answered May 18 '16 at 16:08

0

I couldn't figure out how to fix the VBA so I simply ran the code without the MID and CONCATENATE in there. Once I did that, I copy and pasted my results into a new tab and entered the equations.

answered May 18 '16 at 16:08

Ben

75
7

MID and CONCATENATE in VBA

1 Answers1