Need help building a loop

[flindy87]

I need a loop that goes across columns, when it hits a cell E.G 'Team 1' the loop then goes down to a particular row E.G row 11 and fills in team member names that it gets from a list on a different sheet, once it has filled in all of 'Team 1' members, it goes back to moving across columns until it hits the next cell with E.G 'Team 2' ...

Anyone able to provide some code or point me in the right way of a decent tutorial page?

[nathansav]

look at for next loops, and offset.

if you could attach a sample, I could illustrate.

[flindy87]

I've attached an example of what i am dealing with

Thanks

[nathansav]

Dim rngRowToCheck As Excel.Range
Dim rngTeamDetails As Excel.Range
Dim strTeamLookFor As String
Dim rw As Excel.Range
Dim cell As Excel.Range
Dim intPresentationRowNumber As Integer
Dim wsTeamSheet As Excel.Worksheet
Dim wsLookup As Excel.Worksheet

Set wsTeamSheet = ThisWorkbook.Worksheets("Team Sheet")
Set wsLookup = ThisWorkbook.Worksheets("Lookup")

Set rngRowToCheck = wsTeamSheet.Rows("3:3")
Set rngTeamDetails = wsLookup.Range("c1:e35")

For Each cell In rngRowToCheck.Cells

    If LCase(cell.Value) Like "team*" Then
        
        intPresentationRowNumber = 10
        strTeamToLookFor = LCase(cell.Value)
        
        For Each rw In rngTeamDetails.Rows
            If LCase(rw.Cells(, 3).Value) = strTeamToLookFor Then
                wsTeamSheet.Cells(intPresentationRowNumber, cell.Column + 1).Value = rw.Cells(, 2).Value
                intPresentationRowNumber = intPresentationRowNumber + 1
            End If
        Next rw
    
    End If
    

Next cell

[flindy87]

Thanks very much,

I gave the code a test, it seems to be pulling through the Position, rather than the Full name?

[nathansav]

I thought that was a number

wsTeamSheet.Cells(intPresentationRowNumber, cell.Column ).Value = rw.Cells(, 1).Value
wsTeamSheet.Cells(intPresentationRowNumber, cell.Column + 1).Value = rw.Cells(, 2).Value

[flindy87]

Really appreciate all your help, however i may have butchered the code a bit too much, it doesn't seem to work at all now - basically runs through the loop doing nothing. A specific line does not seem to be doing anything at all...."For Each Cell in rngRowToCheck.Cells"

Sub Looptester()

'Dim rngRowToCheck As Integer
'Dim rngTeamDetails As Integer
'Dim strTeamLookFor As String
'Dim rw As Integer
'Dim cell As Integer
'Dim intPresentationRowNumber As Integer
'Dim wsTeamSheet As String
'Dim wsLookup As String



Set wsTeamSheet = ThisWorkbook.Worksheets("Team Sheet")
Set wsLookup = ThisWorkbook.Worksheets("Lookup")

Set rngRowToCheck = wsTeamSheet.Rows("3:3")
Set rngTeamDetails = wsLookup.Range("c2:e35")

For Each cell In rngRowToCheck.Cells

    If LCase(cell.Value) Like "Team*" Then
        
        intPresentationRowNumber = 10
        strTeamToLookFor = LCase(cell.Value)
        
        For Each rw In rngTeamDetails.Rows
            If LCase(rw.Cells(, 3).Value) = strTeamToLookFor Then
                wsTeamSheet.Cells(intPresentationRowNumber, cell.Column).Value = rw.Cells(, 1).Value
                wsTeamSheet.Cells(intPresentationRowNumber, cell.Column + 1).Value = rw.Cells(, 2).Value
                intPresentationRowNumber = intPresentationRowNumber + 1
            End If
        Next rw
    
    End If
    

Next cell

End Sub

[nathansav]

its cause you've change the "team*" to be "Team*" LCASE changes to lower case, therefore this will never be true, leave the t as lower case.

uncomment the dim statements

if at the top of the code you say Option Compare Text, then there is no need for the Lcase bit.

[flindy87]

Ok that has fixed part of the problem, the code now runs and correctly fills out team 1 with the correct team members, however the code then stops with a "Mismatch error" and does not fill out any other team.

Dim rngRowToCheck As Excel.Range
Dim rngTeamDetails As Excel.Range
Dim strTeamLookFor As String
Dim rw As Excel.Range
Dim cell As Excel.Range
Dim intPresentationRowNumber As Integer
Dim wsTeamSheet As Excel.Worksheet
Dim wsLookup As Excel.Worksheet

Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Set wsTeamSheet = ThisWorkbook.Worksheets("Team Sheet")
Set wsLookup = ThisWorkbook.Worksheets("Lookup")

Set rngRowToCheck = wsTeamSheet.Rows("3:3")
Set rngTeamDetails = wsLookup.Range("t2:v" & lastRow)

For Each cell In rngRowToCheck.Cells

    If LCase(cell.Value) Like "team*" Then
        
        intPresentationRowNumber = 10
        strTeamToLookFor = LCase(cell.Value)
        
        For Each rw In rngTeamDetails.Rows
            If LCase(rw.Cells(, 3).Value) = strTeamToLookFor Then
                wsTeamSheet.Cells(intPresentationRowNumber, cell.Column).Value = rw.Cells(, 1).Value
                wsTeamSheet.Cells(intPresentationRowNumber, cell.Column + 1).Value = rw.Cells(, 2).Value
                intPresentationRowNumber = intPresentationRowNumber + 1
            End If
        Next rw
    
    End If
    

Next cell

[flindy87]

Anyone able to help?