Writing VB for Excel - Visual Basic 4 / 5 / 6

Hi,
I have an excel spreadsheet that populates columns based on a user's input. I was previously handling this with If statements in excel but have hit the nesting limitation so I wrote a Case statement but it only seems to work on the first line which I know has to do with my range but every change I make to the range screws up the spreadsheet.
Any help would be appreciated. I am attaching the code below.
Thanks,
A

Function TheSelectCase()

Select Case ("I3")
Case "OPEN"
Range("L3").Value = 1
Case "OPEN/CATH"
Range("L3").Value = 1
Case "OPEN/MRI"
Range("L3").Value = 1
Case "OPEN/OTHER"
Range("L3").Value = 1
Case "OPEN/TTE"
Range("L3").Value = 1
Case "OPEN/CT ANGIO"
Range("L3").Value = 1
Case "CATH/OPEN"
Range("L3").Value = 1
Case "MRI/OPEN"
Range("L3").Value = 1
Case "CT ANGIO/OPEN"
Range("L3").Value = 1
Case "TTE/OPEN"
Range("L3").Value = 1
Case Else
Range("L3").Value = 0
End Select

Select Case Range("I3").Text
Case "Closed"
Range("M3").Value = 1
Case "Closed/Cath"
Range("M3").Value = 1
Case "Closed/MRI"
Range("M3").Value = 1
Case "Closed/Other"
Range("M3").Value = 1
Case "Closed/TTE"
Range("M3").Value = 1
Case "Closed/CTAngio"
Range("M3").Value = 1
Case "Cath/Closed"
Range("M3").Value = 1
Case "MRI/Closed"
Range("M3").Value = 1
Case "CT Angio/Closed"
Range("M3").Value = 1
Case "TTE/Closed"
Range("M3").Value = 1
Case Else
Range("M3").Value = 0
End Select

Select Case Range("I3").Text
Case "Cath"
Range("N3").Value = 1
Case "Cath/Open"
Range("N3").Value = 1
Case "Cath/Closed"
Range("N3").Value = 1
Case "Cath/MRI"
Range("N3").Value = 1
Case "Cath/Other"
Range("N3").Value = 1
Case "Cath/TTE"
Range("N3").Value = 1
Case "Cath/CTAngio"
Range("N3").Value = 1
Case "Cath/Non-cardiac"
Range("N3").Value = 1
Case "Open/Cath"
Range("N3").Value = 1
Case "Closed/Cath"
Range("N3").Value = 1
Case "MRI/Cath"
Range("N3").Value = 1
Case "CT Angio/Cath"
Range("N3").Value = 1
Case "TTE/Cath"
Range("N3").Value = 1
Case "Non-cardiac/Cath"
Range("N3").Value = 1
Case Else
Range("N3").Value = 0
End Select

Select Case Range("I3").Text
Case "MRI"
Range("O3").Value = 1
Case "MRI/Open"
Range("O3").Value = 1
Case "MRI/Closed"
Range("O3").Value = 1
Case "MRI/Other"
Range("O3").Value = 1
Case "MRI/TTE"
Range("O3").Value = 1
Case "MRI/CTAngio"
Range("O3").Value = 1
Case "MRI/Non-cardiac"
Range("O3").Value = 1
Case "Open/MRI"
Range("O3").Value = 1
Case "Closed/MRI"
Range("O3").Value = 1
Case "MRI/Cath"
Range("O3").Value = 1
Case "Cath/MRI"
Range("O3").Value = 1
Case "TTE/MRI"
Range("O3").Value = 1
Case "Non-cardiac/MRI"
Range("O3").Value = 1
Case Else
Range("O3").Value = 0
End Select

Select Case Range("I3").Text
Case "TTE"
Range("P3").Value = 1
Case "TTE/Open"
Range("P3").Value = 1
Case "TTE/Closed"
Range("P3").Value = 1
Case "TTE/MRI"
Range("P3").Value = 1
Case "TTE/Other"
Range("P3").Value = 1
Case "TTE/Non-cardiac"
Range("P3").Value = 1
Case "TTE/CTAngio"
Range("P3").Value = 1
Case "MRI/TTE"
Range("P3").Value = 1
Case "Open/TTE"
Range("P3").Value = 1
Case "Closed/TTE"
Range("P3").Value = 1
Case "Cath/TTE"
Range("P3").Value = 1
Case "MRI/TTE"
Range("P3").Value = 1
Case "CT Angio/TTE"
Range("P3").Value = 1
Case "Non-cardiac/TTE"
Range("P3").Value = 1
Case Else
Range("P3").Value = 0
End Select

Select Case Range("I3").Text
Case "CT Angio"
Range("Q3").Value = 1
Case "CT Angio/Open"
Range("Q3").Value = 1
Case "CT Angio/Closed"
Range("Q3").Value = 1
Case "CT Angio/MRI"
Range("Q3").Value = 1
Case "CT Angio/Other"
Range("Q3").Value = 1
Case "CT Angio/Non-cardiac"
Range("Q3").Value = 1
Case "MRI/CT Angio"
Range("Q3").Value = 1
Case "Open/CT Angio"
Range("Q3").Value = 1
Case "Closed/CT Angio"
Range("Q3").Value = 1
Case "Cath/CT Angio"
Range("Q3").Value = 1
Case "MRI/CT Angio"
Range("Q3").Value = 1
Case "Non-cardiac/CT Angio"
Range("Q3").Value = 1
Case Else
Range("Q3").Value = 0
End Select

