View Full Version : Variable in Loop
Jenst
09-16-2016, 03:26 PM
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!!
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
09-17-2016, 02:07 AM
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!
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
09-17-2016, 04:42 AM
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
only get rounded numbers without digits behind the comma, why??
Dim modulk() As Double
Jenst
09-17-2016, 05:54 AM
It's working! Thanks a lot mana, i appreciate your help so much!! :hi::friends:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.