looping (warning - lots of boring repetitive code)

[cauge]

I can't find the information I am looking for on loops.

I have the following code and still need to add a couple of more loops.
I have a cell (S1) with the number of loops needed figured out by a calculation.

Sub Surface()

Range("A27").Select

varInput = InputBox("Enter the date analyzed.")
Sheets("Information").Range("C65").Value = varInput

varInput = InputBox("At what depth did this section of the well end at?")
Sheets("Information").Range("E65").Value = varInput

varInput = InputBox("Enter the mud type (either Gel Chem or Floc Water).")
Sheets("Information").Range("F65").Value = varInput

varInput = InputBox("Enter the volume of mud landsprayed.")
Sheets("Information").Range("H65").Value = varInput

varInput = InputBox("Enter the specific gravity (1.0-1.5).")
Sheets("Information").Range("I65").Value = varInput

varInput = InputBox("Enter pH.")
Sheets("Information").Range("J65").Value = varInput

varInput = InputBox("Enter EC.")
Sheets("Information").Range("K65").Value = varInput

varInput = InputBox("Enter Na.")
Sheets("Information").Range("L65").Value = varInput

varInput = InputBox("Enter Ca.")
Sheets("Information").Range("M65").Value = varInput

varInput = InputBox("Enter TH.")
Sheets("Information").Range("N65").Value = varInput

varInput = InputBox("Enter Cl.")
Sheets("Information").Range("O65").Value = varInput

varInput = InputBox("Enter NO3.")
Sheets("Information").Range("P65").Value = varInput


Range("A27").Select

End Sub
Sub Fasthole()

Range("A27").Select

varInput = InputBox("Enter the date analyzed.")
Sheets("Information").Range("C66").Value = varInput

varInput = InputBox("At what depth did this section of the well end at?")
Sheets("Information").Range("E66").Value = varInput

varInput = InputBox("Enter the mud type (either Gel Chem or Floc Water).")
Sheets("Information").Range("F66").Value = varInput

varInput = InputBox("Enter the volume of mud landsprayed.")
Sheets("Information").Range("H66").Value = varInput

varInput = InputBox("Enter the specific gravity (1.0-1.5).")
Sheets("Information").Range("I66").Value = varInput

varInput = InputBox("Enter pH.")
Sheets("Information").Range("J66").Value = varInput

varInput = InputBox("Enter EC.")
Sheets("Information").Range("K66").Value = varInput

varInput = InputBox("Enter Na.")
Sheets("Information").Range("L66").Value = varInput

varInput = InputBox("Enter Ca.")
Sheets("Information").Range("M66").Value = varInput

varInput = InputBox("Enter TH.")
Sheets("Information").Range("N66").Value = varInput

varInput = InputBox("Enter Cl.")
Sheets("Information").Range("O66").Value = varInput

varInput = InputBox("Enter NO3.")
Sheets("Information").Range("P66").Value = varInput


Range("A27").Select

End Sub
Sub Intermediate()

Range("A27").Select

varInput = InputBox("Enter the date analyzed.")
Sheets("Information").Range("C67").Value = varInput

varInput = InputBox("At what depth did this section of the well end at?")
Sheets("Information").Range("E67").Value = varInput

varInput = InputBox("Enter the mud type (either Gel Chem or Floc Water).")
Sheets("Information").Range("F67").Value = varInput

varInput = InputBox("Enter the volume of mud landsprayed.")
Sheets("Information").Range("H67").Value = varInput

varInput = InputBox("Enter the specific gravity (1.0-1.5).")
Sheets("Information").Range("I67").Value = varInput

varInput = InputBox("Enter pH.")
Sheets("Information").Range("J67").Value = varInput

varInput = InputBox("Enter EC.")
Sheets("Information").Range("K67").Value = varInput

varInput = InputBox("Enter Na.")
Sheets("Information").Range("L67").Value = varInput

varInput = InputBox("Enter Ca.")
Sheets("Information").Range("M67").Value = varInput

varInput = InputBox("Enter TH.")
Sheets("Information").Range("N67").Value = varInput

