CODEWITHSUNDEEP
vbavariablesbrackets
inky
inky

Reputation: 137

Brackets prevent function from running

I am having difficulty getting my function to recognise the procedure because of where the brackets are.

The following code does not work.

Function

Public Function KonKatenate(rIN As range) As String
Dim r As range
For Each r In rIN
KonKatenate = Replace(KonKatenate & r.Text, ".", "")
Next r
End Function

Procedure

Sub LoopThroughUntilBlanks()

Dim xrg As range

Cells(3, 951).Select
' Set Do loop to stop when two consecutive empty cells are reached.
Application.ScreenUpdating = False
i = 3
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, -2).Value)
Cells(i, 951).Value = KonKatenate(range("AJE" & i & ":AJG" & i & ")"))
ActiveCell.Offset(1, 0).Select
i = i + 1
Loop
Application.ScreenUpdating = False
End Sub

When i completely remove the brackets and use for example a static number this works:

Cells(i, 951).Value = KonKatenate(range("AJE3:AJG3"))

However i need 3 to be a variable i so that the loop transcends down the row

Advice is much needed

Upvotes: 2

Views: 84

Answers (2)

paul bica
paul bica

Reputation: 10705

Another option, without loops

Option Explicit

Public Sub Kat_AJEtoAJG()
    Dim lrO As Long, lrM As Long

    With ThisWorkbook.Worksheets("Sheet3")       'or ThisWorkbook.Activesheet
        lrO = .Cells(.Rows.Count, "AJO").End(xlUp).Row
        lrM = .Cells(.Rows.Count, "AJM").End(xlUp).Row
        With .Range(.Cells(3, "AJO"), .Cells(IIf(lrO > lrM, lrO, lrM), "AJO"))
            .Formula = "=AJE3 & AJF3 & AJG3"
            .Value2 = .Value2
            .Replace ".", vbNullString
        End With
    End With
End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

Your KonKatenate function keeps overwriting its own result as it loops through the range. You need to keep concatenating the new new string onto the result. You didn't have a delimiter in your original but I've added an easy way to include one.

Public Function KonKatenate(rIN As range) As String
    Dim r As range, d as string

    d = ""

    For Each r In rIN
        KonKatenate = KonKatenate & d & Replace(r.Text, ".", "")
    Next r

    KonKatenate = mid(KonKatenate, len(d)+1)

End Function

Your LoopThroughUntilBlanks sub procedure should use the vars it declares and declare the vars it uses. A For ... Next loop may be more appropriate.

Sub LoopThroughUntilBlanks()
    dim lr as long, i as long

    Application.ScreenUpdating = False

    with activesheet    '<~~ would be better as a defined worksheet
        lr = application.max(.cells(.rows.coun, "AJO").end(xlup).row, _
                             .cells(.rows.coun, "AJO").Offset(0, -2).end(xlup).row)
        for i=3 to lr
            .Cells(i, "AJO").Value = KonKatenate(.range(.cells(i, "AJE"), .cells(i, "AJG")))
        next i
    end with

    Application.ScreenUpdating = False
End Sub

Upvotes: 2

Related Questions