Need to check 2 cells in a loop that are in Column B and C

[beanzNY]

The 2 values i want to check are in column B and C then go down the list. if the 2 values are met i want it to take the first 3 letters of Column D.. here is an idea of what i want it to do
The list is a schedule from an NBA team Atlanta Hawks. I need it go down my list of dates check to see if they are home or away. If there is no value in column C then strHomeTeam = "Atl", it would then take the date (format it to "yyyymmdd")which would be used to append a url using formatted date text + home team text. (Url+date text, must be in yyyymmdd format + home team text) If column C does contain "@" then strHomeTeam = first 3 letters of opposing team which would be "IND" because both values have been met.

Atlanta Hawks Schedule

• B C D end result
• B is the date column
• C is home/away column
• D is Opposing team column
• end result is date text+ home team text to use for URL

1. 10/29/2009 Portland "20091029" + "Atl"
2. 10/30/2009 Chicago "20091030" + "Atl"
3. 11/1/2009 @ Indiana "20091101" + "Ind"
4. 11/3/2009 @ Sacramento "20091103" + "Sac"

 

[sijpie]

Assuming that the list is not very very large, this code will run fine:

Sub getsched()
Dim Rowcnt As Long, i As Long
Dim StCl As Range
Dim url As String
Set StCl = Range("B3")  'first cell in your range
Rowcnt = StCl.CurrentRegion.Rows.Count
Do While Rowcnt         ' we're going to process from the bottom upwards
    Rowcnt = Rowcnt - 1
    With StCl
        url = """" & Year(.Offset(Rowcnt, 0).Value) & _
            Format(Month(.Offset(Rowcnt, 0).Value), "00") & _
            Format(Day(.Offset(Rowcnt, 0).Value), "00") & """"
        If .Offset(Rowcnt, 1).Value = "@" Then
            url = url & " + ""Alt"""
        Else
            url = url & " + """ & .Offset(Rowcnt, 2).Value & """"
        End If
        .Offset(Rowcnt, 3).Value = url
    End With
Loop
End Sub

This outputs into column E, and starts in row 3. Iff different change to your requirement

 

[beanzNY]

hey thanks but i figured it out

Sub Import_Team_Boxscores()
Dim strCol As String
Dim strRow As String
Dim rngStart As Range
Dim rngEnd As Range
Dim rngCell As Range
Dim date_of_game As String
Dim team As String
Dim home_away As String
Dim i As Integer
On Error GoTo ErrHnd

'setup column letter and first row number containing names
'column
strCol = "B"
'row (number is in double quotes)
strRow = "2"
i = 2
'turn off screen updating to stop flicker & increase speed
Application.ScreenUpdating = False
            
'save this worksheet's name, so we can go back to it later
team = ActiveSheet.Name

'set start of data in selected column
Set rngStart = ActiveSheet.Range(strCol & strRow)
'find end of data in selected column
Set rngEnd = ActiveSheet.Range(strCol & CStr(Application.Rows.Count)) _
            .End(xlUp)

'loop through cells in used range
For Each rngCell In ActiveSheet.Range(rngStart, rngEnd)
    'ignore empty cells in range
    If rngCell.Text <> "" Then
        'format date to 20091028
        date_of_game = Format(rngCell, "yyyymmdd")
        'check to see if team is home or away
        If Range("C" & CStr(i)) = "@" Then
           team = Mid(Range("D" & CStr(i)), 1, 3)
         'change teams abrv. to corresponding webpage url  
          If Range("D" & CStr(i)) = "Golden State Warriors" Then team = "GSW"
           If Range("D" & CStr(i)) = "Los Angeles Lakers" Then team = "LAL"
           If Range("D" & CStr(i)) = "Los Angeles Clippers" Then team = "LAC"
           If Range("D" & CStr(i)) = "New Jersey Nets" Then team = "NJN"
           If Range("D" & CStr(i)) = "New York Knicks" Then team = "NYK"
           If Range("D" & CStr(i)) = "Oklahoma City Thunder" Then team = "OKC"
           If Range("D" & CStr(i)) = "San Antonio Spurs" Then team = "SAS"
           Call Connect_Box_Scores(date_of_game, team)
        Else
            team = ActiveSheet.Name
            
            Call Connect_Box_Scores(date_of_game, team)
            
            On Error GoTo ErrHnd
        End If
    End If
    i = i + 1
Next rngCell

'go back to the source worksheet
Worksheets(team).Activate

'reinstate screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
'go back to the source worksheet
Worksheets(team).Activate
'reinstate screen updating
Application.ScreenUpdating = True
End Sub