Adjust Values As They Are Being Stored In VBA Array

Question

I am trying to write Column A to an array and while passing into the array or when writing the array to the sheet, I would like to multiple each value stored by a set number (specifically .01). I will be writing the array back over the same column it was set from.

Ex. Sheet before macro:

Col A     Col B     Col C
Header    Header    Header
100
50
50
40
100

Sheet after macro:

Col A     Col B     Col C
Header    Header    Header
1
.5
.5
.4
1

So far I have been working off a basic array portion of code from a tutorial I saw online shown below:

Sub ArrayTest
Dim Arr() As Variant
Arr = Range("A1:A6")
Dim R As Long
Dim C As Long
  For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
  For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
   Debug.Print Arr(R, C)
Next C
Next R

'resize range array will be written to
Dim Destination As Range
  Set Destination = Range("K1")
Destination.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr

'transpose / write array to range
Set Destination = Range("A1")
Destination.Resize(UBound(Arr, 2), UBound(Arr, 1)).Value = Application.Transpose(Arr)

End Sub

This code has no errors, but I'm unsure of where / how I can "manipulate" the values (either on the way into the array or on the way back to the sheet).

An array may not even be the best way to achieve this overall goal of overwriting a columns values with itself multiplied by a another number. I know I could write the column to a dummy sheet, do the calculation then move back over the original sheet and column, but I was trying to find something cleaner and potentially faster than that. This is also a simplified example, my actual data set is much larger and more variable, but for the ease of discussion I created this example.

Any advice is much appreciated!

Tim Williams · Accepted Answer · 2018-08-28 20:53:21Z

3

Here's a "no loop" approach:

Sub Tester()

    Dim arr, rngSrc As Range, sht As Worksheet

    Set sht = ActiveSheet

    Set rngSrc = sht.Range("A2:A6")

    arr = rngSrc.Parent.Evaluate(rngSrc.Address() & " * 10") '<< returns an array

    sht.Range("B2").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr

End Sub

For your specific case:

With Range("A2:A6")
    .Value = .Parent.Evaluate(.Address & " * 0.01")
End With

edited Aug 28, 2018 at 20:53

answered Aug 28, 2018 at 20:46

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RugsKid Over a year ago

thanks - your "B2" here:sht.Range("B2").Resize I had to change to "A2" to fit with the example. But this works well and is much cleaner. Cheers!

Tim Williams Over a year ago

That was just an example - added a second snippet to address your specific question.

Cyril Over a year ago

Tim, as I'm not very familiar with .Parent (I thought it was part of Chart Objects), is there a particular reason to use it in the array?

Tim Williams Over a year ago

The Parent of a Range Object is the containing worksheet: it's important when using Evaluate to use the correct context for the formula being evaluated, otherwise it will default to the Activesheet. That's not important here (since we're working on the ActiveSheet anyway) but it's good practice never to rely on defaults and instead always be specific. Note you can use .Worksheet instead of .Parent - it means the same thing in this case.

Cyril Over a year ago

@TimWilliams thanks for the rundown! i think i may have a few specific uses for that offhand...

SJR · Accepted Answer · 2018-08-28 20:40:44Z

3

You can do it like this, but easier to use pastespecial (multiply or divide).

Sub x()

Dim v As Variant, i As Long

v = Range("A2:A6").Value

For i = LBound(v) To UBound(v)
    v(i, 1) = v(i, 1) * 0.01
    Debug.Print v(i, 1)
Next i

Range("A2:A6").Value = v

End Sub

answered Aug 28, 2018 at 20:40

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Comments

Cyril · Accepted Answer · 2018-08-28 20:50:35Z

1

Was working on this as I saw Tim post... similar use of evaluate, but doesn't need an additional array or loop:

Dim rng As Range, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 1), Cells(lr, 1))
rng = Evaluate(rng.Address & "*0.01")

answered Aug 28, 2018 at 20:50

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Comments

user4039065 · Accepted Answer · 2018-08-28 21:10:44Z

1

Perhaps you should collect the values first and process the adjustment(s) in memory.

dim i as long, arr as variant

with worksheets("sheet1")

    arr = .range(.cells(2, "A"), .cells(.rows.count, "A").end(xlup)).value2

    for i=lbound(arr, 1) to ubound(arr, 1)
        arr(i, 1) = arr(i, 1)/100
    next i

    for i=lbound(arr, 1) to ubound(arr, 1)
        debug.print arr(i, 1)
    next i

    .cells(1, "K").resize(ubound(arr, 1), ubound(arr, 2)) = arr
    .cells(1, "L").resize(ubound(arr, 2), ubound(arr, 1)) = application.transpose(arr)

end with

answered Aug 28, 2018 at 21:10

user4039065

Collectives™ on Stack Overflow

Adjust Values As They Are Being Stored In VBA Array

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related