Populating a worksheet from an array will not work

andrewmrichards

New Member
Joined
Aug 28, 2013
Messages
20
Hi all

I am wanting to write VBA code which will do the following:
  1. Take values from a single worksheet column, and read them into an array
  2. Convert the array into a string
  3. Manipulate the string using RegEx
  4. Convert the string back to an array
  5. Write the array back to the worksheet column
For the purposes of testing my data is as follows
Data cleansing tools.xlsm
A
1Any instance of
2Cl;Cl.
3La;La.
4St;St.
5Rd;Rd.
6Dr;Dr.;Drv;Drv.
7sample
8Sample
9Doctor;Dr.
10Mr.
11Mrs.
12Ms.
13Rev.;Revd.;Revd
Consistency rules


For step 1, I am using the following:
VBA Code:
Dim MyArr() As Variant
MyArr = Sheet5.Range("A1:A13")

This works fine.
For step 2, given that MyArr is now a 2-dimensional array (A1 is now in MyArr(1,1), A2 is in MyArr(2,1) and so on), I'm using Nigel Heffernan's "Join2D" function, which I paste below for reference, or can be found at Excellerando.Blogspot.com This also works fine.

For step 3, I manipulate the string as required. No problem here.

For step 4, I then use the following function as a simple way to get the string back into an array of the right shape, given that I will only ever want to read from / write to a single column:

VBA Code:
End Sub
Function MySplit2d(InputVar As String, SplitChar As String) As Variant()

      Dim MyArr() As String
      Dim OutputArr() As Variant
      Dim l As Long

10    MyArr = Split(InputVar, SplitChar)

20    ReDim OutputArr(UBound(MyArr) + 1, 1)
30    For l = 1 To UBound(MyArr) + 1
40        OutputArr(l, 1) = MyArr(l - 1)
50    Next l

60    MySplit2d = OutputArr

End Function

This appears to return the array with exactly the right shape and contents.

My testing code is as follows:

VBA Code:
Sub TestMy2dSplit()

      Dim MyArr() As Variant, MyNewArr() As Variant
      Dim MyString As String

      Dim i As Integer

10    MyArr = Sheet5.Range("A1:A13")

20    MyString = Join2d(MyArr, Chr(187))
30    MyNewArr = MySplit2d(MyString, Chr(187))

40    For i = LBound(MyArr) To UBound(MyArr)
50        Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1)
60    Next i

70    Sheet5.Range("L1", Sheet5.Range("L1").Cells(UBound(MyNewArr))).Value = MyNewArr()


End Sub

If I change line 70 above to end ... =MyArr() then the range starting in L1 populates with the original data, as expected.
If I look at each element in the MyNewArr() array, it seems to me to be identical to MyArr(). I have tested this with the following loop:

VBA Code:
For i = LBound(MyArr) To UBound(MyArr)
    Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1)
Next i

But when I run this code, the range starting in L1 remains completely empty. No errors, no problems, just nothing seems to happen.

Does anyone have any idea of what I'm missing?

Below is the Join2D function as mentioned above.

Many thanks
Andrew

---

VBA Code:
Public Function Join2d(ByRef InputArray As Variant, _
                       Optional RowDelimiter As String = vbCr, _
                       Optional FieldDelimiter = vbTab, _
                       Optional SkipBlankRows As Boolean = False _
                       ) As String

' Join up a 2-dimensional array into a string. Works like the standard
'  VBA.Strings.Join, for a 2-dimensional array.
' Note that the default delimiters are those inserted into the string
'  returned by ADODB.Recordset.GetString

On Error Resume Next

' Coding note: we're not doing any string-handling in VBA.Strings -
' allocating, deallocating and (especially!) concatenating are SLOW.
' We're using the VBA Join & Split functions ONLY. The VBA Join,
' Split, & Replace functions are linked directly to fast (by VBA
' standards) functions in the native Windows code. Feel free to
' optimise further by declaring and using the Kernel string functions
' if you want to.

' ** THIS CODE IS IN THE PUBLIC DOMAIN **
'   Nigel Heffernan   Excellerando.Blogspot.com

Dim i As Long
Dim j As Long

Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long

Dim arrTemp1() As String
Dim arrTemp2() As String

Dim strBlankRow As String

i_lBound = LBound(InputArray, 1)
i_uBound = UBound(InputArray, 1)

j_lBound = LBound(InputArray, 2)
j_uBound = UBound(InputArray, 2)

ReDim arrTemp1(i_lBound To i_uBound)
ReDim arrTemp2(j_lBound To j_uBound)

For i = i_lBound To i_uBound

    For j = j_lBound To j_uBound
        arrTemp2(j) = InputArray(i, j)
    Next j

    arrTemp1(i) = Join(arrTemp2, FieldDelimiter)

Next i

If SkipBlankRows Then

    If Len(FieldDelimiter) = 1 Then
        strBlankRow = String(j_uBound - j_lBound, FieldDelimiter)
    Else
        For j = j_lBound To j_uBound
            strBlankRow = strBlankRow & FieldDelimiter
        Next j
    End If

    Join2d = Replace(Join(arrTemp1, RowDelimiter), strBlankRow, RowDelimiter, "")
    i = Len(strBlankRow & RowDelimiter)

    If Left(Join2d, i) = strBlankRow & RowDelimiter Then
        Mid$(Join2d, 1, i) = ""
    End If

Else

    Join2d = Join(arrTemp1, RowDelimiter)

End If

Erase arrTemp1

End Function
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
MyNewArr is a 2d array 0 to 13, 0 to 1, and on this line50 Debug.Print "MyArr(" & i & ") = " & MyArr(i, 1), , "MyNewArr(" & i & ") = " & MyNewArr(i, 1) you are printing the 2nd column of that array, but when you output it to the sheet you are outputting the 1st column. if you use
VBA Code:
70    Sheet5.Range("L1", Sheet5.Range("L1").Cells(UBound(MyNewArr))).Resize(, 2).Value = MyNewArr()
you will see that you get the contents of the array in col M
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,320
Members
425,273
Latest member
tonio909

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