I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!
--
Craig

To get started
-right click on the sheet tab that you would like to put the macro and
select "view code"
-In the drop down box that says "(General)" select Worksheet
-The second drop down should say "SelectionChange" but if it doesn't select
it from the list.
-In the "Private Sub" that says Worksheet_SelectionChange you will enter the
code for the select case

This is what it should look like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''SelectionChange event runs the macro every time a change
''is made on the worksheet

Dim Rng1 As Range
Dim Rng2 As Range

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
Rng2 = ThisWorkbook.Sheets("Sheet1").Range("A2")

Select Case Rng1.Value
Case 1 'single value
Rng2.Value = "a" '<< place in quotations what
'you want to see in cell a2
Case 2 To 10 'all values from x to x
Rng2.Value = "b"
Case 11, 13 'value x and value x
Rng2.Value = "C"
Case 12 'single value
Rng2.Value = "D"
'Keep adding as many cases as you need
End Select 'this ends the case select function
End Sub

hope that helps


"Craig" wrote:

I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!
--
Craig

Hi,

If it is a simply lookup then maybe the INDEX function will suffice.
This small example will output 102 if the value in A1 is 2. Expand with
values to suit you 36 alternatives.

=INDEX({101,102,103},1,A1)

Cheers
Andy

Craig wrote:
I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Another alternative is to use the VLOOKUP function:

Assume A1 and A2 are in Sheet1. Create a "lookup" table in Sheet2 in A1 to
B36 where A1=1, B1=unique value 1, A2=2, B2=unique value 2 .....etc

in A2 in sheet1 put "=VLOOKUP(A1,Sheet2!A1:A36,2)" (without "s)

As A1 is changed , A2 will change.

"JNW" wrote:

To get started
-right click on the sheet tab that you would like to put the macro and
select "view code"
-In the drop down box that says "(General)" select Worksheet
-The second drop down should say "SelectionChange" but if it doesn't select
it from the list.
-In the "Private Sub" that says Worksheet_SelectionChange you will enter the
code for the select case

This is what it should look like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''SelectionChange event runs the macro every time a change
''is made on the worksheet

Dim Rng1 As Range
Dim Rng2 As Range

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
Rng2 = ThisWorkbook.Sheets("Sheet1").Range("A2")

Select Case Rng1.Value
Case 1 'single value
Rng2.Value = "a" '<< place in quotations what
'you want to see in cell a2
Case 2 To 10 'all values from x to x
Rng2.Value = "b"
Case 11, 13 'value x and value x
Rng2.Value = "C"
Case 12 'single value
Rng2.Value = "D"
'Keep adding as many cases as you need
End Select 'this ends the case select function
End Sub

hope that helps


"Craig" wrote:

I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!
--
Craig

My solution assumes the unique values are "contstants". If they are computed,
then JNW solution is required.

"Craig" wrote:

I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!
--
Craig

Thanks to all who replied, JNW, I wrote your piece of code into my workbook
but it gace an error called Object Variable or With block variable not set.
When I clicked debug it highlighted this line

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")

Any ideas why it would do this?

Cheers


Craig
--
Craig


"JNW" wrote:

To get started
-right click on the sheet tab that you would like to put the macro and
select "view code"
-In the drop down box that says "(General)" select Worksheet
-The second drop down should say "SelectionChange" but if it doesn't select
it from the list.
-In the "Private Sub" that says Worksheet_SelectionChange you will enter the
code for the select case

This is what it should look like:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''SelectionChange event runs the macro every time a change
''is made on the worksheet

Dim Rng1 As Range
Dim Rng2 As Range

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")
Rng2 = ThisWorkbook.Sheets("Sheet1").Range("A2")

Select Case Rng1.Value
Case 1 'single value
Rng2.Value = "a" '<< place in quotations what
'you want to see in cell a2
Case 2 To 10 'all values from x to x
Rng2.Value = "b"
Case 11, 13 'value x and value x
Rng2.Value = "C"
Case 12 'single value
Rng2.Value = "D"
'Keep adding as many cases as you need
End Select 'this ends the case select function
End Sub

hope that helps


"Craig" wrote:

I am totally ignorant to the processes and language of VBA and have been told
that I need to use a Macro to replace an IF statement that I had tried to
enter into a cell. The IF statement needed more than 7 functions so I needed
to approach the problem in a different way and this is where Select Case was
suggested.

What I need to do is quite straight forward but I have no idea how to write
it in VB, I need to look at a cell (A1) which will contain a number 1-36,
depending on the entry in that cell I require a unique figure to be displayed
in a different cell (A2), I also need cell(A2) to update automatically as
cell(A1) is altered.

Could somebody please help me in as easy to understand language as possible.

Sorry for asking such a trivial question!
--
Craig

You need to use SET

Set Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")

Cheers
Andy

Craig wrote:
Thanks to all who replied, JNW, I wrote your piece of code into my workbook
but it gace an error called Object Variable or With block variable not set.
When I clicked debug it highlighted this line

Rng1 = ThisWorkbook.Sheets("Sheet1").Range("A1")

Any ideas why it would do this?

Cheers


Craig

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info