Recurring String Generator

[dlo1503]

Hello

I am currently writing a code to generate labels from a spreadsheet of data. At the minute the various strings are made up from cells in Row 2 on Sheet1. I would like this to continue down until the last row of text in Column B. The output looks like I want it to however I would like it to generate output for each row in column B on Sheet 1 to each row in Column A on Sheet2.

Sub Concatenate2()

Dim String1 As String
Dim String2 As String
Dim String3 As String
Dim String4 As String
Dim String5 As String
Dim String 6 As String

Dim full_string As String

String1 = Sheet1.Cells(2, 20).Value
String2 = Sheet1.Cells(2, 2).Value
String3 = Sheet1.Cells(2, 4).Value
String4 = Sheet1.Cells(2, 6).Value
String5 = Sheet1.Cells(2, 5).Value
String6 = Sheet1.Cells(2, 15).Value

Sheet2.Cells(1, 1).Value = String1 & vbNewLine & vbNewLine & String2 & vbNewLine & "QTY- " & String3 & vbNewLine & String4 & "mm " & String5 & vbNewLine & String6

End Sub


Any help greatly appreciated

thanks
Daniel

 

[Fluff]

How about

Sub dlo()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   With Sheets("Sheet1")
      Ary = .Range("B2:T" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r, 1) = Ary(r, 1) & vbLf & vbLf & Ary(r, 1) & vbLf & "QTY- " & Ary(r, 3) & vbLf & Ary(r, 5) & "mm " & Ary(r, 4) & vbLf & Ary(r, 14)
   Next r
   Sheets("Sheet2").Range("A2").Resize(r - 1).Value = Nary
End Sub

 

[dlo1503]

Hi Fluff

thank you for your speedy response. Appreciate that so much. My only issue with this is String1 was to remain an constant throughout. I should have mentioned that. It represents an order number that will apply to all of the labels.

Can you help me with an amendment please?

Thanks
Daniel

 

[Fluff]

So each line should start with the value in T2 & all other strings are from the individual rows?

 

[dlo1503]

Yes that's correct. T2 should appear at the top of each and then every row from row 2 onwards

Thanks
Daniel

 

[Fluff]

Ok, how about

Sub dlo()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long
   
   With Sheets("Sheet1")
      Ary = .Range("B2:T" & .Range("B" & Rows.Count).End(xlUp).Row).Value2
   End With
   ReDim Nary(1 To UBound(Ary), 1 To 1)
   For r = 1 To UBound(Ary)
      Nary(r, 1) = Ary(1, 19) & vbLf & vbLf & Ary(r, 1) & vbLf & "QTY- " & Ary(r, 3) & vbLf & Ary(r, 5) & "mm " & Ary(r, 4) & vbLf & Ary(r, 14)
   Next r
   Sheets("Sheet2").Range("A2").Resize(r - 1).Value = Nary
End Sub

 

[dlo1503]

That is perfect Fluff. Works a charm.

You're a star!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dlo1503]

Fluff, can I pester you with one more thing as you are an Excel Wizard. This code is basically to create a label without having to involve Word. I need to include a QR code in with the text that is a representation of the output the code has generated. Any ideas about this? I've been trawling forums for hours trying to find a solution but can't get anywhere?

Much appreciated
Daniel

 

[Fluff]

Absolutely no idea, I know nothing about QR codes.