Jump to content
Welcome Guest!

Join us now to get access to all our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, and so, so much more. It's also quick and totally free, so what are you waiting for?

  • 0
Sign in to follow this  
ZiyaSepp

Create Address Label using User Data

Question

ZiyaSepp    2

I have a spreadsheet that contain some names and addresses for clients.

The Data is on a sheet called "2017" and is layed out like this:

b6udn5.png

 

The first entry is on Row 2 and ends Row 50 (but that can change). Can someone help me write a macro so that it can Create and address for each line like this (skipping blank cells):

Miss Z Sepp

256 Hollywood Ave

Fairfied

New Jersey

NJ 00074

 

And have each pasted on a sheet called "Labels". This is to enable printed labels to be created with addresses already on them. I have uploaded the Lables sheets but not the DATA as it has sensitve information on them. If you have any follow up questions please let me know

 

Thanks

Z

Labels.xlsx

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0
Caleeco    8

Hi Ziya, 

 

I should be able to help out with that. With the data and example you have provided. I assume you do not want the first name (Column H) to be included? Also, in order to determine the last row used, I will need a column that will always have data. Could you let me know which column will always be filled in? eg Surname?

 

Thanks

Caleeco

Share this post


Link to post
Share on other sites
  • 0
ZiyaSepp    2

Hey Caleeco, 

 

Yes if you could please omit the first name that would be good. Surname should always be filled out so that can be used to find the end of the data. I will probably add a Unique ID for each entry at a later date, but we use last name for now. 

 

Kind Regards

Z

Share this post


Link to post
Share on other sites
  • 0
Caleeco    8

Thanks for the additional information. I have put some code together for you to test!

Note: The Option Base 1 statement must appear at the top of your code

Code:

Option Explicit
Option Base 1

Sub CreateLabels()
'Source: www.ExcelWTF.com
'Purpose: To create address labels using data from excel sheet

 

'----- Variables
Dim shYear As Worksheet, shLabel As Worksheet
Dim lr As Long, i As Long, j As Long, k As Long
Dim Label() As String
Dim r As Range, RNG As Range

 

'----- Code
i = 1
Set shYear = Sheets("2017")                         'Change the line below next year if you create a new sheet
lr = shYear.Range("I" & Rows.Count).End(xlUp).Row   'Find the last used row
ReDim Label(lr - 1)                                 'Set the size of the array
Set RNG = shYear.Range("F2:F" & lr)                 'Set the data range to loop through
Application.ScreenUpdating = False
On Error Resume Next
For Each r In RNG 'Collate information in the array
    If Not IsEmpty(r.Value) Then Label(i) = Label(i) & r.Value & " " 'Title
    If Not IsEmpty(r.Offset(0, 1).Value) Then Label(i) = Label(i) & r.Offset(0, 1).Value & " " 'Initial
    If Not IsEmpty(r.Offset(0, 3).Value) Then Label(i) = Label(i) & r.Offset(0, 3).Value & vbCrLf 'Surname
    If Not IsEmpty(r.Offset(0, 4).Value) Then Label(i) = Label(i) & r.Offset(0, 4).Value & vbCrLf 'Add 1
    If Not IsEmpty(r.Offset(0, 5).Value) Then Label(i) = Label(i) & r.Offset(0, 5).Value & vbCrLf 'Add 2
    If Not IsEmpty(r.Offset(0, 6).Value) Then Label(i) = Label(i) & r.Offset(0, 6).Value & vbCrLf 'Add 3
    If Not IsEmpty(r.Offset(0, 7).Value) Then Label(i) = Label(i) & r.Offset(0, 7).Value & vbCrLf 'Add 4
    If Not IsEmpty(r.Offset(0, 8).Value) Then Label(i) = Label(i) & r.Offset(0, 8).Value & vbCrLf 'Add 5
    If Not IsEmpty(r.Offset(0, 9).Value) Then Label(i) = Label(i) & r.Offset(0, 9).Value & vbCrLf 'Add 6
    If Not IsEmpty(r.Offset(0, 10).Value) Then Label(i) = Label(i) & r.Offset(0, 10).Value 'PostCode
    'Debug.Print Label(i)
    i = i + 1
Next r

'Print the data to the labels sheet
Set shLabel = Sheets("Labels")
With shLabel.Columns("A:C")
    .ClearContents
    .VerticalAlignment = xlVAlignCenter
End With

j = 1
k = 1
For i = LBound(Label) To UBound(Label)
    shLabel.Cells(j, k).Value = Label(i)
    If k = 1 Then
        k = 3
    ElseIf k = 3 Then
        k = 1
        j = j + 1
    End If
Next

Application.ScreenUpdating = True

End Sub

 

Let me know how you get on

Thanks

 

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
ZiyaSepp    2

Thanks Caleeco! I sort of followed what you did, but I'm not good enough at VBA to understand all of it!

 

Not sure how 1 string variable Label gets used to make separate address entries O.o

 

I like how it fills up the page left to right, and then top to bottom :D However, the cells are left aligned.. is there a way to have them more centralised? As I will be printing these on sticky paper, dont want the addresses right on the edge. 

 

Z

Share this post


Link to post
Share on other sites
  • 0
Caleeco    8

Hi Ziya, 

 

No problem, I'm happy to help! With regard to the alignment , each cell has an INDENT property we can make use of. So towards the bottom of the code please edit this WITH statement:

Code:

With shLabel.Columns("A:C")
    .ClearContents
    .VerticalAlignment = xlVAlignCenter
    .IndentLevel = 5
End With

Change the INDENT LEVEL to suit your requirement. 

 

Aha, you're half right there. The variable Label is a string, but it is an ARRAY of strings. As declared at the start, the parenthesis are the clue to knowing its an ARRAY rather than a variable. 

Code:

Dim Label() As String

 

You can think of an array like a table of data, the size of the table can be whatever you like. In this instance, i changed the size of the array to match how many rows of data we had (adjusting -1 to account for the fact the data starts in row 2).

Code:

lr = shYear.Range("I" & Rows.Count).End(xlUp).Row   'Find the last used row
ReDim Label(lr - 1)                                 'Set the size of the array

 

Each row of an ARRAY is called an ELEMENT. So i created a loop, to fill each ELEMENT with 1 address from your 2017 sheet :) If you would like a more in depth explanation I would be happy to talk you through it. However, I will be starting a blog on the website very soon to teach people this sort of thing ;)

 

Caleeco

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
ZiyaSepp    2

Hi Caleeco, 

 

So sorry for not getting back to you sooner, work-life has been busy! Ahhh ok, I'm clueless when it comes to arrays... I'm still learning the basic macro stuff :P YES!!! Some tutorials would be great!

 

Thanks for breaking down the problem, at least I now know how to spot arrays ;)

 

Z

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×