Results 1 to 5 of 5

Thread: Ignoring blank cells

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2022
    Posts
    2

    Ignoring blank cells

    I am working on tracking the records of MLB teams. I have the records set to be calculated with wins, and losses, but it is counting the blank cells as a loss instead of neutral. Is their a function that would ignore the blank cells within Basic Visual.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    108,499

    Re: Ignoring blank cells

    Blank cells of what? Please provide a FULL and CLEAR explanation of the problem. Maybe you could provide the code you currently have, if you'd like us to address an issue with that code.

  3. #3

    Thread Starter
    New Member
    Join Date
    May 2022
    Posts
    2

    Re: Ignoring blank cells

    Sorry this is my first time working with Basic, so my apologies if I am messing up verbiage. The four columns I have are Home (Pit) Away (CIN) Home score (7) and Away Score (3). However I have the whole schedule for all 30 teams already in the cells. With some games not yet played, the scores are blank. For the code I have,
    Code:
    For start_Line = 2 To end_line
            Loca = Sheet1.Cells(start_Line, 1).Value
            home_team = Sheet1.Cells(start_Line, 3).Value
            away_team = Sheet1.Cells(start_Line, 2).Value
            home_score = Sheet1.Cells(start_Line, 6).Value
            away_score = Sheet1.Cells(start_Line, 5).Value   
            'Away Team Lookup
            away_lookup = Team_Lookup(away_team)
            If away_lookup = 0 Then
                new_line = new_line + 1
                away_lookup = new_line
                Sheet2.Cells(away_lookup, 1).Value = away_team
            End If
            If away_score > home_score Then
                Sheet2.Cells(away_lookup, 2).Value = Sheet2.Cells(away_lookup, 2).Value + 1 
            Else        
                Sheet2.Cells(away_lookup, 3).Value = Sheet2.Cells(away_lookup, 3).Value + 1  
            End If        
            Sheet2.Cells(away_lookup, 4).Value = Sheet2.Cells(away_lookup, 4).Value + 1           
            'Home Team Lookup            
                home_lookup = Team_Lookup(home_team)        
            If home_lookup = 0 Then      
                new_line = new_line + 1
                home_lookup = new_line
                Sheet2.Cells(home_lookup, 1).Value = home_team        
            End If  
            If home_score > away_score Then       
                Sheet2.Cells(home_lookup, 2).Value = Sheet2.Cells(home_lookup, 2).Value + 1                  
            Else        
                Sheet2.Cells(home_lookup, 3).Value = Sheet2.Cells(home_lookup, 3).Value + 1           
            End If      
            Sheet2.Cells(home_lookup, 4).Value = Sheet2.Cells(home_lookup, 4).Value + 1         
                If Loca = "H" Then            
                    If home_score > away_score Then
                        Sheet3.Cells(2, 2).Value = Sheet3.Cells(2, 2).Value + 1
                        Sheet3.Cells(3, 3).Value = Sheet3.Cells(3, 3).Value + 1
                    Else
                        Sheet3.Cells(2, 3).Value = Sheet3.Cells(2, 3).Value + 1
                        Sheet3.Cells(3, 2).Value = Sheet3.Cells(3, 2).Value + 1
                    End If                
                    Sheet3.Cells(2, 4).Value = Sheet3.Cells(2, 4).Value + 1
                    Sheet3.Cells(3, 4).Value = Sheet3.Cells(3, 4).Value + 1                    
                End If        
        Next start_Line   
        start_Line = 2
        end_line = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row   
        For start_Line = 2 To end_line   
            wins = Sheet2.Cells(start_Line, 2).Value
            games = Sheet2.Cells(start_Line, 4).Value  
            Sheet2.Cells(start_Line, 5) = wins / games 
        Next start_Line    
        Sheet3.Cells(2, 5).Value = Sheet3.Cells(2, 2).Value / Sheet3.Cells(2, 4).Value
        Sheet3.Cells(3, 5).Value = Sheet3.Cells(3, 2).Value / Sheet3.Cells(3, 4).Value
    Last edited by dday9; May 25th, 2022 at 08:52 AM. Reason: Added BB Code

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,670

    Re: Ignoring blank cells

    Can you just add "and cell > "" to the logic?
    Please remember next time...elections matter!

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,804

    Re: Ignoring blank cells

    Cleanup on aisle 12!
    Can you please re-post that code but with [code][/code] tags around it? That's preserve the indenting of the code making it easier to read. It'll look like a river of code than a cliff of code. Thanks!

    Now... the problem is your if statement checking to see who got the win:
    Code:
    If away_score > home_score Then
        Sheet2.Cells(away_lookup, 2).Value = Sheet2.Cells(away_lookup, 2).Value + 1
    Else
        Sheet2.Cells(away_lookup, 3).Value = Sheet2.Cells(away_lookup, 3).Value + 1
    End If
    So if the away_score is greater than home_score it does something....
    else it does something else ... but what it doesn't account for is games no played. By default that's going to come out 0 & 0 ... so it hits the if ... 0 is not less than 0 ... so it falls to the else and blindly does what it does...

    What you need to do is check for an away win, else check for a home win ... else potentially do somehting else.
    Code:
    If away_score > home_score Then
        Sheet2.Cells(away_lookup, 2).Value = Sheet2.Cells(away_lookup, 2).Value + 1
    ElseIf away_Score < home_score
        Sheet2.Cells(away_lookup, 3).Value = Sheet2.Cells(away_lookup, 3).Value + 1
    Else
      ' If you need to do something for unplayed games, do it here
    End If

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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