CODEWITHSUNDEEP
vbaexcel
Travis
Travis

Reputation: 360

Excel VBA find all values in row and save different column values to variables

I've done quite a bit of searching and can't find any code that matches my situation or to a point I can modify except for one.

Looking at the spreadsheet below. I want to have the user enter the OrderNumber then search Column A for every value of that number. As it does I want it to copy the ItemNumber and QtyOrdered to two different variables in order to put them into textboxes later on.

I want it to "stack" the information into the variable so something like ItemNumValues = ItemNumValues + Cell.Value

Spreadsheet

I tried to modify code from someone else ("their code") but I am getting a mismatch type error. The rest of the code works. There are some trace elements in the script from previous features that aren't used and I just haven't removed them yet.

'***********************************************************
'********** Their Code Follows *****************
'***********************************************************

    Dim numentries As Integer
    Dim i As Integer
    '***********************************************************

    'Get number of entries
    numentries = Worksheets(Sheet1).UsedRange.Rows.Count
    '*************************************************************

    'Run loop to cycle through all entries (rows) to copy
    For i = 1 To numentries

         If (Worksheets("Sheet1").Cells(i + 2, 1).Value = InStr(1, Cell, OrderNumber, vbTextCompare)) Then
              MsgBox Test
        End If

    Next i

End If   

'***********************************************************
'********** End Their Code *****************
'***********************************************************

Upvotes: 0

Views: 4437

Answers (2)

A.S.H
A.S.H

Reputation: 29352

It seems that the OrderNumber (column A) is sorted. Very good news (if they're not, just sort them ;) ). This simple function will get you the ItemNumbers and QtyOrdered into a bi-dimensional array, where each row is a pair of them.

Function ArrItemQty(ByVal OrderNumber As Long)
With Worksheets("Sheet1").UsedRange.Offset(1)
        .AutoFilter 1, OrderNumber
        ArrItemQty= .Resize(, 2).Offset(, 1).SpecialCells(xlCellTypeVisible).value
        .Parent.AutoFilterMode = False
    End With
End Function

And here's a little testing:

Sub Test()
    Dim i As Long, j As Long, ar
    ar = ArrItemQty(636779)
    For i = LBound(ar, 1) To UBound(ar, 1)
         Debug.Print
         For j = LBound(ar, 2) To UBound(ar, 2): Debug.Print ar(i, j),: Next
     Next
End Sub

p.s. be aware that the resulting array is 1-based. Use LBound and UBound as indicated is safest.

Upvotes: 1

Craig Gross
Craig Gross

Reputation: 96

I recommend using a multidimensional array. If you've never used arrays before, I strongly suggest reading up on them. 

Sub GatherData()
Dim c As Range
Dim aGetData() As Variant 'This is our array
Dim i As Integer
Dim a As Integer
Dim iRowCount As Integer
Dim sRange As String

'Gather data
iRowCount = Worksheets("Sheet1").UsedRange.Rows.Count
For Each c In Range("A2:A" & iRowCount)
  If c.Value = 636779 Then
    ReDim Preserve aGetData(2, i) 'An array must have a set size but as we
    'do not know how many order numbers will be found we have to 'resize'
    'the array to account for how many we do find. Using "ReDim Preserve" 
    'keeps any data we have placed into the array while at the same time
    'changing it's size.
    For a = 0 To 2 'Our first index will hold each col of data that is why
                   'it is set to 2 (arrays start at a base of zero, so
                   '0,1,2 will be each col(A,B,C)
      aGetData(a, i) = c.Offset(0, a) 'This gets each value from col A,B and C
    Next a
    i = i + 1 'Increment for array in case we find another order number
              'Our second index "aGetData(index1,index2) is being resized 
              'this represents each order number found on the sheet
  End If
Next c

'How to read the array
For i = 0 To UBound(aGetData())
  For a = 0 To 2
    Debug.Print aGetData(a, i)
  Next a
Next i
End Sub

Upvotes: 1

Related Questions