varInput = InputBox("Enter Cl.")
Sheets("Information").Range("O67").Value = varInput

varInput = InputBox("Enter NO3.")
Sheets("Information").Range("P67").Value = varInput


Range("A27").Select

End Sub

I think you get the point. Any input appreciated.

I changed it to

Sub Surface()
Dim ar, ar1 As Variant
ar = Array("Enter the date analyzed.", "At what depth was surface set?", "Enter the mud type (either Gel Chem or Floc Water).", "Enter the volume of surface mud landsprayed.", "Enter the specific gravity (1.0-1.5).", "Enter pH.", "Enter EC.", "Enter Na.", "Enter Ca.", "Enter TH.", "Enter Cl.", "Enter NO3.")
ar1 = Array("c65", "e65", "f65", "h65", "i65", "j65", "k65", "l65", "m65", "n65", "o65", "p65")
Range("A27").Select
For i = 0 To UBound(ar)
varInput = InputBox(ar(i))
Sheets("Information").Range(ar1(i)).Value = varInput
Next
End Sub
Sub Fasthole()

Dim ar, ar1 As Variant
ar = Array("Enter the date analyzed.", "At what depth was mud up?", "Enter the mud type (either Gel Chem or Floc Water).", "Enter the volume of fasthole mud landsprayed.", "Enter the specific gravity (1.0-1.5).", "Enter pH.", "Enter EC.", "Enter Na.", "Enter Ca.", "Enter TH.", "Enter Cl.", "Enter NO3.")
ar1 = Array("c66", "e66", "f66", "h66", "i66", "j66", "k66", "l66", "m66", "n66", "o66", "p66")
Range("A27").Select
For i = 0 To UBound(ar)
varInput = InputBox(ar(i))
Sheets("Information").Range(ar1(i)).Value = varInput
Next
End Sub

Works fine, but still wondering if its possible to have a loop as I have a similar situation with soils but don't know how many there are. The number of soils is calculated in a cell that can be referenced in the loop.


Thanks
Wendy

 

[Tazguy37]

Instead of using (1 example):

varInput = InputBox("Enter NO3.") 
Sheets("Information").Range("P67").Value = varInput

why not:

Sheets("Information").Range("P67").Value = InputBox("Enter NO3.")

If you need the value later, you can get it off the sheet, no?

 

[cauge]

That would be shorter, thanks, but I did find a short way of doing it in the second half of the original post.

However the real question was I was wondering if its possible to have a loop as I have a similar situation with soils but don't know how many there are. The number of soils is calculated in a cell that can be referenced in the loop.

 

[tactps]

A simple loop you can adapt may be as follows:

dim counter as integer
counter = 0
do until counter >= [a1].value 'assumes A1 contains the number of times the loop is required
'Your code here
counter = counter + 1
loop

Does that help?

 

[cauge]

My code involves input boxes that puts the input in a table.
how would you tell the macro when it does the next loop that the input in the table would be in the next row and so on.

the code is

Sub SoilA()

Dim ar, ar1 As Variant
ar = Array("Enter the date analyzed.", "At what depth was this sample taken?", "Enter the specific gravity (1.0-1.5).", "Enter pH.", "Enter EC.", "Enter Na.", "Enter Ca.", "Enter TH.", "Enter Cl.", "Enter NO3.")
ar1 = Array("c71", "e71", "i71", "j71", "k71", "l71", "m71", "n71", "o71", "p71")
Range("A27").Select
For i = 0 To UBound(ar)
varInput = InputBox(ar(i))
Sheets("Information").Range(ar1(i)).Value = varInput
Next
End Sub

so now there would be as many soils as are in that cell and the next loop would be the same input boxes just need to have the data moved over to c72, e72, i72 etc (next row).

Wendy

 

[acw]

Wendy

If you know the number of iterations (as you do), then you can have a base row (say 70), put that to a variable and have it increment for each new row of output. You would have to modify the array of output cells to only contain the column and the line to something like:

Sheets("Information").Range(ar1(i) & rowvar + j).Value = varInput

where rowvar contains the base row (70) and j is the loop counter (from the cell).


Tony