2
\$\begingroup\$

Good morning,

I've made the loop for the range, which is copied from one sheet to another. However I have no solution for trimming my code down, which looks like this:

 Sub sunrise()
  Dim i As Single
  With Worksheets("7")
 .UsedRange.Clear
 .Range("a1").Value = "Latitude"
 .Range("b1").Value = "-18"
 .Range("c1").Value = "-17"
 .Range("d1").Value = "-16"
 .Range("e1").Value = "-15"
 .Range("f1").Value = "-14"
 .Range("g1").Value = "-13"
 .Range("h1").Value = "-12"
 .Range("i1").Value = "-11"
 .Range("j1").Value = "-10"
 .Range("k1").Value = "-9"
 .Range("l1").Value = "-8"
 .Range("m1").Value = "-7"
 .Range("n1").Value = "-6"
 .Range("o1").Value = "-5"
 .Range("p1").Value = "-4"
 .Range("q1").Value = "-3"
 .Range("r1").Value = "-2"
 .Range("s1").Value = "-1"
 .Range("t1").Value = "-0.85"
 .Range("u1").Value = "0.6"
 .Range("v1").Value = "1.7"
 .Range("w1").Value = "2.76"
 .Range("x1").Value = "3.84"

  For i = 0 To 10 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("1. GENERAL").Range("E742").Value
    .Offset(1, 2).Value = Worksheets("1. GENERAL").Range("F742").Value
    .Offset(1, 3).Value = Worksheets("1. GENERAL").Range("G742").Value
    .Offset(1, 4).Value = Worksheets("1. GENERAL").Range("H742").Value
    .Offset(1, 5).Value = Worksheets("1. GENERAL").Range("I742").Value
    .Offset(1, 6).Value = Worksheets("1. GENERAL").Range("J742").Value
    .Offset(1, 7).Value = Worksheets("1. GENERAL").Range("K742").Value
    .Offset(1, 8).Value = Worksheets("1. GENERAL").Range("L742").Value
    .Offset(1, 9).Value = Worksheets("1. GENERAL").Range("M742").Value
    .Offset(1, 10).Value = Worksheets("1. GENERAL").Range("N742").Value
    .Offset(1, 11).Value = Worksheets("1. GENERAL").Range("O742").Value
    .Offset(1, 12).Value = Worksheets("1. GENERAL").Range("P742").Value
    .Offset(1, 13).Value = Worksheets("1. GENERAL").Range("Q742").Value
    .Offset(1, 14).Value = Worksheets("1. GENERAL").Range("R742").Value
    .Offset(1, 15).Value = Worksheets("1. GENERAL").Range("S742").Value
    .Offset(1, 16).Value = Worksheets("1. GENERAL").Range("T742").Value
    .Offset(1, 17).Value = Worksheets("1. GENERAL").Range("U742").Value
    .Offset(1, 18).Value = Worksheets("1. GENERAL").Range("V742").Value
    .Offset(1, 19).Value = Worksheets("1. GENERAL").Range("W742").Value
    .Offset(1, 20).Value = Worksheets("1. GENERAL").Range("X742").Value
    .Offset(1, 21).Value = Worksheets("1. GENERAL").Range("Y742").Value
    .Offset(1, 22).Value = Worksheets("1. GENERAL").Range("Z742").Value
    .Offset(1, 23).Value = Worksheets("1. GENERAL").Range("AA742").Value
    End With
   Next I
  End With
  End Sub

I would rather populate this code as a range. I know, that it could be sth like this;

     .Offset(1, 1).Value = Worksheets("1. GENERAL").Range("E742:AA742").Value

Regrettably, only the value from E742 is copied to the B column. Is there a way to expand the range of my offset? enter image description here

\$\endgroup\$
2
  • 1
    \$\begingroup\$ The sample code crashes. For i = 0 To 10 Step 0.25 causes an infinite loop. For loops increment integers by whole numbers. 0.25 gets rounded to 0, So the For incremental step is 0. \$\endgroup\$
    – TinMan
    Jul 10, 2020 at 17:13
  • 1
    \$\begingroup\$ You can assign all the values for a row using arrays. I would post an answer with the refactored code but a prerequisite of CR is that the OP's code is working. \$\endgroup\$
    – TinMan
    Jul 10, 2020 at 17:19

0

Browse other questions tagged or ask your own question.