Solved

if else statement to select values from workbook

Posted on 2013-12-28
2
314 Views
Last Modified: 2013-12-28
folks I have a function which activates a select from a specific workbook

Function Select Region()

' Activating GenericUser sheet
ActiveWorkbook.Sheets("Region1").Activate

however I would like to change the sheet to be used based on the region as specified in sheet 1 i.e.

if sheet1 b1 = europe then

ActiveWorkbook.Sheets("Region1").Activate


else if sheet1 b1 = asia then

ActiveWorkbook.Sheets("Region2").Activate

else if sheet1 b1 = africa then

ActiveWorkbook.Sheets("Region3").Activate

how do i correctly program this?
0
Comment
Question by:rutgermons
2 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 39743589
Sub Select_Region()
  select [Sheet1!B1]
    case "europe": rg = "Region1"
    case "asia": rg = "Region2"
    case "africa": rg = "Region3"
    case else: rg = "Region4"
  End select
  ActiveWorkBook.Sheets(rg).Activate
End Sub

Open in new window

0
 
LVL 13

Accepted Solution

by:
Ashok earned 500 total points
ID: 39743604
Function Select_Region()
   If Sheet1.Range("B1") = "europe" Then
     ActiveWorkbook.Sheets("Region1").Activate
   ElseIf Sheet1.Range("B1") = "asia" Then
     ActiveWorkbook.Sheets("Region2").Activate
   ElseIf Sheet1.Range("B1") = "africa" Then
     ActiveWorkbook.Sheets("Region3").Activate
   End If
End Function
Test-Function.xlsx
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now