How to use nested arrays to store a cell's value and its rowcount

[rbysetty]

I have a simple piece of code written which basically scans through column A, detects for a condition and once the condition is met in a row, it copies the cell in column B of the same row into an array. I was hoping someone could help me make a nested array which would not only store the value in column B but also its rowcount. here is what i have so far, any help is appreciated.

Dim col2 As Range
Dim cell2 As Excel.Range
Dim rowcount2 As Integer
Dim ii As Integer

ii = 0
rowcount2 = DataSheet.UsedRange.Rows.Count
Set col2 = DataSheet.Range("A1:A" & rowcount2)
Dim parsedcell() As String
Dim oldarray() As String

For Each cell2 In col2

If cell2.Value <> Empty Then
parsedcell = Split(cell2.Value, "$")
sheetName = parsedcell(0)

If sheetName = DHRSheet.Name Then

Dim oldvalue As Range
ReDim Preserve oldarray(ii)
Set oldvalue = DataSheet.Cells(cell2.Row, 2)

oldarray(ii) = oldvalue.Value

ii = ii + 1
'MsgBox (oldvalue)

End If

End If

Next

 

[Al Chara]

Have you thought about using a mutildimensional array?

 

[p45cal]

Have you thought about using a mutildimensional array?

as in:

Sub blah()
Dim col2 As Range
Dim cell2 As Excel.Range
Dim rowcount2 As [COLOR=#ff0000]long[/COLOR]
Dim ii As [COLOR=#ff0000]long[/COLOR]
ii = 0
rowcount2 = DataSheet.UsedRange.Rows.Count
Set col2 = DataSheet.Range("A1:A" & rowcount2)
Dim oldarray() ' As String
ReDim oldarray([COLOR=#ff0000]0 To 1, 0 To ii[/COLOR])
For Each cell2 In col2.Cells
  If cell2.Value <> Empty Then
    If Split(cell2.Value, "$")(0) = DHRSheet.Name Then
      ReDim Preserve oldarray([COLOR=#ff0000]0 To 1, 0 To ii[/COLOR])
      oldarray([COLOR=#ff0000]0, ii[/COLOR]) = DataSheet.Cells(cell2.Row, 2) 'or = cell2.offset(,1)
      oldarray([COLOR=#ff0000]1, ii[/COLOR]) = cell2.Row
      MsgBox oldarray([COLOR=#ff0000]0, ii[/COLOR]) & vbLf & "on row: " & oldarray([COLOR=#ff0000]1, ii[/COLOR])
      ii = ii + 1
    End If
  End If
Next
'oldarray = Application.Transpose(oldarray) ' may make it easier for future manipulation.
End Sub

but I may have misunderstood

but also its rowcount

You've used the variable name rowcount2 to hold the number of rows in a range. A single cell however only ever has 1 row, so I've used the row number of the cell.

 

[Al Chara]

Are you looking for the number of rows in a range or the row number of a given cell?

 

[rbysetty]

that worked thanks !!