About Us

tictox

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

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

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.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code Not Working	tictox	Excel Discussion (Misc queries)	0	July 6th 10 06:40 PM
Help !!! My code is not working as expected.	Ayo	Excel Discussion (Misc queries)	3	August 30th 07 10:39 PM
VB Code Is Not Working	Rob	Excel Discussion (Misc queries)	2	May 30th 07 05:23 PM
Calendar code not working	RobN[_3_]	Excel Discussion (Misc queries)	9	May 17th 07 04:48 AM
Code not working and can't see why	Steve	Excel Discussion (Misc queries)	3	December 31st 04 03:12 PM

Menu

About Us