I am trying to figure out how to find a missing number in a range of rows. I can get the following to work by hard coding the statements to look at the first range of cells. LowerVal & UpperVal calculated previously (pseudo code below)
Dim InputRange As Range
Dim ValueFound As Range
Dim LowerVal As Integer
Dim UpperVal As Integer
Dim S1 As Integer
'Set search range
Set InputRange = Range("c2:d6")
'Search for team that has a bye
For S1 = LowerVal To UpperVal
Set ValueFound = InputRange.Find(S1)
If ValueFound Is Nothing Then
Sheets("rawdata").Range("C7") = S1, Sheets(“rawdata”).range(“D7”) = “bye”
End If
Next S1
I am trying to figure out a way to loop thru a series of rows. (The rows are in a format like below). The objective is to identify what team has a bye (their team number is missing) by searching the home & away columns for the team numbers, find the missing team number and then write that number (and the word ‘bye’ to a blank row under the searched range. Note: a separate macro has been used to insert a blank row after each date. If it is easier to insert the blank row as part of this query, then the “blank row insert’ query can be tossed out.
Each season the number of teams, location and the dates will change which will change the amount of data- but not the format. Can I get some help on how to loop thru the raw data, find the missing team for each scheduled date of play and insert their team number in the blank row indicating that they have a bye week?
A sample of the rawdata worksheet is below
ColA ColB ColC ColD ColE
Date Time Home Away Location
5/7/2019 10:00 AM 8 10 4
5/7/2019 10:00 AM 1 9 1
5/7/2019 10:00 AM 7 6 5
5/7/2019 10:00 AM 3 5 2
5/7/2019 10:00 AM 11 4 6
5/14/2019 10:00 AM 2 9 3
5/14/2019 10:00 AM 6 10 2
5/14/2019 10:00 AM 7 5 1
5/14/2019 10:00 AM 1 11 5
5/14/2019 10:00 AM 8 4 4
5/21/2019 10:00 AM 3 1 6
5/21/2019 10:00 AM 5 4 3
5/21/2019 10:00 AM 6 8 1
5/21/2019 10:00 AM 10 2 5
5/21/2019 10:00 AM 9 11 4
Dim InputRange As Range
Dim ValueFound As Range
Dim LowerVal As Integer
Dim UpperVal As Integer
Dim S1 As Integer
'Set search range
Set InputRange = Range("c2:d6")
'Search for team that has a bye
For S1 = LowerVal To UpperVal
Set ValueFound = InputRange.Find(S1)
If ValueFound Is Nothing Then
Sheets("rawdata").Range("C7") = S1, Sheets(“rawdata”).range(“D7”) = “bye”
End If
Next S1
I am trying to figure out a way to loop thru a series of rows. (The rows are in a format like below). The objective is to identify what team has a bye (their team number is missing) by searching the home & away columns for the team numbers, find the missing team number and then write that number (and the word ‘bye’ to a blank row under the searched range. Note: a separate macro has been used to insert a blank row after each date. If it is easier to insert the blank row as part of this query, then the “blank row insert’ query can be tossed out.
Each season the number of teams, location and the dates will change which will change the amount of data- but not the format. Can I get some help on how to loop thru the raw data, find the missing team for each scheduled date of play and insert their team number in the blank row indicating that they have a bye week?
A sample of the rawdata worksheet is below
ColA ColB ColC ColD ColE
Date Time Home Away Location
5/7/2019 10:00 AM 8 10 4
5/7/2019 10:00 AM 1 9 1
5/7/2019 10:00 AM 7 6 5
5/7/2019 10:00 AM 3 5 2
5/7/2019 10:00 AM 11 4 6
5/14/2019 10:00 AM 2 9 3
5/14/2019 10:00 AM 6 10 2
5/14/2019 10:00 AM 7 5 1
5/14/2019 10:00 AM 1 11 5
5/14/2019 10:00 AM 8 4 4
5/21/2019 10:00 AM 3 1 6
5/21/2019 10:00 AM 5 4 3
5/21/2019 10:00 AM 6 8 1
5/21/2019 10:00 AM 10 2 5
5/21/2019 10:00 AM 9 11 4