[Urgent/Simple] I have a defined function and a defined range of cells. How do I do =MyFun

Here is what I have:

Dim Rngx2 As Range
Set Rngx2 = Sheets("Sheet2").Range("A1:A" & Range("A1").End(xlDown).Row)
Sheets("Sheet1").Range("A3").Formula = "=MyConcat(Rngx2)"

I want to use =MyFunct for that rng. How do I do that?

Something like... =MyFunct(rng) ... but it won't work.

Originally Posted by Norie

Perhaps.

Dim Rngx2 As Range
Set Rngx2 = Sheets("Sheet2").Range("A1:A" & Range("A1").End(xlDown).Row)
Sheets("Sheet1").Range("A3").Formula = "=MyConcat(" & Rngx2.Address(External:=True & ")"

Says there is a syntax error on the last line. Any idea?

Not sure exactly what you want but try one of these

Function MyConcat(myRange As Range) As String

    Dim cell As Range
    For Each cell In myRange
        MyConcat = MyConcat & cell.Value & Chr(10)
    Next cell
  MyConcat = left(MyConcat,len(MyConcat)-1)  

End Function




Function MyConcat(myRange As Range) As String

Dim cell As Range
For Each cell In myRange
if MyConcat = "" then
MyConcat = cell.Value
else        
if cell.Value = "" then
else
MyConcat = MyConcat & Chr(10) & cell.Value
end if    
end if
Next cell
End Function

Originally Posted by scottiex

Not sure exactly what you want but try one of these

Function MyConcat(myRange As Range) As String

    Dim cell As Range
    For Each cell In myRange
        MyConcat = MyConcat & cell.Value & Chr(10)
    Next cell
  MyConcat = left(MyConcat,len(MyConcat)-1)  

End Function




Function MyConcat(myRange As Range) As String

Dim cell As Range
For Each cell In myRange
if MyConcat = "" then
MyConcat = cell.Value
else        
if cell.Value = "" then
else
MyConcat = MyConcat & Chr(10) & cell.Value
end if    
end if
Next cell
End Function

SECOND ONE DID IT. Love you. Thanks!