Help to Select Case with dynamic range

sbv1986

Board Regular
Joined
Nov 2, 2017
Messages
74
Hi All

I want to have a Function with select case that:
range value is range(A2: last row) and group by range(B2:lastrow) like below table have 04 group:
The code of function like this:
Code:
Funtion ()
for i = range(A2: lastrow)
select case group by range(B2:last row)
Case a = Array("B004")
'group by range (B2:last row) = "20"
Case a = Array("A002","G010")
'group by range (B2:last row) = "15"
Case a = Array("B002","B010")
'group by range (B2:last row) = "10"
Case Esle  ---> 
'group by range (B2:last row) = "7"
next i
End Function
I'm not sure how to code right to do this, please help me

Thanks./.
AB
1A0017
2B00210
3A00215
4B00420
5C0057
6C0017
7A0107
8B01010
9G01015

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,939
Office Version
365, 2010
Platform
Windows
Since you are looping through the values in Column A, just put the appropriate value that corresponds into Column B.

Code:
Sub FillValues()
Dim c As Range
For Each c In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
    Select Case c
        Case "B004"
            c.Offset(, 1) = 20
        Case "A002", "G010"
            c.Offset(, 1) = 15
        Case "B002", "B010"
            c.Offset(, 1) = 10
        Case Else
            c.Offset(, 1) = 7
    End Select
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,090,247
Messages
5,413,291
Members
403,472
Latest member
ArtisticOwl

This Week's Hot Topics

Top