Case Statement w/ Excel

Microsoft Excel 2007
October 31, 2009 at 12:23:48
Specs: Windows XP, 3.0/4GB
I have the following code:

Application.CutCopyMode = False

Select Case True
Case Cells(2, 2) = ""
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Case Cells(3, 2) = ""
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Case Cells(4, 2) = ""
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Case Cells(5, 2) = ""
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Select

Everything works for the first two cases but not the rest of the cases. Also tried doing this with some nested if/then/elseif/thens and same thing the first two ran but not the rest of the elseifs.

Please Help!!

See More: Case Statement w/ Excel

Report •

October 31, 2009 at 13:29:02

Regarding Select Case,

The code you have posted will not work, because :
a. You must pass a variable (or expression) to the Select case on the first line. You have passed it the constant TRUE.
b.The individual Case statements are not values against which the Select Case variable can be tested.
Each case statement is like an if(b=a, then do this)
where an individual case statement is the' IF(b=' part
and the Select case is the a
The next Case statement is IF(c=a, then do this

Only the code after ONE Case statement is run.

The idea of Select Case is that you pass it a variable, which could be a variable that changes during the execution of your program or it could be the value of a Cell.

Select case then goes down each Case statement to see if the value of the variable passed to it matches the Case statement. If it matches, it runs any lines of code from the matching Case until it encounters the next case statement, and then exits the Select Case structure

Here is a simple example:
This subroutine sets a value in the Active Cell and then the Select Case takes the value in the Active cell and makes comparisons.
Here it finds no matches, and runs the code following Case Else
outputting the message
"Active cell doesn't contain a, b or c"

Sub Temp()
ActiveCell.Value = "v"

Select Case ActiveCell
    Case "a"
        MsgBox "Active cell contains a"
    Case "b"
        MsgBox "Active cell contains b"
    Case "c"
        MsgBox "Active cell contains c"
    Case Else
        MsgBox "Active cell doesn't contain a, b or c"
End Select
End Sub

To be able to help, you need to say what you are trying to do, rather than just saying some code you have written doesn't work. Without knowing what you intended your code to do, it is very hard to propose a solution - which we would like to do.

It looks as though one of the things you are trying to do is to copy the range J3:J18 on the Worksheet named "Current" and paste it into the Range B2:B17 on the "YTD" worksheet.

This can be done with one line:
ActiveWorkbook.Worksheets("Current").Range("J3:J18").Copy Destination:=ActiveWorkbook.Worksheets("YTD").Range("B2:B17")

I note that you are using PasteSpecial with PasteValues. Is this what you intended - because you now loose the links to the source data. If the source data changes, the pasted cells will not change.

I suspect that your code has come in part from recording a Macro.

Recording macros can be a useful way to find out how Excel works, but there are several issues such as:
1. It uses a lot of Select statements, which are rarely required in VBA
2. It will include lots of default values which are not needed, such as Operation:=xlNone

Anyway give us some specifics to work on.


Report •

November 1, 2009 at 10:09:35
It's basically a payroll spreadsheet that I'm designing for my business and the range that I'm dealing with is the current gross pay and then I'm copying it to the first empty row of data. Therefore moving from pay period to pay period without having to do it manually. So yes I'm trying to use just the values and don't need anything linked. Yes I did try to record the macro first to see how it was working. I have done the select case statement like that in regular VB so I figured it would work. I first tried to do this with some nested if/then/elseif/then statements, but only the first if and elseif got ran. Even tried to use just an else after the elseif and that didn't run either. I've never really programed in Excel before so I know I'm using pretty basic programing stuff. Any help you could give would be great.


Report •

November 1, 2009 at 11:02:02

There are many ways to do this.

This one has the YTD worksheet setup with names for each pay period already in place.

In this example the Pay period headings are in Row 2 of the YTD worksheet and in cells C2 to H2 (you will need more of course)

On the Source worksheet named "Current", the gross pay each pay period appears in cells J3 to J18.

On the "Current" worksheet, open the Control Toolbox toolbar, select and add a command button.
Right click the button, select properties and change the name to:CopyToYTD
Change the Caption to Copy To YTD

Right click and select View Code.

Enter this code (The CopyToYTD_Click and End Sub will already be showing, so just add the code between, plus Option Explicit before the line Private Sub CopyToYTD_Click()):

Option Explicit
Private Sub CopyToYTD_Click()
Dim strPP As String
Dim rngCell As Range
Dim rngDest As Range
Dim blnPresent As Boolean

On Error GoTo ErrHnd

strPP = InputBox("Enter pay period", "Move pay to YTD file")

blnPresent = False
For Each rngCell In ActiveWorkbook.Worksheets("YTD").Range("C2:H2")
    If rngCell.Text = strPP Then
       'found the pay period name, so keep a copy of its address
       blnPresent = True
       Set rngDest = rngCell
       Exit For
    End If
Next rngCell
If blnPresent = False Then
    'didn't find the pay period name
    MsgBox ("The pay period name you entered '" & strPP & "' does not exist - please try again")
    Exit Sub
End If

'the pay period name exists, so move the data
rngDest.Offset(1, 0).PasteSpecial xlPasteValues
'normal program exit
Exit Sub
'error handler
End Sub

Return to the worksheet and from the Control Toolbox toolbar Exit Design Mode.

Enter sample data in cells J3 to J18
Enter the pay period headers on the YTD worksheet in cells C2 to H2:

	C	D	E	F	G	H
2	Pay-1	Pay-2	Pay-3	Pay-4	Pay-5	Pay-6

Back on the worksheet "Current" , click the button, and you will get an input box asking for a pay period name, enter Pay-1 and OK.

The data will now be transferred to the cells under the pay period name selected.

There are other ways to do this such as using a search for the last used column in the results range on YTD and pasting to that, rather than asking for a name.

Anyway tell me what you think - is this what you were looking for?


Report •

Related Solutions

Ask Question