kortere code voor..

[sjonnie1974]

onderstaand opgenomen in mijn vba... Kan dit korter?

st1 = Range("settings!g3")
st2 = Range("settings!g4")
st3 = Range("settings!g5")
st4 = Range("settings!g6")
st5 = Range("settings!g7")
st6 = Range("settings!g8")
st7 = Range("settings!g9")
st8 = Range("settings!g10")
st9 = Range("settings!g11")
st10 = Range("settings!g12")
st11 = Range("settings!g13")
st12 = Range("settings!g14")
st13 = Range("settings!g15")
st14 = Range("settings!g16")
st15 = Range("settings!g17")
st16 = Range("settings!g18")
st17 = Range("settings!g19")
st18 = Range("settings!g20")
st19 = Range("settings!g21")
st20 = Range("settings!g22")
st21 = Range("settings!g23")
st22 = Range("settings!g24")
st23 = Range("settings!g25")
st24 = Range("settings!g26")
st25 = Range("settings!g27")
st26 = Range("settings!g28")
st27 = Range("settings!g29")
st28 = Range("settings!g30")
st29 = Range("settings!g31")
st30 = Range("settings!g32")
st31 = Range("settings!g33")
st32 = Range("settings!g34")
st33 = Range("settings!g35")
st34 = Range("settings!g36")
st35 = Range("settings!g37")
st36 = Range("settings!h3")
st37 = Range("settings!h4")
st38 = Range("settings!h5")
st39 = Range("settings!h6")
st40 = Range("settings!h7")
st41 = Range("settings!h8")
st42 = Range("settings!h9")
st43 = Range("settings!h10")
st44 = Range("settings!h11")
st45 = Range("settings!h12")
st46 = Range("settings!h13")
st47 = Range("settings!h14")
st48 = Range("settings!h15")
st49 = Range("settings!h16")
st50 = Range("settings!h17")
st51 = Range("settings!h18")
st52 = Range("settings!h19")
st53 = Range("settings!h20")
st54 = Range("settings!h21")
st55 = Range("settings!h22")
st56 = Range("settings!h23")
st57 = Range("settings!h24")
st58 = Range("settings!h25")
st59 = Range("settings!h26")
st60 = Range("settings!h27")
st61 = Range("settings!h28")
st62 = Range("settings!h29")
st63 = Range("settings!h30")
st64 = Range("settings!h31")
st65 = Range("settings!h32")
st66 = Range("settings!h33")
st67 = Range("settings!h34")
st68 = Range("settings!h35")
st69 = Range("settings!h36")
st70 = Range("settings!h37")
st71 = Range("settings!i3")
st72 = Range("settings!i4")
st73 = Range("settings!i5")
st74 = Range("settings!i6")
st75 = Range("settings!i7")
st76 = Range("settings!i8")
st77 = Range("settings!i9")
st78 = Range("settings!i10")
st79 = Range("settings!i11")
st80 = Range("settings!i12")
st81 = Range("settings!i13")
st82 = Range("settings!i14")
st83 = Range("settings!i15")
st84 = Range("settings!i16")
st85 = Range("settings!i17")
st86 = Range("settings!i18")
st87 = Range("settings!i19")
st88 = Range("settings!i20")
st89 = Range("settings!i21")
st90 = Range("settings!i22")
st91 = Range("settings!i23")
st92 = Range("settings!i24")
st93 = Range("settings!i25")
st94 = Range("settings!i26")
st95 = Range("settings!i27")
st96 = Range("settings!i28")
st97 = Range("settings!i29")
st98 = Range("settings!i30")
st99 = Range("settings!i31")

 

[OctaFish]

Dat kan inderdaad een stuk korter

Option Base 1

Sub testje()
Dim i As Integer
Dim st(99) As Variant
    For i = 1 To 35
        st(i) = Range("settings!G" & i + 2)
        st(i + 35) = Range("settings!H" & i + 2)
        st(i + 70) = Range("settings!H" & i + 2)
    Next i

End Sub

 

[snb]

Zoals:

Sub M_snb()
  sn=sheets("Settings").[G3:I37]
End sub

 

[OctaFish]

Hiermee krijg je niet de nummering die TS in zijn naamgeving gebruikt. Bovendien heb je nu een matrix met meer dimensies. We weten uiteraard niet of TS daarmee uit de voeten kan. In eerste instantie had ik dan ook dit gemaakt, waarbij wèl wordt gekeken naar de afwijkende reeksen:

Dim i As Integer
Dim st(99) As Variant
    For i = 1 To 35
        st(i) = Range("Settings!G" & i + 2)
    Next i
    For i = 36 To 70
        st(i) = Range("Settings!H" & i - 33)
    Next i
    For i = 71 To 99
        st(i) = Range("Settings!I" & i - 68)
    Next i

Al vind ik de uitgebreidere matrix zelf ook mooier .

 

[snb]

dan kan waarschijnlijk ook:

Sub M_snb()
  redim sp(99)

  for each it in sheets("Settings").[G3:I37].specialcells(2)
    sp(j)=it.value
    j=j+1
  next
End sub