Hi,
As I am a beginner to macros, I have some basic doubt in using switch case.
I am trying to use multiple condition in switch case which uses two column reference value.
For eg. in the below table, there are some common roles in both project A and B. Based on the project I need to set the rate for common roles in column 3. Say for example SIT manager is common to project A and B but the rate for them is different.
I tried using IF condition and it worked fine. Now I need to make it more efficient using Select Case. Please provide me any solution how to achieve it.
| Project | Roles | Rate |
| A | Defect manager | |
| B | SIT manager | 100 |
| A | SIT manager | 150 |
| A | UAT manager | |
| B | NFT manager | 320 |
| B | Automation Engineer | |
| A | NFT manager | 350 |
Sample code for Switch case:
Sub CaseTrial()
Dim role As Range
Dim Rate As Long
Set role = Range("D2:D200")
Range("D2").Select
Select Case role
Case (Selection.Offset(0, 0) = "SIT manager" And Selection.Offset(-1, 0) = "A")
Cells(4, 3).Value = 150
Case Else
Cells(4, 3).Value = 0
'rate = 100
End Select
End Sub
Hi,
Please provide a workbook rather than typing data and code so we don't have to recreate everything.
You can do what you want with a combination of VLOOKUP and IF, you don't need VBA.
Please see attached workbook.
You haven't provided values for all the rates so just fill those in on the worksheet and everything should work.
Please read this explanation of how to use VLOOKUP
Regards
Phil
Hi,
Actually I need to automate the sheet so that no.of resources under one role will be updated easily. I know we can use VLOOKUP in EXCEL but I want to now how we can use macros using Switch case for the same.
Hereby attaching the sheet for your reference.
Hi,
This is why supplying the actual workbook and thoroughly explaining the expected result is so important. The work I did in my first answer was wasted time.
I still don't really understand what you want to do and the sample workbook doesn't look like the actual workbook you will use. I need to work with an accurate copy of the real workbook, with at least a subset of the data. The layout of the sample is not the layout of the real workbook so anything I write will need to be altered to fit the real situation.
What do you mean by 'need to automate the sheet'? Why? For what purpose? How/where do you intend to update the number of resources under one role? What is a resource? It's not mentioned in the workbook. How does a resource relate to a role?
I need to understand what you mean by this and see an example properly laid out in the sheet.
Phil
