Appending a Value to Each Element of a Range Array

bentom

New Member
Joined
Oct 24, 2019
Messages
4
I have a workbook with 2 sheets: Sheet1 and Sheet2.
In Column A of Sheet1 I have a short uninterrupted list of product IDs:

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl63 {mso-number-format:"\@"; border-top:.5pt solid #A9D08E ; border-right:none; border-bottom:.5pt solid #A9D08E ; border-left:.5pt solid #A9D08E ; background:#E2EFDA; mso-pattern:#E2EFDA none;}.xl64 {mso-number-format:"\@"; border-top:.5pt solid #A9D08E ; border-right:none; border-bottom:.5pt solid #A9D08E ; border-left:.5pt solid #A9D08E ;}--></style>
26109179
20346713
26113399
20306639
26115634

<tbody>
</tbody>

I copy this list to an array with the aim of transforming it and pasting it into Sheet2. There are various things that I need to do, but the first step is to append a 4 digit code, like 6030, to the end of each element value e.g. DirArray(0) becomes 261091796030. However each time, I am get type mismatch errors. If I try to access each element in a for loop I get an out of bounds error. I am beginning to think this is not possible to do. The code below works in so far as to copy the data in Sheet1 to Sheet2 - I just need to transform the array so at the point I paste into Sheet2 they are updated values.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000 ; background-color: #ffffff }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000080 ; background-color: #ffffff }p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff ; min-height: 13.0px}span.s1 {color: #000080 }span.s2 {color: #000000 }</style> Dim StartCell As Range
Dim DirArray As Variant
Dim i As Integer


Sheets("Sheet1").Select
Set StartCell = Range("A1")
StartCell.CurrentRegion.Select


DirArray = StartCell.CurrentRegion.Value

Sheets("Sheet2").Select

'At this point I want to append "6030" to the END of each element value in DirArray

ActiveSheet.Range(Cells(2, 2), Cells(UBound(DirArray) + 1, 2)) = DirArray




Can anyone please help me? Thanks in anticipation.
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
One way
- note that no sheets or cells are selected
- selection slows down the code and can make it more tricky to amend the code
- use variables and always qualify ranges with sheet reference etc


Code:
Sub bentom()
    Const append = "6030"
    Dim A, B() As String
    Dim x As Integer, u As Integer, dest As Worksheet

    Set dest = Sheets("Sheet2")
    A = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    u = UBound(A)
    ReDim B(1 To u, 1)
[COLOR=#006400] 'create array of appended values[/COLOR]
    For x = 1 To u
        B(x, 1) = A(x, 1) & append
    Next x
[COLOR=#006400]'format destination[/COLOR]
    Application.ScreenUpdating = False
    With dest.Columns("B")
        .ColumnWidth = 15
        .NumberFormat = "0"                [COLOR=#006400] 'prevents scientific notation[/COLOR]
    End With
[COLOR=#006400]'write values to sheet[/COLOR]
    For x = 1 To u
        Sheets("Sheet2").Cells(x + 1, 2) = B(x, 1)
    Next x
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
another way ...

Code:
Sub bentom2()
    Const append = "6030"
    Dim A, x As Integer, u As Integer

    A = Sheets("Sheet1").Range("A1").CurrentRegion.Value
    u = UBound(A)
    ReDim B(1 To u, 1)
[COLOR=#006400] 'create array of appended values[/COLOR]
    For x = 1 To u
        A(x, 1) = A(x, 1) & append
    Next x
[COLOR=#006400]'format destination and write values to sheet[/COLOR]
    Application.ScreenUpdating = False
    With Sheets("Sheet2")
        .Columns("B").ColumnWidth = 15
        .Columns("B").NumberFormat = "0"                 [COLOR=#006400]'prevents scientific notation[/COLOR]
        .Cells(2, 2).Resize(u).Value = A
    End With
End Sub

and some bedtime reading for you
https://excelmacromastery.com/excel-vba-array/
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,873
Office Version
  1. 2010
Platform
  1. Windows
And yet another way...
Code:
Sub Append6030()
  Dim Arr As Variant
  Arr = Application.Transpose(Split(Replace(Join(Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion), vbLf), vbLf, 6030 & vbLf) & 6030, vbLf))
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr)).NumberFormat = "@"
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr)) = Arr
End Sub
 
Last edited:

bentom

New Member
Joined
Oct 24, 2019
Messages
4

ADVERTISEMENT

Thank you very much Yongle. I clearly need that bedtime reading.

Thank you again.
 

bentom

New Member
Joined
Oct 24, 2019
Messages
4
Wow - that's neat. Will compare with all the solutions to see which is fastest as there will be a fair amount of data to crunch. Thank you for this elegant response.
 

bentom

New Member
Joined
Oct 24, 2019
Messages
4
And yet another way...
Code:
Sub Append6030()
  Dim Arr As Variant
  Arr = Application.Transpose(Split(Replace(Join(Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion), vbLf), vbLf, 6030 & vbLf) & 6030, vbLf))
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr)).NumberFormat = "@"
  Sheets("Sheet2").Range("A1").Resize(UBound(Arr)) = Arr
End Sub

one thing though - i was having major problems with application.transpose as it was only repeating the first element in the array. I'm not sure what you've done to overcome this bug in Excel.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Another one to try.

Rich (BB code):
Sub Append_6030()
  With Sheets("Sheet2").Range("B2").Resize(Sheets("Sheet1").Range("A1").CurrentRegion.Rows.Count)
    .NumberFormat = "0"
    .Value = Evaluate("Sheet1!" & .Offset(-1, -1).Address & "&""6030""")
  End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,540
Messages
5,625,408
Members
416,100
Latest member
lirongr1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top