CODEWITHSUNDEEP
vbaexcel
hbabbar
hbabbar

Reputation: 967

How to create a named range of cells based on value of another column in excel vba

I have the following sheet of data 

Channel Quarter Week value
a       1       1    5811
a       1       2     199
a       1       1    8111
a       2       2     881
a       2       1     124
b       2       2    1991

I need to update the value column based on the combination of Channel and Quarter columns (I have created a helper column which would serve as a key)

Helper Channel Quarter Week value
 a1      a       1       1    5811
 a1      a       1       2     199
 a1      a       1       1    8111
 a2      a       2       2     881
 a2      a       2       1     124
 b2      b       2       2    1991

From my VBA form, I have a created a dictionary with the names in these helper columns like

 cellDict = [a1:= 1000, 2:= 2000, b1:= 500 etc]

Now I want to update the value of 'value' column on the basis of this dictionary in such a way that the cells corresponding to a1 be updated with 1000/3 each (As the number of rows for a1 is 3) and similarly cells corresponding to a2 be updated with 1000/2 each.

The attempt was to create a named range for value column with separate helper names and then iterate over each named range and update the cell value by taking the total value and dividing it by count of that named range

So, my question is how do I create a named range with value column on the basis of helper column so that I have (Assuming value is column V)

Range("a1") = V1:V3
Range("a2") = V4:V5 etc..

Upvotes: 2

Views: 718

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

I think you can do this without named ranges in a lot easier way.

First of all, I assume the rows are sorted by your helper column!
Just find the first a1 in the helper column eg. with find method and get its row number as rowStart.

Then use WorksheetFunction.CountIf Method to count the occurrence of a1 in the helper column as rowCount. So rowEnd = rowStart + rowCount - 1.

Now you can loop like …

Dim iRow As Long
For iRow = rowStart to rowEnd
    'use your dictionary and rowCount to replace values here.
    'Example for accessing the cell:
    Worksheets("MySheet").Cells(iRow, "E").Value = "" 
Next iRow

Upvotes: 2

Related Questions