Select Case Range("I3").Text
Case "Non-cardiac"
Range("S3").Value = 1
Case "Non-cardiac/Open"
Range("S3").Value = 1
Case "Non-cardiac/Closed"
Range("S3").Value = 1
Case "Non-cardiac/MRI"
Range("S3").Value = 1
Case "Non-cardiac/Other"
Range("S3").Value = 1
Case "Non-cardiac/TTE"
Range("S3").Value = 1
Case "Non-cardiac/Cath"
Range("S3").Value = 1
Case "Non-cardiac/CT Angio"
Range("S3").Value = 1
Case "MRI/Non-cardiac"
Range("S3").Value = 1
Case "Open/Non-cardiac"
Range("S3").Value = 1
Case "Closed/Non-cardiac"
Range("S3").Value = 1
Case "Cath/Non-cardiac"
Range("S3").Value = 1
Case "TTE/Non-cardiac"
Range("S3").Value = 1
Case "CT Angio/Non-cardiac"
Range("S3").Value = 1
Case Else
Range("S3").Value = 0
End Select

Select Case Range("I3").Text
Case "Other"
Range("T3").Value = 1
Case "Other/Open"
Range("T3").Value = 1
Case "Other/Closed"
Range("T3").Value = 1
Case "Other/MRI"
Range("T3").Value = 1
Case "Other/TTE"
Range("T3").Value = 1
Case "Other/CT Angio"
Range("T3").Value = 1
Case "Other/Non-cardiac"
Range("T3").Value = 1
Case "MRI/Other"
Range("T3").Value = 1
Case "Open/Other"
Range("T3").Value = 1
Case "Closed/Other"
Range("T3").Value = 1
Case "Cath/Other"
Range("T3").Value = 1
Case "TTE/Other"
Range("T3").Value = 1
Case "CT Angio/Other"
Range("T3").Value = 1
Case "Non-cardiac/Other"
Range("T3").Value = 1
Case Else
Range("T3").Value = 0
End Select

Select Case Range("I3").Text
Case "Standby"
Range("R3").Value = 1
Case Else
Range("R3").Value = 0
End Select
End Function

Aug 5 '08 #1

Subscribe Post Reply

1062

leoce7

Hey!

Ok so at a quick glance ....

Your initial case, "Select Case ("I3")" ... I'm not sure it is recognizing this because it is not told that it is a cell/single range. This statement assumes an arbitrary string "I3" rather than (what I assume you intend) the cell I3. Also, if you are having trouble changing the "value" of range I would suggest using the following instead: Cells(Row_Index, Column_Index).value = "blah"

Aug 21 '08 #2

QVeen72

1,445

Expert 1GB

Hi,

What Leoce said, is correct, you need not refer a Range for single cell...
just use "Cells"

Also Your Select statement can be simplified:

Expand|Select|Wrap|Line Numbers

 
Select Case Range("I3").Text

    Case "Other", "Other/Open", "Other/Closed","Other/MRI","Other/CT Angio"

        Range("T3").Value = 1

    Case Else

        Range("T3").Value = 0

End Select

If same thing you are doing, then a Comma Seprated Case will work

Regards
Veena

Aug 23 '08 #3

Similar topics

Retaining a Comma when writing to a CSV

by: Deepa | last post by:

I am writing a console app in c# wherein am converting a dataset into a CSV file. It works fine. But I have some values in the dataset which have a comma within(eg. A,B,C). When I view the CSV file...

.NET Framework

Auto writing Excel spreadsheets from Access (97 & 2K) fails to releaseExcel

by: Mr. Smith | last post by:

Hello all, My code can successfully open, write to, format and save several worksheets in a workbook then save it by a given name, close and quit excel. My problem is that if I try and do it...

Microsoft Access / VBA

Writing into excel spreadsheet

by: bob | last post by:

My program copys things from a word document and puts them into an excel spreadsheet. The problem I have though is these annoying symbols that appear in place of spaces in the text after Ive...

C# / C Sharp

Writing and reading from a template Excel file using ADO.Net - results not recalculated

by: RJN | last post by:

Hi I've a template excel file which has all the calculations defined. There are certain input values to be entered which gives a lot of output to the user. I don't want to expose the excel sheet...

Visual Basic .NET

Writing to file and Excel

by: RahimAsif | last post by:

I am designing a data acquisition program that peridically collects data from a panel and writes to a file. The way I am doing it right now, every time I have data from the panel, I open the file,...

C# / C Sharp

Writing French Accented Characters in Excel using Perl.

by: shintu | last post by:

Hallo, I am trying to write french accented characters é è ê in Excel worksheet using my perl script , But I am stuck here as I couldnt find a way of writing it !: My code: use strict;...

Perl

Writing into an Existing Excel file

by: thanawala27 | last post by:

Hi, I had problems writing in an existing Excel File. There is an excel file with a worksheet. I would like to open this existing Excel file and add another worksheet and write data into it....

Perl

Writing special characters to excel html

by: jpero09 | last post by:

Having some trouble writing out accented characters to an ms-excel type output from my asp.net page (c#). I've tried a few different charsets but every time the è on my page is getting rendered as...

.NET Framework

Performance issue whilst writing to Excel

by: =?Utf-8?B?ZmhpbGxpcG8=?= | last post by:

We have a code snippet that downloads data to Excel. it is writing row by row. This causes a performance issue. Any ideas on how to speed this up will be appreciated. Please find below an...

C# / C Sharp

Writing to ms excel

by: Marin Brkic | last post by:

Hello all, please, let me apologize in advance. English is not my first language (not even my second one), so excuse any errors with which I'm about to embarass myself in front of the general...

Python

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice