mjgcancio
12-10-2023, 08:27 AM
Hi all.
I'm having a difficulty in figuring out this.
I have the attached spreadsheet with 2 sheets, "ALL" and "GAME TABLES".
In sheet "ALL" I have the values needed to fill sheet "GAMES TABLE", in which the criteria for searching teams are cells D1 and F1.
The tables needed to fill are in ranges:
("I:N")-All games Home Team (Home and Away)
("P:U")-All games Away Team (Home and Away)
("W:AB")-All games Home team when playing Home
("AD:AI")-All games Away team when playing Away
I've googled for a solution and found the following code:
Sub LoopThroughColumns() Dim ws1, ws2 As Worksheet
Dim lastRow As Long, i, j As Long
Dim valueA1 As String
' Set the worksheet object
Set ws1 = ThisWorkbook.Worksheets("ALL") ' Replace "Sheet1" with your sheet name
Set ws2 = ThisWorkbook.Worksheets("GAME TABLES") ' Replace "Sheet2" with your sheet name
' Get the last row in column A
lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
' Get the value of cell D1
valueD1 = ws2.Range("D1").Value
' Loop through the columns E and F
For i = 5 To 6 ' Change the column numbers as per your requirement
' Loop through the rows
For j = 2 To lastRow
' Check if the value in cell D1 matches the value in column E
If ws1.Cells(j, 1).Value = valueA1 Then
' Retrieve the values from columns B and C
Dim valueB As Variant
Dim valueC As Variant
valueB = ws1.Cells(j, i).Value
valueC = ws1.Cells(j, i + 1).Value
' Do something with the retrieved values
' For example, print them in the Immediate Window
Debug.Print "Value of column B: " & valueB
Debug.Print "Value of column C: " & valueC
End If
Next j
Next i
End Sub
But now I'm stuck in advancing for the rest, and could use a little help.
Thanks in advanced, best regards
MC
I'm having a difficulty in figuring out this.
I have the attached spreadsheet with 2 sheets, "ALL" and "GAME TABLES".
In sheet "ALL" I have the values needed to fill sheet "GAMES TABLE", in which the criteria for searching teams are cells D1 and F1.
The tables needed to fill are in ranges:
("I:N")-All games Home Team (Home and Away)
("P:U")-All games Away Team (Home and Away)
("W:AB")-All games Home team when playing Home
("AD:AI")-All games Away team when playing Away
I've googled for a solution and found the following code:
Sub LoopThroughColumns() Dim ws1, ws2 As Worksheet
Dim lastRow As Long, i, j As Long
Dim valueA1 As String
' Set the worksheet object
Set ws1 = ThisWorkbook.Worksheets("ALL") ' Replace "Sheet1" with your sheet name
Set ws2 = ThisWorkbook.Worksheets("GAME TABLES") ' Replace "Sheet2" with your sheet name
' Get the last row in column A
lastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
' Get the value of cell D1
valueD1 = ws2.Range("D1").Value
' Loop through the columns E and F
For i = 5 To 6 ' Change the column numbers as per your requirement
' Loop through the rows
For j = 2 To lastRow
' Check if the value in cell D1 matches the value in column E
If ws1.Cells(j, 1).Value = valueA1 Then
' Retrieve the values from columns B and C
Dim valueB As Variant
Dim valueC As Variant
valueB = ws1.Cells(j, i).Value
valueC = ws1.Cells(j, i + 1).Value
' Do something with the retrieved values
' For example, print them in the Immediate Window
Debug.Print "Value of column B: " & valueB
Debug.Print "Value of column C: " & valueC
End If
Next j
Next i
End Sub
But now I'm stuck in advancing for the rest, and could use a little help.
Thanks in advanced, best regards
MC