Change Array in VBA

Question

I want to swap values into a multidimensional array which are not #N/A or not 0 for each row. So the input table is on a spreadsheet with contains the numbers below, the problem is I can't swap them how I want.

24  20  0   #N/A  #N/A  #N/A 
21  20  0   #NA  #N/A  #N/A
25  24  20   0   #N/A  #N/A
26  25  24  20    0    #N/A
28  26  25  24    20    0

Do you have any suggestion how to deal with that?

Sub FlipRows()

    Dim Rng As Range
    Dim WorkRng As Range
    Dim arr As Variant
    Dim i As Integer, j As Integer, k As Integer
    Dim matrix As Range, matrix2 As Range

    Set matrix = Range("A1:F5")
    Set matrix2 = Range("A7:F11")
    'Set tempMatrix as String, tempMatrix2 as String

    On Error Resume Next

    'matrix.Select
    'matrix.Copy

    'matrix2.Select
    'matrix2.PasteSpecial

    Set WorkRng = Application.Selection
    arr = WorkRng.Formula
    For i = 1 To UBound(arr, 1)
        k = UBound(arr, 2)
        For j = 1 To UBound(arr, 2) / 2
            xTemp = arr(i, j)
            arr(i, j) = arr(i, k)
            arr(i, k) = xTemp
            k = k - 1
        Next
    Next
    WorkRng.Formula = arr

End Sub

Are the #NA and zeros always on the right side, or they can occur in-between good values? — A.S.H
– A.S.H, Commented Feb 25, 2017 at 13:16

A.S.H · Accepted Answer · 2017-02-25 14:30:18Z

1

Assuming you invalid values (0 and #NA) are always on the right of each row, this should do:

    For i = LBound(arr, 1) To UBound(arr, 1)
        'first search backward the first valid entry
        For k = UBound(arr, 2) To LBound(arr, 2) Step -1
            If Not IsError(arr(i, k)) Then If arr(i, k) <> 0 And arr(i, k) <> "#NA" Then Exit For
        Next

        'Now do the swap in the valid region
        For j = LBound(arr, 2) To Int(k / 2)
            Dim temp: temp = arr(i, j)
            arr(i, j) = arr(i, LBound(arr, 2) + k - j)
            arr(i, LBound(arr, 2) + k - j) = temp
        Next
    Next
    WorkRng.Formula = arr

edited Feb 25, 2017 at 14:30

answered Feb 25, 2017 at 13:53

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

3 Comments

ECode Over a year ago

This code also moves the invalid values to the left. I want to keep the invalid values always on the right side. I see that this code keeps only the 0 on the last row on the right side

A.S.H Over a year ago

@Florin that should be because some of your #NA values are actually strings, I thought they were errors. I added a check for that, try the edited code.

A.S.H Over a year ago

But by the way, why are you setting your range from the Selection? You should better set it to an explicit range.

h2so4 · Accepted Answer · 2017-02-25 14:00:03Z

0

Assuming you always have a zero value after the values you want to swap

Sub swap()
Set r = Range("A1:F5")
For Each ro In r.Rows
    Set re = ro.Find(0, lookat:=xlWhole)
    co = re.Column
    For c = 1 To (co - 1) / 2
        a = ro.Cells(1, c)
        ro.Cells(1, c) = ro.Cells(1, co - c)
        ro.Cells(1, co - c) = a
    Next c
Next ro

End Sub

answered Feb 25, 2017 at 14:00

h2so4

1,5771 gold badge10 silver badges11 bronze badges

Collectives™ on Stack Overflow

Change Array in VBA

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related