2
\$\begingroup\$

Good afternoon,

I have a set of working code, which works as a loop between values i = 0-90 step 0.25. It gives 360 combinations exactly.

I used to do this loop separately column by column. It takes time and I must repeat the process exactly 9 times.

enter image description here

So, red bounded is the column first, which I am currently working on. The blue bounds apply to different columns. The value comes from a completely different worksheet. The output is stored in a complete worksheet too (as below). enter image description here

My working codes look as follows:

 Sub calc4()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("b1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i * (-1)
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("B371").Value
    End With
Next I
End With
End Sub

Sub calc3()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
'.Range("a1").Value = "Input"
.Range("c1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("D371").Value
    End With
Next I
End With
End Sub


Sub calc2()
Dim i As Single
  With Worksheets("9")
   .UsedRange.clear
  ' .Range("a1").Value = "Input"
.Range("d1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("F371").Value
    End With
  Next I
 End With
End Sub


 Sub calc1()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("e1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("H371").Value
    End With
Next I
End With
End Sub

Sub calc085()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("f1").Value = "Output"
For i = 0 To 490 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("I371").Value
    End With
  Next I
End With
End Sub

 Sub calc06()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("g1").Value = "Output"
For i = 0 To 45 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("L371").Value
    End With
   Next I
End With
End Sub

Sub calc17()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("h1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("N371").Value
    End With
Next I
End With
End Sub

Sub calc276a()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("i1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("P371").Value
    End With
Next I
End With
End Sub

Sub calc38a()
Dim i As Single
With Worksheets("9")
.UsedRange.clear
.Range("a1").Value = "Input"
.Range("j1").Value = "Output"
For i = 0 To 90 Step 0.25
    Worksheets("1_GENERAL").Range("A2").Value = i
    With .Cells(Rows.Count, 1).End(xlUp)
    .Offset(1, 0).Value = i
    .Offset(1, 1).Value = Worksheets("4.MINUTES").Range("R371").Value
    End With
Next I
End With
End Sub

basically it's every 2 columns as you can see. The exception is column H and I, next to each other. In this event even split this loop into 2 separate will help me a lot. Is it possible?

\$\endgroup\$
4
  • \$\begingroup\$ You will want to iterate rows 1-90 (0-90 is 91 iterations) once, regardless of how many columns you have to work with. The macro is a sheet traversal; coding it as such would slash its execution time big time - or do you need 9 separate outputs? If not, then why does every output procedure begin with clearing the used range of the output sheet? \$\endgroup\$ May 22, 2020 at 15:40
  • \$\begingroup\$ Also you're working out where to write next at each iteration; tracking which row you're at instead, would eliminate that worksheet operation and speed things up too. \$\endgroup\$ May 22, 2020 at 15:44
  • \$\begingroup\$ Your code is not matching up with your output worksheet "9". In each of your calc routines, you are simply assigning the loop variable i to column A and the *exact same value from Worksheets("4.MINUTES").Range(x371).Value. How is the value is column B changing? Plus, as you're looping, you are updating the loop value to a single cell on "1_GENERAL" worksheet. It's unclear how your VBA logic works to produce the output shown. \$\endgroup\$
    – PeterT
    May 22, 2020 at 21:44
  • \$\begingroup\$ OK let me do the explanation: I am doing these calculations for 360 records exactly. They refer to latitude 0-90 divided by 4, hence step 0.25. The step value comes from the 1_GENERAL sheet, which links values in 4.MINUTES. This is why the loop is going this way. As the value in 1_GENERAL changes, all the calculations change in 4.MINUTES sheet bringing the different summary calculated in .Range(x371).Value. Because each loop is related to a separate columns in 4.MINUTES (B371, D371, F371, etc), I need 9 separate outputs in Sheet "9" columns B-J. \$\endgroup\$
    – Geographos
    May 24, 2020 at 20:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.