Formula's in VBA coding

[JuliusCas]

I have a question regarding some VBA code I am writing.

It seems to me there should be a simple answer but I haven't been able to find it out.

I want to write a macro to evaluate the selected cell by means of a formula and take action based on the results.

I will illustrate it with an example:
I have a list of tests named: "Quesionnaire (1)", "IQ-test (2)", "Personalitytest (3)" who are randomly listed in my first collumn. On basis of the test type in the first collumn I want the macro to fill the second collumn (and potenally more) with X for (1), Y for (2) and Z for (3). As the most right 3 characters of the options in collumn 1 are sufficient to determine what to put in collumn 2 I want to write a macro that takes te following steps:
- Take the right 3 characters of the selected cell (by means of the "right" formula)
- Evaluate the results of this formula and choose the proper scenario for collumn 2
- Select the cell in collumn 2
- Fill in the correct answer
- Move back to collumn 1 and select the cell one row lower and repeat the steps

I want to take all these steps in a macro. The most complex step I believe is the VBA code for the evaluation of the active cell by means of the results from a formula (in this case the "right" formula).

I have been trying to accomplish this by using the evaluate function and select case but have not succeeded.

Thanks in advance for you reactions.

 

[ChrisM]

You don't need to use the Evaluate function, VBA has all its own built in functions. All you need to do is loop through your range and run the select case like you mentioned.

Dim rCell as Range

For Each rCell In Sheets("Mysheet").Range("A1:A100")
      Select Case Right(rCell,3)
          Case "(1)"
                rCell.Offset(0,1) = "X"
          Case "(2)"
                rCell.Offset(0,1) = "Y"
          Case "(3)"
                rCell.Offset(0,1) = "Z"
      End Select
Next rCell

 

[JuliusCas]

Thank you ChrisM, your solution above has been very helpfull. I am currently trying to get it one step further and was wondering whether you have some suggestions there as well.

I am looking for a solution to accomplish the following:
- I still want to evaluate the cells in a set range (like in the previous part). Only now I want to evaluate the cell on basis of formula's stated in the diferent cases.

So for example:

Select Case rCell
Case 1: IF(OR(LEFT(rCell,3)="(1)",RIGHT(rCell,3)="(1)"))=TRUE then "X"
Case 2: IF(AND(LEFT(rCell;3)="(2)";RIGHT(rCell;3)="(2)"))=TRUE then "Y"

Hopefully it is clear what I am trying to accomplish.

 

[ChrisM]

You wouldn't use a Select Case for that, you'd use an If-Then:

If LEFT(rCell,3)="(1)" OR RIGHT(rCell,3)="(1)" Then
        "X"
ElseIf LEFT(rCell,3)="(2)" AND RIGHT(rCell,3)="(2)" Then
        "Y"
End If