ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   VBA Code Not Working (https://www.excelbanter.com/excel-discussion-misc-queries/265403-vba-code-not-working.html)

tictox
VBA Code Not Working
 
Hi. I am trying to find the flaw in my code. I've been staring at it for some time, and can't seem to figure it out. I am working with a spread sheet that someone else created. I am wondering if there is an excel quirk I am unaware of or if I am just missing my mistake. I am new to excel and vba.

Below is my code. I am getting 17 for a when I should be getting 18, but the total for all combined is correct. One of the 'a' values is being counted for misc.

Code:
Private Sub CommandButton1_Click()
    Dim misc    As Integer         
    Dim a    As Integer         
    Dim b  As Integer         
    Dim c As Integer         
    Dim d As Integer         
    Dim e    As Integer         
   
    Dim r As range
   
    'Create For-Each Loop to cycle through cells.
    For Each r In Worksheets("INPUT SHEET").range("F9:F335")
        If (UCase(r.Offset(0, -4).Value) < "CURED") Then
                Case "A"
                    a = a + 1
                   
                Case "AA"
                    a = a + 1
                   
                Case "AAA"
                    a = a + 1
               
                Case "B"
                    b = b + 1
               
                Case "C"
                    c = c + 1
           
                Case "D"
                    d = d + 1
           
                Case "E"
                    e = e + 1
               
                Case Else
                    If (r.Offset(0, -4).Value < "") Then
                        misc = misc + 1
                    End If
            End Select
        End If
    Next r   
    Worksheets("Summary").range("d10").Value = a
    Worksheets("Summary").range("d11").Value = b
    Worksheets("Summary").range("d12").Value = c
    Worksheets("Summary").range("d13").Value = d
    Worksheets("Summary").range("d14").Value = e
    Worksheets("Summary").range("d9").Value = misc
End Sub

David of XL Plus
Quote:
Originally Posted by tictox (Post 958787)
Hi. I am trying to find the flaw in my code. I've been staring at it for some time, and can't seem to figure it out. I am working with a spread sheet that someone else created. I am wondering if there is an excel quirk I am unaware of or if I am just missing my mistake. I am new to excel and vba.

Below is my code. I am getting 17 for a when I should be getting 18, but the total for all combined is correct. One of the 'a' values is being counted for misc.

Code:
Private Sub CommandButton1_Click()
    Dim misc    As Integer         
    Dim a    As Integer         
    Dim b  As Integer         
    Dim c As Integer         
    Dim d As Integer         
    Dim e    As Integer         
   
    Dim r As range
   
    'Create For-Each Loop to cycle through cells.
    For Each r In Worksheets("INPUT SHEET").range("F9:F335")
        If (UCase(r.Offset(0, -4).Value) < "CURED") Then
                Case "A"
                    a = a + 1
                   
                Case "AA"
                    a = a + 1
                   
                Case "AAA"
                    a = a + 1
               
                Case "B"
                    b = b + 1
               
                Case "C"
                    c = c + 1
           
                Case "D"
                    d = d + 1
           
                Case "E"
                    e = e + 1
               
                Case Else
                    If (r.Offset(0, -4).Value < "") Then
                        misc = misc + 1
                    End If
            End Select
        End If
    Next r   
    Worksheets("Summary").range("d10").Value = a
    Worksheets("Summary").range("d11").Value = b
    Worksheets("Summary").range("d12").Value = c
    Worksheets("Summary").range("d13").Value = d
    Worksheets("Summary").range("d14").Value = e
    Worksheets("Summary").range("d9").Value = misc
End Sub


Hi,

It might be easier to start by telling us what this code is trying to achieve.

There just might be a better way

Educo

walterharris
I am not sure this is the right place to post my question but I give it a shot.

I have programmed my computer to turn on at 7am every morning, then using windows Scheduled Tasks, I am opening an excel workbook at 7:05 am. The workbook is programmed to run a vba code on opening. When I wake up around 7:30 am, I am checking what is happening. I am always surprised to see that the code stops running at one moment I am retrieving data from the web, and it is not always the same stage. I am 110 sure this is not coming from the VBA code, as when I run it manually i.e. opening myself the workbook by clicking on it, it works perfectly well.

I am wondering if it does not come from windows or the Scheduled Task software.

Does someone has any idea from where it could come and how to solve this issue ?

Thank you very much for your help.

Sepeteus Jedermann
Quote:
Originally Posted by tictox (Post 958787)
Hi. I am trying to find the flaw in my code. I've been staring at it for some time, and can't seem to figure it out. I am working with a spread sheet that someone else created. I am wondering if there is an excel quirk I am unaware of or if I am just missing my mistake. I am new to excel and vba.

Below is my code. I am getting 17 for a when I should be getting 18, but the total for all combined is correct. One of the 'a' values is being counted for misc.

Code:
Private Sub CommandButton1_Click()
    Dim misc    As Integer         
    Dim a    As Integer         
    Dim b  As Integer         
    Dim c As Integer         
    Dim d As Integer         
    Dim e    As Integer         
   
    Dim r As range
   
    'Create For-Each Loop to cycle through cells.
    For Each r In Worksheets("INPUT SHEET").range("F9:F335")
        If (UCase(r.Offset(0, -4).Value) < "CURED") Then
                Case "A"
                    a = a + 1
                   
                Case "AA"
                    a = a + 1
                   
                Case "AAA"
                    a = a + 1
               
                Case "B"
                    b = b + 1
               
                Case "C"
                    c = c + 1
           
                Case "D"
                    d = d + 1
           
                Case "E"
                    e = e + 1
               
                Case Else
                    If (r.Offset(0, -4).Value < "") Then
                        misc = misc + 1
                    End If
            End Select
        End If
    Next r   
    Worksheets("Summary").range("d10").Value = a
    Worksheets("Summary").range("d11").Value = b
    Worksheets("Summary").range("d12").Value = c
    Worksheets("Summary").range("d13").Value = d
    Worksheets("Summary").range("d14").Value = e
    Worksheets("Summary").range("d9").Value = misc
End Sub
Have you checked out all "a", "aa" and "aaa" values at column B ?
Do you put those letter at column B manually or does those base some calculation ?
If there is any spaces before or after those "a"-marks they are counted as
misc at your code.


All times are GMT +1. The time now is 08:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com