Help with loop function

[jyokom]

How can I simplify the following by utilizing the loop function?

Sub Crew1_Change()
Sheets("Setup").Calculate
TB_Pos1_1.Value = Sheets("Setup").Range("AJ9").Text
TB_Unit1_1.Value = Sheets("Setup").Range("AQ9").Text
TB_SSN1_1.Value = Sheets("Setup").Range("AT9").Text

TB_Pos1_2.Value = Sheets("Setup").Range("AJ10").Text
TB_Unit1_2.Value = Sheets("Setup").Range("AQ10").Text
TB_SSN1_2.Value = Sheets("Setup").Range("AT10").Text

TB_Pos1_3.Value = Sheets("Setup").Range("AJ11").Text
TB_Unit1_3.Value = Sheets("Setup").Range("AQ11").Text
TB_SSN1_3.Value = Sheets("Setup").Range("AT11").Text

TB_Pos1_4.Value = Sheets("Setup").Range("AJ12").Text
TB_Unit1_4.Value = Sheets("Setup").Range("AQ12").Text
TB_SSN1_4.Value = Sheets("Setup").Range("AT12").Text

TB_Pos1_5.Value = Sheets("Setup").Range("AJ13").Text
TB_Unit1_5.Value = Sheets("Setup").Range("AQ13").Text
TB_SSN1_5.Value = Sheets("Setup").Range("AT13").Text

End Sub

 

[SydneyGeek]

Try this...

Sub Crew1_Change_Loop()
    Dim i As Integer
    Sheets("Setup").Calculate
    For i = 1 To 5
        Range("TB_Pos_" & i).Value = Sheets("Setup").Range("AJ8").Offset(i, 0).Text
        Range("TB_Unit_" & i).Value = Sheets("Setup").Range("AQ8").Offset(i, 0).Text
        Range("TB_SSN_" & i).Value = Sheets("Setup").Range("AT8").Offset(i, 0).Text
    Next i
End Sub

Denis

 

[jyokom]

I receive a Run-Time Error '1004'

Method 'Range' of Object' _Global' Failed.

 

[hallingh]

Sub Crew1_Change_Loop()
    Dim i As Integer
    Sheets("Setup").Calculate
    For i = 9 To 13
 
    TB_Pos1_1.Value = Sheets("Setup").Range("AJ" & i).Text
    TB_Unit1_1.Value = Sheets("Setup").Range("AQ" & i).Text
    TB_SSN1_1.Value = Sheets("Setup").Range("AT" & i).Text
 
     Next i
End Sub

Uhhh I think this will work? If not I'm either really out of it or misunderstanding what you want to do... Or both.

 

[hallingh]

Haha nevermind yes I am completely out of it. Gotta love the end of work. Hold on a second.

 

[hallingh]

You can try...

Sub Crew1_Change_Loop()

Dim i As Integer
    Sheets("Setup").Calculate
    For i = 9 To 13
 
    Range("TB_Pos1_" & (i-8)).Value = Sheets("Setup").Range("AJ" & i).Text
    Range("TB_Unit1_" & (i-8)).Value = Sheets("Setup").Range("AQ" & i).Text
    Range("TB_SSN1_" & (i-8)).Value = Sheets("Setup").Range("AT" & i).Text
 
     Next i
 
End Sub

But it's really similar to the code you already tried so you will probably get the same error. I'm leaving work now but if no one has helped you by tomorrow I'll take a look.

Hank

 

[jyokom]

Same error.

 

[SydneyGeek]

When that happens, click the Debug button. Which line highlights?

The reason I ask, is that I created a workbook with those range names and tested it. With the error you are getting, either the range names or the 'Setup' sheet's name don't match the code.

Denis

 

[SydneyGeek]

Hmmm.... seems I can't read. Try...

Sub Crew1_Change_Loop()
     Dim i As Integer
     Sheets("Setup").Calculate

     For i = 1 To 5
         Range("TB_Pos1_" & i).Value = Sheets("Setup").Range("AJ8").Offset(i, 0).Text
         Range("TB_Unit1_" & i).Value = Sheets("Setup").Range("AQ8").Offset(i, 0).Text
         Range("TB_SSN1_" & i).Value = Sheets("Setup").Range("AT8").Offset(i, 0).Text
     Next i
 End Sub

Denis

 

[jyokom]

Still get the same error.