how to store specific cells into an array? vba-excel

Question

I have this:
column A
row1: str1;str2;str3
row2: str4;str5;str6
row3: str7;str8;str9
....................
rown: strn;strn;strn

The code below finds ";" character into the column A:

     Range("A:A").Find(What:=";", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
     xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

I want to put all rows (from column A, containing semicolon character) into an array. I tried to use SET, like this:

       dim r as Variant
       Set r = Range("A:A").Find(What:=rngsearch, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=_  
       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,MatchCase:=False _
       , SearchFormat:=False).Activate

...but doesn't work. It's run-time error '13', type mismatch

I need this array (containing all the cells with semicolon) because I want to extract the strings (from str1 to strn) and separate them in different rows.

Can anyone help me? Maybe someone has another idea how I can do this?

Why don't you use an autofilter with ";" as the criteria, then loop through the visible cells? — Rory
– Rory, Commented Jul 29, 2014 at 9:33
Do you want to add arr(0) = str1, arr(1) = str2, arr(3) = str3, arr(n) = strn like this? — Jagadish Dabbiru
– Jagadish Dabbiru, Commented Jul 29, 2014 at 9:35
I meant using VBA code - I wasn't expecting you to do it by hand. :) — Rory
– Rory, Commented Jul 29, 2014 at 10:33

Dave · Accepted Answer · 2014-07-29 09:35:28Z

0

There are probably more efficient ways to do this, I would personally prefer to avoid referring to an entire column, but this should hopefully do what you are expecting:

Sub test()

Dim ws As Worksheet
Dim rng As Range
Dim cel As Range
Dim strTmp As String
Dim arrFinal As Variant

Set ws = Sheets("Sheet1")
Set rng = ws.Range("A:A")

' Loop through all cells in column A
For Each cel In rng.Cells
    ' Is there a semicolon character in the cell?
    If InStr(1, cel.Value, ";") > 0 Then
        ' Add the cell value to strTmp and add a _
            semicolon at the end to separate this _
            row from the next row
        strTmp = strTmp & cel.Value & ";"
    End If
Next cel

' Split strTmp into an array
arrFinal = Split(strTmp, ";")

End Sub

The end result Is an array called arrFinal of all strings between the semicolon characters

answered Jul 29, 2014 at 9:35

Dave

1,6431 gold badge9 silver badges9 bronze badges

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

2 Comments

ghostlegend Over a year ago

thank you a lot! That is what I need. But if I need the string upto or after the semicolon (I mean put in a new row the string), I have to manipulate the Split line code, right?

Dave Over a year ago

Sorry, I don't know what you mean?

Rory · Accepted Answer · 2014-07-30 09:44:10Z

0

I was referring to something like this:

Sub GetSemicolonData()
    Dim rngCell               As Excel.Range
    Dim asValues()            As String
    Dim lngCount              As Long
    Dim x                     As Long


    With Range("A1").CurrentRegion.Columns(1)
        .AutoFilter field:=1, Criteria1:="*;*"
        lngCount = .SpecialCells(xlCellTypeVisible).Count
        If lngCount > 1 Then
            x = 1
            ' exclude header row
            ReDim asValues(1 To lngCount - 1)
            For Each rngCell In .SpecialCells(xlCellTypeVisible)
                If rngCell.Row > 1 Then
                    ' load value into array
                    asValues(x) = rngCell.Value
                    x = x + 1
                End If
            Next rngCell
        End If
    End With
End Sub

You could also use a variation of Dave's approach that loads all the data into an array and processes that - it should be faster than cell by cell reads.

answered Jul 30, 2014 at 9:44

Rory

34.2k5 gold badges40 silver badges42 bronze badges

1 Comment

ghostlegend Over a year ago

your idea is great too! But yes, Dave's code processes faster. Thank you anyway!

Collectives™ on Stack Overflow

how to store specific cells into an array? vba-excel

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related