VBA Code required for array type lookup

[kazbarina]

Hi,

I have a list of store numbers in column B and in columns D to H I have a quantities - the headers of these columns are 4, 3, 2, 1.

I then have another list of locations against which is a number - 1, 2, 3, 4. (header of the column is Size)

I am trying to go down the store list by store, look across to the locations, and if the location is a (size) 3, go back to the store list and read across to the quantity in the column headed 3. If that quantity is 5, put the store number in column A in the location and subsequent 4 locations.

Example below.

Excel Workbook
	B	D	E	F	G	H	J	K	M
3	*	No. of locations require if size is:				*	*	*	*
4	Store Nbr	4	3	2	1	*	Location	Size	Allocate
5	52382	1	2	3	4	*	1	3	52382
6	52395	1	2	3	4	*	3	3	52382
7	52967	1	2	3	4	*	5	3	52395
8	52996	1	2	3	4	*	7	3	52395
9	92135	1	2	3	4	*	9	3	52967
10	40638	2	3	4	5	*	11	3	52967
11	40619	1	2	3	4	*	13	3	*
12	40561	1	2	3	4	*	15	3	*
13	40569	1	2	3	4	*	16	3	*
Generate Chill Grid

 

[Jerry Sullivan]

... If that quantity is 5, put the store number in column A in the location and subsequent 4 locations.

Hi,

I'm not sure I understand what you mean by "subsquent 4 locations".

Are you are saying that when you find the first instance of a Store Nbr that has Size=3 and Column E = 5,
there will be another 4 rows somewhere farther down your list that have the same Store Nbr and also have Size=3 and Column E = 5 ?

If that is the case, then one approach with VBA is to use AutoFilters to display only rows where:
Size "=3"and Column E "=5" as a filter criteria

Once you have that, you can use .FormulaR1C1 in the VisibleCells in Column A to copy the Store Nbrs from Column B.

If you want some help with that code, please first confirm that my understanding of your problem is correct.

 

[kazbarina]

Sorry - quite difficult to explain. Columns B to G are static.

An example to understand the data:
(B5) Store 52382 - If size is 4 will require 1 location, if size is 3 will require 2 locations, if size is 2 will require 3 locations, if size is 1 will require 4 locations.

I then have another list - these are locations numbered 1,3,5,9, etc. There is a column called size against each location. For example location 1 has Size as 3.

I need to take the first store 52382 (B5) and the first empty location (M5), get the size of the location (column K5) which is 3, then read across columns D to G to find the column that is headed 3 (E), and then read down to find that the number of locations needed for store 52382 against column 3 is 2. This means that store 52382 needs 2 locations.

I need to store the store number 52832 and populate in the first 2 empty locations (K5 and K6) the store number 52382 - as shown in the screen print.

I then need to repeat the above for the next store and so on.

Have I explained this any clearer?

Thanks

 

[Jerry Sullivan]

That helps. I wasn't following that your screen shot is showing two tables side by side - I assumed that each row read all the way accross for a Store.

I also read this to mean that your goal was to populate Column A (not shown).

If that quantity is 5, put the store number in column A in the location and subsequent 4 locations.

It looks like your goal is to populate Column M. Did you mean to write Column B in the quote above?

 

[Jerry Sullivan]

I have to wrap up for the night...

Here is some code based on my understanding of what you want.
It assumes that your Store Numbers are in Column B.

Sub Allocate_Stores()
    Dim rngStoreNrs As Range
    Dim cStore As Range, cNextLocation As Range
    Dim lngRow As Long, lngSize As Long, lngReqd As Long
    Application.ScreenUpdating = False
    On Error GoTo ErrorHandler
 
    With Worksheets("Generate Chill Grid")
        Set cNextLocation = .Cells(.Rows.Count, 13).End(xlUp).Offset(1)
        Set rngStoreNrs = .Range("B5:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
        For Each cStore In rngStoreNrs
            lngSize = cNextLocation.Offset(0, -1)
            lngReqd = cStore.Offset(0, 5 - lngSize)
            cNextLocation.Resize(lngReqd, 1) = cStore.Value
            Set cNextLocation = cNextLocation.Offset(lngReqd)
         Next cStore
    End With
ErrorHandler:
    Set rngStoreNrs = Nothing
    Set cStore = Nothing
    Set cNextLocation = Nothing
End Sub

Please let me know if I misunderstood.

 

[kazbarina]

Hi Yes,

Read stores numbers in column B and populate column M. I tried your code and many thanks - it doesn't appear to be reading from the size. For example location 1 is size 3. Taking the first store and reading across in rows D4 to G4 to find 3 and then reading down against store. This quantity of 2 needs to be the value and the number of store locations populated - as in my scrren shots.

Thanks

 

[Jerry Sullivan]

Hmmm...It it does what you want it to in my workbook.

My guess is that one of the columns or rows is in a different place in my mockup.

Try this version for debugging.
I will will allow you to follow each step as it reads the size and number reqd.

You can hit CTRL + Break to stop the loop anytime.

Sub Allocate_Stores()
    Dim rngStoreNrs As Range
    Dim cStore As Range, cNextLocation As Range
    Dim lngRow As Long, lngSize As Long, lngReqd As Long
    Application.ScreenUpdating = True
    On Error GoTo ErrorHandler
 
    With Worksheets("Generate Chill Grid")
        Set cNextLocation = .Cells(.Rows.Count, 13).End(xlUp).Offset(1)
        Set rngStoreNrs = .Range("B5:B" & .Cells(Rows.Count, "B").End(xlUp).Row)
        For Each cStore In rngStoreNrs
            lngSize = cNextLocation.Offset(0, -1)
            lngReqd = cStore.Offset(0, 5 - lngSize)
 
           MsgBox "Reading Cell: " & cNextLocation.Offset(0, -1).Address & _
               " Size= " & cNextLocation.Offset(0, -1)
           MsgBox "Reading Cell: " & cStore.Offset(0, 5 - lngSize).Address & _
               " Number Required= " & cStore.Offset(0, 5 - lngSize)
 
            cNextLocation.Resize(lngReqd, 1) = cStore.Value
            Set cNextLocation = cNextLocation.Offset(lngReqd)
         Next cStore
    End With
ErrorHandler:
    Set rngStoreNrs = Nothing
    Set cStore = Nothing
    Set cNextLocation = Nothing
End Sub

 

[kazbarina]

Hi,

Sorry for the tardy reply - My daughter has been ill and I've not had chance to try this. It works great. Many thanks for you help

Karen