none
Compile Error : Argument not optional RRS feed

  • Question

  • Hi there,

    When I try to execute this macro, I get the compile error as mentioned and just cannot see why! There is only one parameter which the function expects and I'm passing it a range [A1-J1].

    Here's what I need to do on over a hundred files ...

    Take the text from A1 - J1 and concatenate it with a delimiter. That's the function and it works a treat [I sourced this from the 'Net]. I then recorded a macro to do the necessary selection, copy and special pasting required.

    Can anyone please shed some light on this?

    Here's what I've got - am I not seeing the forest because of the trees?

    Function Join(source As Range) As String
        Dim sResult As String
        Dim oCell As Range
        
        delimiter = "|"
        
        For Each oCell In source.Cells
            If Len(oCell.Value) > 0 Then
                sResult = sResult + CStr(oCell.Value) + delimiter
            End If
         Next

        If Len(sResult) > 0 Then
            If Len(delimiter) > 0 Then
                sResult = Mid$(sResult, 1, Len(sResult) - Len(delimiter))
            End If
        End If
        Join = sResult
    End Function

    Sub Macro1()
        Range("K1").Select
        'Call the Join function on range A1:J1
        ActiveCell.FormulaR1C1 = "=join(RC[-10]:RC[-1])"
        'Next, autofill the contents in column 'K' based on the last entry in the 'I' column as cells in 'J' column can be empty.
        'The number of rows in the 'I' column varies for each file.
        Selection.AutoFill Destination:=Range("K1", Range.Offset(, -2).End(xlDown)), Type:=xlFillDefault
        Range("K1").End(xlDown).Select
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Save
    End Sub

    Wednesday, April 22, 2015 4:28 PM

Answers

  • Range("K1", Range.Offset(, -2).End(xlDown)) is not correct.

    Here is a simplified version of Macro1:

    Sub Macro1()
        Dim m As Long
        m = Range("I1").End(xlDown).Row
        Range("K1:K" & m).FormulaR1C1 = "=Join(RC[-10]:RC[-1])"
        Range("K1:K" & m).Copy
        Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        ActiveWorkbook.Save
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by W Underwood Wednesday, April 22, 2015 6:47 PM
    Wednesday, April 22, 2015 6:42 PM