Loop structures

[natalie123vba]

Hi All,

I am looking for a looping structure for the below code, as I need to repeat task for at least seven entries. can you help with a looping structure? thanks

Sub Speedandsensorwarning()

Dim Reading1 As Integer

Dim Reading2 As Integer

Dim Reading3 As Integer

'Create storage location for sensor Reading 1 Trial 1

Dim Reply1 As Variant

Dim Reply2 As Variant

Dim Reply3 As Variant

'Create storage locations for speed reading

Dim Speed1 As Integer

Dim Speed2 As Integer

Dim Speed3 As Integer

'The following are actions for reading1:

Reading1 = InputBox("Please enter the first distance reading from sensor")

'This will record reading 1 - trial 1 from the engineer onto the table

'inputbox will ask a question and store in location reading1 followed by

'storing onto table d21 in my case

Speed1 = InputBox("enter the speed1 values in km/hr")

Range("D21").Value = Reading1

Range("G21").Value = Speed1

Range("J6").Value = Reading1

'copy reading1 to calculation area

Range("E21").Value = Range("J7").Value

'Bring the action back to table - COPY

Range("H21").Value = MinSpeed(Range("G21").Value)

Range("I21").Value = MaxSpeed(Speed1)

Reply1 = InputBox("Is everything ok?")

Range("F21").Value = Reply1

'The following are actions for reading2:



Reading2 = InputBox("Please enter the second distance reading from sensor")

Speed2 = InputBox("enter the speed2 values in km/hr")

Range("D22").Value = Reading2

Range("G22").Value = Speed2

Range("J6").Value = Reading2

Range("E22").Value = Range("J7").Value

Range("H22").Value = MinSpeed(Range("G22").Value)

Range("I22").Value = MaxSpeed(Speed2)

Reply2 = InputBox("Is everything ok?")

Range("F22").Value = Reply2

'The following are actions for reading 3:

Reading3 = InputBox("Please enter the third distance reading from sensor")

Speed3 = InputBox("enter the speed3 values in km/hr")

Range("D23").Value = Reading3

Range("G23").Value = Speed3

Range("J6").Value = Reading3

Range("E23").Value = Range("J7").Value

Range("H23").Value = MinSpeed(Range("G23").Value)

Range("I23").Value = MaxSpeed(Speed3)

Reply3 = InputBox("Is everything ok?")

Range("F23").Value = Reply3

End Sub

 

[Skybluekid]

Try this:

Sub Speedandsensorwarning()

Dim Reading as Integer
Dim Reply as Variant
Dim Speed As Integer
Dim i as Integer, j as Integer, k as Integer

j = 21

For i 1 to 7

Reading = InputBox("Please enter the first distance reading from sensor")

'This will record reading 1 - trial 1 from the engineer onto the table

'inputbox will ask a question and store in location reading1 followed by

'storing onto table d21 in my case

Speed = InputBox("enter the speed values in km/hr")

Range("D"& j).Value = Reading

Range("G"&j).Value = Speed

Range("J6").Value = Reading

'copy readingo calculation area

Range("E"&j).Value = Range("J7").Value

'Bring the action back to table - COPY

Range("H"&j).Value = MinSpeed(Range("G"&j).Value)

Range("I"&j").Value = MaxSpeed(Speed)

Reply = InputBox("Is everything ok?")

Range("F"&j).Value = Reply1

j = j + 1

Next i

 

[natalie123vba]

Thank you. this is coming up with 2 errors:

'For i 1 to 7' and 'Range("i"&j").Value = MaxSpeed(Speed)'

What would you call this looping structure? so i can look into it. trying to teach myself this is impossible haha

 

[natalie123vba]

Thank you. this is coming up with 2 errors:

'For i 1 to 7' and 'Range("i"&j").Value = MaxSpeed(Speed)'

What would you call this looping structure? so i can look into it. trying to teach myself this is impossible haha

wait i fixed the errors thanks

 

[Skybluekid]

This is a For Next Loop.

 

[natalie123vba]

This is a For Next Loop.

Thank you

 

[James006]

Hi,

There is no need for variables j or k

Sub Speedandsensorwarning2()
Dim Reading As Long, Speed As Long, i As Long
Dim Reply As Variant
    ' Loop
    For i = 1 To 7
        Reading = InputBox("Please enter the first distance reading from sensor")
        Speed = InputBox("enter the speed values in km/hr")
        Range("D" & i + 20).Value = Reading
        Range("G" & i + 20).Value = Speed
        Range("J6").Value = Reading
        Range("E" & i + 20).Value = Range("J7").Value
        Range("H" & i + 20).Value = MinSpeed(Range("G" & i + 20).Value)
        Range("I" & i + 20).Value = MaxSpeed(Speed)
        Reply = InputBox("Is Everything OK ???")
        Range("F" & i + 20).Value = Reply
    Next i
End Sub