Write Array to Cell

Question

The code finds the first and last occurrence of a sting in a range then builds a array. My problem is I'm not sure how to write the array to a cell in a comma delimited format to the worksheet. The .Find searches from the beginning of the range forward and the second .Find searches from the end of the range back. Both stop on the first occurrence of the search variable.

Questions: 1. how to improve the code for speed as the this will be searching ranges in the 100,000+ row range 2. how to write the created array into a comma delimited string in a worksheet.

Public Function FindVehicleOptions()

Dim LastRow As Long
Dim vArr As Variant
Dim FindString As String
Dim Rng1 As Range
Dim Rng2 As Range
Dim CellAddress As String
Dim Cell As Range
Dim Search As String
Dim NumRows As Long
Dim NumCols As Long
Dim Key As String
Dim i As Integer
Dim j As Integer
Dim x As Integer
Dim s As String
Dim wb1 As Excel.Workbook: Set wb1 = Application.Workbooks("AFS Configuration Ver 2.xlsm")
Dim ws1 As Worksheet: Set ws1 = Sheets("Configuration")
Dim Destination As Range
Dim sDelimString As String
Dim lCounter As Long

FindString = Sheets("AFS Report").Range("A3")

If Trim(FindString) <> "" Then
    With ws1.Range("B:B")
        Set Rng1 = .Find(What:=FindString, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not Rng1 Is Nothing Then
                Application.Goto Rng1, True
                Debug.Print Rng1.Address
            Else
                Debug.Print "Nothing found"
            End If
    End With
End If

If Trim(FindString) <> "" Then
    With ws1.Range("B:B")
        Set Rng2 = .Find(What:=FindString, After:=.Cells(1), LookIn:=xlValues, LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
            If Not Rng2 Is Nothing Then
                Application.Goto Rng2, True
                Debug.Print Rng2.Address
                    CellAddress = Rng2.Address
                    Set Cell = Range(CellAddress)
            Else
                Debug.Print "Nothing found"
            End If
    End With
End If

vArr = ws1.Range(Rng1.Address & ":" & Rng2.Offset(0, 5).Address).Value

Debug.Print "New value of " & Rng1.Address & Rng2.Offset(0, 5).Address

NumRows = UBound(vArr, 1) - LBound(vArr, 1) + 1
NumCols = UBound(vArr, 2) - LBound(vArr, 2) + 1
Set Destination = Range("B3")
Destination.Resize(UBound(vArr, 2), UBound(vArr, 1)).Value = Application.Transpose(vArr)

End Function

Gary's Student · Accepted Answer · 2014-06-20 00:08:29Z

1

Here is a typical example of placing a two dimensional array into a single cell in csv form:

Sub dural()
Dim vArray(1 To 3, 1 To 5) As Long, K As Long
Dim rDestination As Range, sTringg As String
Set rDestination = Range("B9")

K = 1
For i = 1 To 3
    For j = 1 To 5
        vArray(i, j) = K
        K = K + 1
    Next j
Next i

sTringg = ""
For i = LBound(vArray, 1) To UBound(vArray, 1)
    For j = LBound(vArray, 2) To UBound(vArray, 2)
        sTringg = sTringg & "," & vArray(i, j)
    Next j
Next i
sTringg = Mid(sTringg, 2, Len(sTringg) - 1)

rDestination = sTringg

End Sub

answered Jun 20, 2014 at 0:08

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

3 Comments

RL001 Over a year ago

Just wondering part of what i need to do is also extract every fifth element from the array. I've been using vArr = Application.Index(vArr, 0, 5) wondering if there is a better method. This is new territory for me.

RL001 Over a year ago

How can i sort the array before placing it into the csv format? I'm attempting to follow the code from cpearson.com "QSortInPlace" and I'm getting an error that the array is multi-dimensional. I thought that if it was (1 to 28, 1 to 1) it was a single dimension.

Gary's Student Over a year ago

@User406160 Open a new post.

Collectives™ on Stack Overflow

Write Array to Cell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related