VBA defined name help

[jdmetcalfe]

Hi i'm pretty new to vba and having real trouble with what should be a simple code.

I have the following vba code which works fine

Sub printlist()


If UCase(Range("T6").Value) = "1" Then Call stocksheetsub
If UCase(Range("T6").Value) = "2" Then Call supplier2sub




End Sub

However instead of using the cell value T6 i would like to replace this with a defined name so

Sub printlist()


If UCase(Range("reportlist").Value) = "1" Then Call stocksheetsub
If UCase(Range("reportlist").Value) = "2" Then Call supplier2sub




End Sub

Everything i try i just get errors. Any help would be much apreciated.

Thanks

James

 

[Tom Urtis]

Maybe all you need is
If UCase("reportlist").Value = "1" Then Call stocksheetsub
and so on

But on another topic, I cannot recall seeing UCASE be invoked when a number is being evaluated as the potential value. I'm not so sure you need to evaluate an integer's case as you would an alphabet letter, nor might you need to place the number in quotes, unless it's being regarded as text somehow.

 

[JoeMo]

What error(s) do you get? Is "reportlist" defined by a formula (e.g. a dynamic range) or is it a hard-coded range e.g.refersTo=$T$6)? Does "reportlist" have a workbook-level or sheet-level scope?

 

[jdmetcalfe]

okay thanks all i need to do is call a macro based on the number in a cell defined by the name report list

I'm not sure the best way to do this but basically

IF T6 = 1 run macro 1
IF T6 = 2 run macro 2
If T6 =3 run macro 3

Thats all i found that other code on forum and it seemed to work

 

[Tom Urtis]

Just using the cell itself as an example:

Select Case Range("T6").Value
case 1: Run "stocksheetsub"
case 2: Run "supplier2sub"
case 3: Run "whatever3sub"
End Select

 

[jdmetcalfe]

Thanks that works