Variable in Loop

[Jenst]

Hi Guys,

I have a counter loop starting with
For loopcounter = 1 To 8
...
next loopcounter

Now I want to appoint the result of a calculation to a variable in this loop. The problem is, that the variable will get overwritten if it is the same in every loop. The variable must change with every loop. How can I combine the Loopcountervalue with the name of a variable??
Thanks!!

[mana]

Option Explicit

Sub test()
Dim loopcounter As Long
Dim myValue() As Long

For loopcounter = 1 To 8
ReDim Preserve myValue(1 To loopcounter)
myValue(loopcounter) = loopcounter * 10
Next loopcounter

For loopcounter = 1 To 8
Debug.Print myValue(loopcounter)
Next loopcounter

End Sub

[Jenst]

Thats great thanks :content:, it's a simple way to do it! Based on this information I was able to produce the below code:

Sub test()
Dim loopcounter As Long
Dim myValue() As Long
Modulesc = InputBox("how many modules are there")
c = Range("A2", Range("A2").End(xlDown)).Count
For loopcounter = 1 To Modulesc
ReDim Preserve myValue(1 To loopcounter)
myValue(loopcounter) = WorksheetFunction.Subtotal(101, Range(Cells(2, loopcounter), Cells(c + 1, loopcounter)))
Next loopcounter
Range("G10").Select
For loopcounter = 1 To 4
ActiveCell.Value = myValue(loopcounter)
ActiveCell = ActiveCell.Offset(1, 0).Select
Next loopcounter
End Sub

All is working fine excluding one detail. When the code is ready in column G under the values of myvalue(loopcounter), it shows True in the last column- any idea why??

Thanks so much! Great support!

[mana]

Option Explicit


Sub test2()
Dim Modulesc
Dim loopcounter As Long
Dim myValue() As Double
Dim r As Range
Modulesc = Application.InputBox("how many modules are there", Type:=1)
If VarType(Modulesc) = vbBoolean Then Exit Sub
ReDim myValue(1 To Modulesc)
Set r = Range("A2", Range("A2").End(xlDown))
For loopcounter = 1 To Modulesc
myValue(loopcounter) = WorksheetFunction.Subtotal(101, r.Offset(, loopcounter))
Next loopcounter
For loopcounter = 1 To Modulesc
r(1).Offset(loopcounter, Modulesc + 2).Value = myValue(loopcounter)
Next loopcounter
End Sub

Sub test3()
Dim r As Range, c As Range
Dim myValue() As Double
Dim n As Long
Set r = Range("A1").CurrentRegion
Set r = Intersect(r, r.Offset(1, 1))
ReDim myValue(1 To r.Columns.Count)
For Each c In r.Columns
n = n + 1
myValue(n) = WorksheetFunction.Subtotal(101, c)
Next
r.Offset(, n + 1).Resize(UBound(myValue), 1).Value = _
WorksheetFunction.Transpose(myValue)
End Sub

[Jenst]

Thanks for the the new impulses! I started to implement some of them and ended up with a problem I can't explain. After the looping is ready, I insert some of the results and only get rounded numbers without digits behind the comma, why??

Sorry, it's really confusion at the moment :dunno:banghead:


Sub report()
Dim loopcountermk As Long
Dim modulk() As Long
Dim Modulend As Long
Num = Range("y3", Range("y3").End(xlDown)).Count
Modulstak = 27
Modulnum = 8
Modulend = Modulstak + Modulnum
ReDim modulk(27 To Modulend)
For loopcountermk = Modulstak To Modulend - 1
Range(Cells(3, loopcountermk), Cells(Num + 2, loopcountermk)).Select
modulk(loopcountermk) = WorksheetFunction.Subtotal(101, Range(Cells(3, loopcountermk), Cells(Num + 2, loopcountermk)))
Next loopcountermk
Range("A22").Value = modulk(27)
Range("A23").Value = modulk(28)
Range("A24").Value = modulk(29)
Range("A25").Value = modulk(30)
Range("A26").Value = modulk(31)
Range("A27").Value = modulk(32)
End Sub

[mana]

only get rounded numbers without digits behind the comma, why??

Dim modulk() As Double

[Jenst]

It's working! Thanks a lot mana, i appreciate your help so much!! :hi::friends: