VBA to create formula - variable to how many cells contain value

[Tosborn]

Hi guys,

I have this code which works as intended but i need to expand it without much idea of where to begin.

the end result needs to look like this:

formula in cell:

="<<" &B1 & ","&B2 & ","&B3 & ","&B4 & ","&B5

actual value:

<<327020,327117,327123,327403,327410


My current code:

Sub JnlLookup()

'to create a large journal lookup from a list of journals

Dim i As Integer

    For i = 1 To Range("D1")

        Cells(4, 4).Formula = "=""<<"" &B" & i & " & "","" "
        
    Next i

End Sub

which creates the formula:

="<<" &B1 & ","

that is the value:

<<327020,

if I run this as step into, on each run it goes down my list in the B column to give me

<<327020,

then

<<327117,
etc

with my list in column B looking like this:
327020
327117
327123
327403
327410

and the cell in D1 has a value of 5 which is counting how many cells that contain values.

I need this macro to change as the list in column B expands or contracts to have more or less numbers.

I hope this is clear enough. Greatly appreciate any assistance at all.

Many thanks!

Tim

 

[GWteB]

Something like this ...

Sub JnlLookup()

    'to create a large journal lookup from a list of journals

    Dim i       As Integer
    Dim sTmp    As String

    sTmp = "=""<< """
    For i = 1 To Range("D1") - 1
        sTmp = sTmp & " & B" & i & " & "","""
    Next i
    sTmp = sTmp & " & B" & Range("D1") & ""
    Cells(4, 4).Formula = sTmp

End Sub

 

[Tosborn]

that's awesome, thanks a bunch!

 

[DanteAmor]

In case you want only the values of the cells. Here is a macro for you to consider.

Sub Test()
  [D4].Value = "<< " & Join(Application.Transpose(Range("B1:B" & [D1])), ",")
End Sub

 

[Tosborn]

In case you want only the values of the cells. Here is a macro for you to consider.

Sub Test()
  [D4].Value = "<< " & Join(Application.Transpose(Range("B1:B" & [D1])), ",")
End Sub

wow, nice short code & works a treat, thanks!