montecarlo2012
Well-known Member
- Joined
- Jan 26, 2011
- Messages
- 984
- Office Version
- 2010
- Platform
- Windows
Hi.
This code count how many cells the same value jump
The start point to count is “U2”
So the first value to start the count is at Column P2
have the value 2 so the code count from “U2” to the end
all this table change daily (dynamic array).
My little code help me with one line at the time.
I have been trying to figure out how to plug variable that change
In all this changes, but not clue.
I repeat my code already four times in order to illustrate more or less
What I am looking for, and to indicate what parts are changing as well
The size is 36 rows by any number of columns
When I rung the first code this is what I got
The value on P2 is 2 so if you start to count from U2, the number two is two spaces apart, and after 6 and so on, the same with the rest of the P values.
this four results is because I just copy and paste the same code four time, and change the necesary part for this task.
so what change here:
Set rngData = Sheet2.Range("U2:HH2")
Set rngData = Sheet2.Range("U3 : HH3")
etc.
and also
If cell = 2 Then '////////////////// ("P2") the value is on P2 ...
If cell = 1 Then '//////////////////("P3")
If cell = 2 Then '//////////////////("P4")
and so on
I tried to create a loop for this but do not work, of course, I did wrong.
I couldn't figure out the loop for this or make it shorter.
Thanks for reading this.
VBA Code:
Sub AA()
'this code count the interval between the same values
Set rngData = Sheet2.Range("U2:HH2")
For Each cell In rngData
If cell = 2 Then '////////////////// ("P2")
Sheet7.Range("c2").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
End Sub
The start point to count is “U2”
So the first value to start the count is at Column P2
have the value 2 so the code count from “U2” to the end
all this table change daily (dynamic array).
My little code help me with one line at the time.
I have been trying to figure out how to plug variable that change
In all this changes, but not clue.
I repeat my code already four times in order to illustrate more or less
What I am looking for, and to indicate what parts are changing as well
The size is 36 rows by any number of columns
When I rung the first code this is what I got
The value on P2 is 2 so if you start to count from U2, the number two is two spaces apart, and after 6 and so on, the same with the rest of the P values.
this four results is because I just copy and paste the same code four time, and change the necesary part for this task.
VBA Code:
Sub AA()
'this code count the interval between the same values
Set rngData = Sheet2.Range("U2:HH2")
For Each cell In rngData
If cell = 2 Then '////////////////// ("P2")
Sheet7.Range("c2").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
End Sub
Sub AB()
Set rngData = Sheet2.Range("U3 : HH3")
For Each cell In rngData
If cell = 1 Then '//////////////////("P3")
Sheet7.Range("c3").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
End Sub
Sub AC()
Set rngData = Sheet2.Range("U4 : HH4")
For Each cell In rngData
If cell = 2 Then '//////////////////("P4")
Sheet7.Range("c4").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
End Sub
Sub AD()
Set rngData = Sheet2.Range("U5 : HH5")
For Each cell In rngData
If cell = 1 Then '//////////////////("P5")
Sheet7.Range("c5").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
End Sub
so what change here:
Set rngData = Sheet2.Range("U2:HH2")
Set rngData = Sheet2.Range("U3 : HH3")
etc.
and also
If cell = 2 Then '////////////////// ("P2") the value is on P2 ...
If cell = 1 Then '//////////////////("P3")
If cell = 2 Then '//////////////////("P4")
and so on
I tried to create a loop for this but do not work, of course, I did wrong.
I couldn't figure out the loop for this or make it shorter.
Thanks for reading this.