Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Dec 2001
    Location
    Oregon
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If and Left (Excel2K 9.0.3821 SR-1)

    I'm trying to write a macro that will select a workbook sheet with data copied into it, determine the first 3 letters in cell "B2" of that sheet, then delete other sheets in the cell based on those first 3 letters. I've been testing this on a sheet containing August, 2003 data. So far, I can only get this to work when I mark my "If", "End if" lines with a '. When they're available to the macro it goes to my selected sheet, then just sits there--nothing gets deleted! I don't really understand VBA and I'm losing confidence by the minute!!!!! Thanks much for any help!

    The sheet I'm trying to grab the data from shows August, 2003 with a single quote in front of the data in cell "B2", so it looks like this-- 'August, 2003. I've tried the first 3 letters in the macro as Aug and also 'Au. The send key is so that it will go ahead with the delete without operator intervention. Here's what I've been trying to use:

    Sheets("qExportMonthlyData").Select
    If Left(B2, 3) = "Aug" Then
    Sheets(Array("28 days", "29 days", "30 days")).Select
    Sheets("28 days").Activate
    Application.SendKeys ("{ENTER}")
    ActiveWindow.SelectedSheets.Delete
    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If and Left (Excel2K 9.0.3821 SR-1)

    In the Visual Basic Editor, select Tools | Options...
    Tick the check box labeled "Require Variable Declaration."
    Click OK.

    From now on, you will have to declare each variable you use. This may seem a nuisance in the beginning, but believe me, it will save you a lot of grief in the end. In your code, your reference to cell B2 is not valid; the way to do it is either Range("B2") or [B2]. By writing just B2, you are referring to a variable named B2. Without required variable declaration, VBA doesn't complain that you didn't define it, and assumes that B2 is an empty string, or 0. With required variable declaration, you'll get a compile error: variable not defined, and B2 will be highlighted.

    Your code can be rewritten to avoid repeatedly selecting cells and worksheets; this makes execution more efficient.

    Instead of using SendKeys, which can be wacky, I reocmmend setting Application.DisplayAlerts to False before deleting the worksheets, and to True again afterwards. This suppresses the usual prompt.

    If Left(Sheets("qExportMonthlyData").Range("B2"), 3) = "Aug" Then
    Application.DisplayAlerts = False
    Sheets(Array("28 days", "29 days", "30 days")).Delete
    Application.DisplayAlerts = True
    End If

  3. #3
    New Lounger
    Join Date
    Dec 2001
    Location
    Oregon
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If and Left (Excel2K 9.0.3821 SR-1)

    Thank you s-o-o-o much for your response! You are my sanity savior!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •