Excel Problem Using Case and worksheet change

**DaaJ** · 02-25-2008, 08:40 AM

Hello,
I'm having problems programming a template that I am developing. I am some what a cross between a novice and intermediate. Most of what I know has been self taught. I'll try to articulate my problem.

I am working with to column ranges where data is selected using the built in validation drop list in excel.
2) I have the worksheet change vb function that runs a macro if something is changed on the sheet.
3)the code to be run should check both column ranges. If the text in each cell meets acriteria then put a value in an adjacent field.
4) I have never used case statements before and was told its best to use these because of the various permutations that need to be performed.

E.G

Range 1 = "performanceInd"
Range 2 = "behaviourInd"

(there is a column between both ranges not sure if this makes a difference)

the macro is basically saying this
Select Case Range("performanceInd:behaviourInd").Text

'if the user selects outstanding and outstanding from both ranges then set prprating to "A"....

Please Login or Register  to view this content.

'its saying if an individual gets a performance marking of outstanding and a behavioural marking of outstanding then give marking "A" in an adjacent cell to the individual.

I can send file if it helps although I think I have provided enough info. Grateful for some help. Thanks a million in advance.

**royUK** · 02-25-2008, 08:50 AM

Welcome to the Forum. Please read the rules & wrap your code with Code Tags. I will edit this post for you.

**royUK** · 02-25-2008, 08:59 AM

You can't get a text from a range like that. This might work

Please Login or Register  to view this content.

**DaaJ** · 02-25-2008, 10:24 AM

Originally Posted by royUK

You can't get a text from a range like that. This might work

Please Login or Register  to view this content.

Roy.. Thanks! However, it doesn't work. The range prprating does not change.. Can you shed any more light on what I might be doing wrong. I have attached a snap shot of the template which should give an idea of what I am trying to achieve. thanks again

**royUK** · 02-25-2008, 11:00 AM

The range won't moved, it is a Named Range & is fixed.An example workbook would be better than an image.

**DaaJ** · 02-25-2008, 12:03 PM

Roy here you go..I hope that this helps me to sort this out. My deadline is fast approaching and I haven't managed to crack it. File attached

**DaaJ** · 02-26-2008, 12:39 PM

Originally Posted by royUK

The range won't moved, it is a Named Range & is fixed.An example workbook would be better than an image.

sent yesterday but not sure if you saw this..

Roy here you go..I hope that this helps me to sort this out. My deadline is fast approaching and I haven't managed to crack it. File attached

**royUK** · 02-27-2008, 06:56 AM

The first error occurs because you don't have a Splash Sheet.

You are giving prpRating a value of A, but where do you want this writing to?

**royUK** · 02-27-2008, 07:12 AM

This might be what you want

Please Login or Register  to view this content.

**DaaJ** · 02-27-2008, 08:21 AM

Originally Posted by royUK

The first error occurs because you don't have a Splash Sheet.

You are giving prpRating a value of A, but where do you want this writing to?

Roy,

I deleted all the other sheets because I just wanted focus to be on the problem i.e. working with the two ranges and depending on the permutations of the choices made give a rating. Everything else works. I have tested the splash sheet setting it visible and invisible etc.. It all works except the problem I've mentioned.

I must say I do appreciate all your help to date. (sent private msg)

Daaj.

**DaaJ** · 02-27-2008, 08:30 AM

Originally Posted by royUK

The first error occurs because you don't have a Splash Sheet.

You are giving prpRating a value of A, but where do you want this writing to?

I want it writing to the cell in the row that falls in the prprating range
e.g Cell 1 = good, cell2 = fair prprating = "C"

Thanks

I attach the spreadsheet again.

**royUK** · 02-27-2008, 08:39 AM

I see now that you are using Merged Cells in your data table, I would strongly recommend that you forget Excel has Merged Cells. This amended code places the grades in Column L

**DaaJ** · 02-27-2008, 08:56 AM

Originally Posted by royUK

I see now that you are using Merged Cells in your data table, I would strongly recommend that you forget Excel has Merged Cells. This amended code places the grades in Column L

Hi you appear to be a genious. Its working a bit but here is the final problem

1) I want the raiting to change depending on the combination and I want the macro to run when the user performance an action e.g. using the Private Sub Worksheet_Change(ByVal Target As Range) in excel.

2) The code should be looking at two cells each time and then give the rating not 1. Its the combination of the cells that determine the rating e.g. good + fair = "B", or fair + fair = "C" etc etc.

If we can crack this i'll be elated.

Again thank you is far from sufficient for what you have done so far but i'll say it anyway.. thanks a million.

Daaj

**royUK** · 02-27-2008, 09:51 AM

I think you need to assign values to the ratings & add them together to give the assessment. I think it would be much easier to have a table hidden on another sheet that can be used as lookup to get the rating

**DaaJ** · 02-27-2008, 11:06 AM

Originally Posted by royUK

I think you need to assign values to the ratings & add them together to give the assessment. I think it would be much easier to have a table hidden on another sheet that can be used as lookup to get the rating

Thanks

VLookup is new to me so will have to do some research. Had a look at yours and couldn't figure out the mechanics.

Thanks anyway..

**royUK** · 02-27-2008, 12:18 PM

Is the example giving the results that you want?

Basically, I am suggesting that each result - OUTSTANDINg, etc is assigned a value from a table. These two results are added together to get a value which is then used to assign the rating.

The extra columns & sheet can be hidden from view.

It is much better to use common Excel Functions like VLOOKUP (there's an example workbook on the Functions page at www.excel-it.com) before attempting VBA.