Looping concatenate based on input range

[4fun123]

Hello,

I need loop that would create concatenate from fixed values (strings) and loop it based on input range (dynamic, user input).

For example, "create image", input range 001-245, ".jpg", "image created", input range [same as 1st one].
Result would give create image 001.jpg image created 001

This is simplified sample as it would have multiple strings and input range would be.
Thanks!

 

[rlv01]

That is not enough information to do anything with. Use XL2BB to post a few rows of sample data and what you want the output to look like for each.

 

[4fun123]

Hello, thank you for answering, here is sheet explaining little better what i want (first 2 rows is how i want to "serve") 4-8th row are outputs of that. Cheers

 

[rlv01]

Posting your data as an image instead of using the XL2BB tool, you make it so that I would have to manually type in the above instead of just copy/paste it into my worksheet if I want play around with it.

Groceries.xlsx
	A	B	C	D	E
1	Store	Price		Qty	Total
2	Trader Joe	$ 3.25	Milk	1	$ 3.25
3	Whole Foods	$ 4.99	Blueberries	2	$ 9.98
4	Whole Foods	$ 3.99	Yogurt	3	$ 11.97
5	Whole Foods	$ 6.98	Chicken	1	$ 6.98
6	Safeway	$ 6.00	Apple Juice	3	$ 18.00
7					$ 50.18
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=B2*D2
E7	E7	=SUM(E2:E6)

 

[4fun123]

Posting your data as an image instead of using the XL2BB tool, you make it so that I would have to manually type in the above instead of just copy/paste it into my worksheet if I want play around with it.

View attachment 62676

Groceries.xlsx
	A	B	C	D	E
1	Store	Price		Qty	Total
2	Trader Joe	$ 3.25	Milk	1	$ 3.25
3	Whole Foods	$ 4.99	Blueberries	2	$ 9.98
4	Whole Foods	$ 3.99	Yogurt	3	$ 11.97
5	Whole Foods	$ 6.98	Chicken	1	$ 6.98
6	Safeway	$ 6.00	Apple Juice	3	$ 18.00
7					$ 50.18
Sheet1

Cell Formulas
Range	Formula
E2:E6	E2	=B2*D2
E7	E7	=SUM(E2:E6)

Sorry about that, here is pasting in XL2bb, thanks!

Book3
	A	B	C	D	E	F	G	H	I
1	string	str	str	input	str	str	input	str	concatenate
2	create	new	image-	101-104	.jpg	image	101-104	created
3
4	string	str	str	input	str	str	input	str	concatenate
5	create	new	image-	101	.jpg	image	101	created	create new image-101.jpg image 101 created
6	create	new	image-	102	.jpg	image	102	created	create new image-102.jpg image 102 created
7	create	new	image-	103	.jpg	image	103	created	create new image-103.jpg image 103 created
8	create	new	image-	104	.jpg	image	104	created	create new image-104.jpg image 104 created
Sheet1

Cell Formulas
Range	Formula
I5:I8	I5	=CONCATENATE(A5,B5,C5,D5,E5,F5,G5,H5)

 

[bebo021999]

What happen with CONCATENATE function?
Does it give expected result?

 

[rlv01]

Perhaps something like this

Sub DoSomething()
    Dim WS As Worksheet
    Dim CellRange As Range, R As Range
    Dim I As Long, J As Long
    Dim S As String
    Dim SA As Variant
   
    Set WS = ActiveSheet

    Set CellRange = WS.Range("A2:I2")

    SA = Split(WS.Range("D2").Value, "-")

    J = 3
    With CellRange
        For I = Val(SA(0)) To Val(SA(1))
            .Copy .Offset(J)
            .Range("D1").Offset(J).Value = I
            .Range("G1").Offset(J).Value = I

            S = ""
            For Each R In .Offset(J)
                S = S & R.Value & " "
            Next R

            .Range("I1").Offset(J).Value = Replace(S, "- ", "-")
            J = J + 1
        Next I
    End With
End Sub

 

[4fun123]

Perhaps something like this

Sub DoSomething()
    Dim WS As Worksheet
    Dim CellRange As Range, R As Range
    Dim I As Long, J As Long
    Dim S As String
    Dim SA As Variant
  
    Set WS = ActiveSheet

    Set CellRange = WS.Range("A2:I2")

    SA = Split(WS.Range("D2").Value, "-")

    J = 3
    With CellRange
        For I = Val(SA(0)) To Val(SA(1))
            .Copy .Offset(J)
            .Range("D1").Offset(J).Value = I
            .Range("G1").Offset(J).Value = I

            S = ""
            For Each R In .Offset(J)
                S = S & R.Value & " "
            Next R

            .Range("I1").Offset(J).Value = Replace(S, "- ", "-")
            J = J + 1
        Next I
    End With
End Sub

That's it! Thank you very very much.
Now i just need to take some time to understand some (not all of the code is clear for me completely) of it and im ready.
Cheers