Results 1 to 8 of 8

Thread: [RESOLVED] Need help with visual basic

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    8

    Resolved [RESOLVED] Need help with visual basic

    Hello!
    Basicly this is my code but its not working properly, i have excel file and i have to make macro which makes student names red if they have more than 3 grades under 4. And with mensage box show how many students are like that who are have 3 under 4 grades.

    I feel problem is here but i dont understand how properly write it.
    Code:
    ''negativeResults = 0
      If Column < 4 Then negativeResults = negativeResults + 1
      Next
      If negativeResults > 3 Then''
    I added attachment of how it looks in excel

    Name:  Tveršana.jpg
Views: 209
Size:  47.0 KB


    Code:
    Public Sub labd_3speck()
    StudN = ActiveSheet.UsedRange.Rows.Count
    S = 0
    C = 0
    For i = 3 To StudN
    For j = 5 To 13
    negativeResults = 0
      If Column < 4 Then negativeResults = negativeResults + 1
      Next
      If negativeResults > 3 Then
    Cells(i, 1).Font.Color = RGB(255, 0, 0)
    Cells(i, 2).Font.Color = RGB(255, 0, 0)
    S = S + 1
    Exit For
    End If
    
    Next
    
    MsgBox (S)
    End Sub
    Last edited by si_the_geek; Nov 22nd, 2020 at 02:04 PM. Reason: added Code tags

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Need help with visual basic

    Welcome to VBForums

    I have moved this thread from the 'VB.Net' forum to the 'Office Development/VBA' forum (for the VB editor in Excel etc).

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Need help with visual basic

    If Column < 4
    any idea which column you are referring to?

    see if this works for you
    Code:
    For i = 3 To StudN
        negativeResults = 0
        For j = 5 To 13
            If Cells(i, j) < 4 Then
                negativeResults = negativeResults + 1
            End If
        Next
        If negativeResults > 3 Then
            Cells(i, 1).Resize(, 2).Font.Color = RGB(255, 0, 0)
            S = S + 1
        End If
    Next
    MsgBox S
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    8

    Re: Need help with visual basic

    Name:  attēls_2020-11-24_095245.jpg
Views: 195
Size:  35.2 KB

    Thanks for the reply, but its not really working, i attached photo, what could be the problem why its not working?

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Need help with visual basic

    post a sample workbook, zip first and attach to post

    even if you post the data as text table, i could at least copy it into a worksheet to test the code, i am not going to populate a worksheet with data from an almost unreadable image for testing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    8

    Re: Need help with visual basic

    Hey added the excel file here.



    Stud_saraksti_LAB_sp_big.xlsx (4) (1).7z

    I hope i did it right and u can open it.

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Need help with visual basic

    I hope i did it right and u can open it.
    better to save as .zip, but i did manage to get it open

    i only modified the incorrect part of the code, so you still need the first few lines, though c is not used and s will be 0 at that point anyway, so you only really need the first line or some equivalent, here is the complete version
    Code:
    Public Sub qw()
    StudN = ActiveSheet.UsedRange.Rows.Count
    S = 0
    C = 0
    For i = 3 To StudN
        negativeResults = 0
        If i = 76 Then Stop
        For j = 4 To 13
            If Cells(i, j) < 4 Then
                negativeResults = negativeResults + 1
            End If
        Next
        If negativeResults > 3 Then
            Cells(i, 1).Resize(, 2).Font.Color = RGB(255, 0, 0)
            S = S + 1
        End If
    Next
    MsgBox S
    End Sub
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2020
    Posts
    8

    Re: Need help with visual basic

    Thank you very much, its working

Posting Permissions

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



Click Here to Expand Forum to Full Width