Issue with programming.

Hi guys,

I am doing a function in MS Excel, the function should concatenate e-mail adrresses separated by semicolons. I will put The e-mails in the column A from a worksheet named BDD. The VB code does not work properly, because when I tried to put the whole string on the content of the cell using either one of the following statements, I get an error message 1004 in execution.

'        ActiveCell.Formula = cadena_de_correos(5, Columna_1, Fila_1)
'        Cells(Fila_2, Columna_2) = cadena_de_correos(5, Columna_1, Fila_1)

Cadena_de_correos() is a string type function that creates a string with all the e-mails separated by semicolons and will return it.

Here it is:

Enclosed is the worksheet and its code and examples. If you debug it you see that the concatenate is working all right but the issue is in that statement, I do not know why, because it works in other functions that I have created previously.

I could not upload the file, because it exceeds the limit for the forum. So I add further information to understand the code enclosed here.

There are two sheets, the first one is named "BDD" and the second one is named "GENERADOR DE LISTAS DE CORREO" where there is a button that executes the private sub CommandButton2_Click(). Finally, you should put in the column A from the worksheet "BDD" all the e-mail addresses in the differente rows starting from row 1 to n consecutively. If you click on the button on execution, you can see the error. If you debug it and brake on the specific line, you can see that everything is being done properly but assigning the value to the cell.

Please let me know if there is any question.

Private Sub CommandButton2_Click()
Dim Celda_Completa_2
Dim Columna_2, Fila_2
Dim TMP2
Dim Fila_1 As Integer
Dim Columna_1 As String

    Fila_2 = 1
    Columna_1 = "A"
    Fila_1 = Fila_1 + 1
    Celda_Completa_1 = Columna_1 & Fila_1
    Range("A1").Select
    TMP2 = Worksheets("BDD").Range(Celda_Completa_1).FormulaR1C1
    Do While (TMP2 <> "")
        ActiveCell.Formula = cadena_de_correos(5, Columna_1, Fila_1)
'        Cells(Fila_2, Columna_2) = cadena_de_correos(5, Columna_1, Fila_1)
        Celda_Completa_2 = Columna_2 & Fila_2
        Range(Celda_Completa_2).Select
        Fila_2 = Fila_2 + 1
        Celda_Completa_2 = Columna_2 & Fila_2
        Range(Celda_Completa_2).Select
    Loop
    Sheets("GENERADOR DE LISTAS DE CORREO").Select
    Range("A1").Select
    MsgBox "YA PUEDE CHEQUEAR SU BASE DE DATOS"
'**************************************************
' ***********  PASAR A VALORES  *******************
'**************************************************
'    Range("A1:A6000").Select
'    Selection.Copy
'    ActiveWindow.SmallScroll Down:=-24
'    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'        :=False, Transpose:=False
'    Range("A1").Select
'**************************************************
' ***********  FIN PASAR A VALORES  ***************
'**************************************************
End Sub

Public Function cadena_de_correos(Maximo_ini As Integer, ByVal Columna_1 As String, ByRef Fila_1 As Integer) As String
    Dim Celda_Completa_1 'Para la hoja de calculo BDD
    Dim TMP
    Dim Cadena
    

    contador = 0
    Columna_1 = "A"
    Fila_1 = Fila_1 + 1
    Celda_Completa_1 = Columna_1 & Fila_1
    TMP = Worksheets("BDD").Range(Celda_Completa_1).FormulaR1C1
    Cadena = "=CONCATENATE(" & TMP
    Do While ((contador < Maximo_ini) And (TMP <> ""))
        Fila_1 = Fila_1 + 1
        Celda_Completa_1 = Columna_1 & Fila_1
        TMP = Worksheets("BDD").Range(Celda_Completa_1).FormulaR1C1
        Cadena = Cadena & ";" & TMP
        contador = contador + 1
    Loop
    Cadena = Cadena & ")"
    cadena_de_correos = Cadena
End Function

alejojauregui

Please do not ignore a moderators request

rylo asked you to do 3 things. You have done 1 of them.
Ignoring rylo's other 2 requests may see your thread closed

Your reply also breaks one of the rules that you were asked to fix in your 1st posting

Now Please take a couple of minutes and read the Forum Rules then wrap your VBA code (Rule 3)