VBA:Adding Text In Front Label Address

Susanto3311
3StarLounger
Posts: 210
Joined: 17 Feb 2022, 05:16

VBA:Adding Text In Front Label Address

Post by Susanto3311 »

hi all..
how to modified this code
the code work fine to make label address with easy but i want to adding text in front label like this
"To. Mr/Mrs."

Code: Select all

Sub labels()
 Dim i As Long, j As Long, myrow As Long, mycol As Long
 Application.ScreenUpdating = False
  For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
   mycol = (i - 2) \ 14
   myrow = 5 * (i - 2) - 14 * 5 * mycol + 2
   For j = 0 To 3
    Cells(myrow + j, mycol + 6) = Cells(i, j + 1)
   Next j
  Cells(myrow, mycol + 6).Resize(4, 1).BorderAround ColorIndex:=1, Weight:=xlMedium
  Next i
 Application.ScreenUpdating = True
End Sub
after running code the result this example
John Mc Donald
Street #43
Michigan
i want to after modified
To. Mr John Mc Donald
Street #43
Michigan

anyone help, thank in advance
susant

User avatar
HansV
Administrator
Posts: 75213
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA:Adding Text In Front Label Address

Post by HansV »

It's not clear what the code has to do with your question.
Regards,
Hans

Susanto3311
3StarLounger
Posts: 210
Joined: 17 Feb 2022, 05:16

Re: VBA:Adding Text In Front Label Address

Post by Susanto3311 »

hi hans..
i hope this picture make more clear
https://ibb.co/FXcnTCT

User avatar
HansV
Administrator
Posts: 75213
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: VBA:Adding Text In Front Label Address

Post by HansV »

Code: Select all

Sub labels()
 Dim i As Long, j As Long, myrow As Long, mycol As Long
 Application.ScreenUpdating = False
 For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
  mycol = (i - 2) \ 14
  myrow = 5 * (i - 2) - 14 * 5 * mycol + 2
  For j = 0 To 3
   If j = 1 Then
     Cells(myrow + j, mycol + 6) = "To Mr./Ms. " & Cells(i, j + 1)
   Else
     Cells(myrow + j, mycol + 6) = Cells(i, j + 1)
   End If
  Next j
  Cells(myrow, mycol + 6).Resize(4, 1).BorderAround ColorIndex:=1, Weight:=xlMedium
 Next i
 Application.ScreenUpdating = True
End Sub
Regards,
Hans

User avatar
SpeakEasy
3StarLounger
Posts: 286
Joined: 27 Jun 2021, 10:46

Re: VBA:Adding Text In Front Label Address

Post by SpeakEasy »

One way is to replace

Cells(myrow + j, mycol + 6) = Cells(i, j + 1)

with

Cells(myrow + j, mycol + 6) = IIf(j = 1, "To. Mr " & Cells(i, j + 1), Cells(i, j + 1))

I note you originally suggest you may also want To. Mrs ... but I see no way from your example of determining the necessary sex.

Susanto3311
3StarLounger
Posts: 210
Joined: 17 Feb 2022, 05:16

Re: VBA:Adding Text In Front Label Address

Post by Susanto3311 »

hi guys...thank you so much!!! working great.