Is this the proper way to write a two dim array?

[Tim Hanson]

The code checks to see if the cells in column F is numeric if so then copies the value to the adjacent cell in column G and clears the original cell

I have been reading on how to write two dim arrays, the code works but I cannot tell if I am fully utilizing the two dimensional benefits

I am hoping someone might give me insights where or if I have gone wrong? or if it is not exactly wrong how might it be improved

Thank you

'Using a 2 dim array
Sub MoveNumbers3()
    Dim ws As Worksheet
    Dim myArray As Variant
    Dim lastrow As Long
    Dim x As Integer
    Dim Y As Integer
  
            Set ws = ThisWorkbook.Sheets("XXX")
         
            With ws
                lastrow = .Range("F" & .Rows.Count).End(xlUp).Row
                myArray = .Range("F2:F" & lastrow).Value
                myArray = .Range("F2:F" & lastrow).Value
            End With
             
            For x = LBound(myArray) To UBound(myArray)
                For Y = LBound(myArray, 2) To UBound(myArray, 2)
                    If IsNumeric(myArray(x, Y)) Then
                      Cells(x, Y).Offset(1, 6).Value = myArray(x, Y)
                      Cells(x, Y).Offset(1, 5).Value = " "
                    End If
                Next Y
            Next x

End Sub

 

[NARAYANK991]

Hi Tim ,

When you assign a range which is just one column or one row to a variant , the resulting array has 2 dimensions , but that is only as far as addressing it using indices goes. In reality , the array has just one dimension , which reflects the range you assigned to it.

To get a real two dimension array , you need to assign a multiple rows , multiple columns range to an array.

Thus if you assign a range such as A1:B2 to a variant , you will get a two dimension array , with elements such as :

arr(1,1) , arr(1,2) , arr(2,1) , arr(2,2)

Narayan

 

[Tim Hanson]

Hello Narayan, that was helpful, so if I understand properly there is no benefit to writing a two dimensional array for a single column or row as it really can only be a one dim array.

So would this be the proper one dim array

Inparticular I wonder if this is the best way to write this

Cells(i + 1, 7).Value = myArray(i, 1)
  Cells(i + 1, 6) = " "

Also what is the difference with MoveNumbers1 and MoveNumbers2 way of writing the macro?

I always think of arrays as faster so on a large data set one uses arrays?

Thank you again

'Loop over a 1 dim array
Sub MoveNumbers1()
Dim myArray As Variant
Dim ws As Worksheet
Dim LR As Long
Dim i As Long

Set ws = ThisWorkbook.Sheets("XXX")
LR = ws.Range("F" & ws.Rows.Count).End(xlUp).Row

myArray = ws.Range("F2:F" & LR).Value

For i = LBound(myArray, 1) To UBound(myArray, 1)
If IsNumeric(myArray(i, 1)) Then
   Cells(i + 1, 7).Value = myArray(i, 1)
   Cells(i + 1, 6) = " "
End If
Next i

End Sub

Sub MoveNumbers2()
Dim ws As Excel.Worksheet
Dim ColLetter As String
Dim ColNumber As Integer
Dim lRow As Long
Dim i As Long

ColLetter = "F"
Set ws = ThisWorkbook.Sheets("XXX")
    lRow = ws.Range(ColLetter & ws.Rows.Count).End(xlUp).Row
  'Get Column Number from column letter
    ColNumber = ws.Range(ColLetter & "1").Column

    For i = 1 To lRow
        If IsNumeric(Cells(i, ColNumber).Value) Then
          Cells(i, (ColNumber + 1)).Value = Cells(i, ColNumber).Value
          Cells(i, ColNumber).Value = " "
        End If
    Next i
End Sub

 

[NARAYANK991]

Hi Tim ,

Your first procedure is fine ; let me go through your second.

An alternative way of writing the first one can be :

Sub MoveNumbers1()
    Dim numArray As Variant, textArray As Variant
    Dim ws As Worksheet
    Dim LR As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    LR = ws.Range("F" & ws.Rows.Count).End(xlUp).Row

    numArray = ws.Range("F2:F" & LR).Value
    textArray = numArray
  
    For i = LBound(numArray, 1) To UBound(numArray, 1)
        If IsNumeric(numArray(i, 1)) Then
           textArray(i, 1) = ""
        Else
           numArray(i, 1) = ""
        End If
    Next i

    ws.Range("F2:F" & LR).Value = textArray
    ws.Range("G2:G" & LR).Value = numArray
End Sub

Narayan

 

[Tim Hanson]

Narayan, that was very helpful to see, thank you!!

 

[NARAYANK991]

Hi Tim ,

See the following code ; what it does is separate out the text items and numeric items from a range which has a mix of both , and put the numeric items in one column , and the text items in the next column.

Sub SeparateNumbersAndText()
    Dim inputArray As Variant, numArray As Variant, textArray As Variant
    Dim ws As Worksheet
    Dim LR As Long, i As Long, j As Long, k As Long, l As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    LR = ws.Range("H" & ws.Rows.Count).End(xlUp).Row

    inputArray = ws.Range("H2:L" & LR).Value
   
    number_of_text_items = Evaluate("=SUMPRODUCT(--ISTEXT(H2:L" & LR & "))")
    number_of_numeric_items = Evaluate("=SUMPRODUCT(--ISNUMBER(H2:L" & LR & "))")
   
    numArray = ws.Range("H2:H" & number_of_numeric_items + 1).Value
    textArray = ws.Range("H2:H" & number_of_text_items + 1).Value
  
    k = 1
    l = 1
  
    For i = LBound(inputArray, 1) To UBound(inputArray, 1)
        For j = LBound(inputArray, 2) To UBound(inputArray, 2)
            If IsNumeric(inputArray(i, j)) Then
               numArray(k, 1) = inputArray(i, j)
               k = k + 1
            Else
               textArray(l, 1) = inputArray(i, j)
               l = l + 1
            End If
        Next j
    Next i

    ws.Range("F2:F" & k).Value = numArray
    ws.Range("G2:G" & l).Value = textArray
End Sub

Narayan

 

[Tim Hanson]

Thank you for this example of a real two dim array I will have to study it as there are things going on i have not seen before.

I greatly appreciate the help