montecarlo2012
Wellknown Member
 Joined
 Jan 26, 2011
 Messages
 857
 Office Version

 2010
 Platform

 Windows
Hi All.
Working on:
I am looking to shorter the code, and manage the condition from cell A1
Like if cell = range(“A1”).value then.
I tried but do not work.
Do you think it is possible to resolved, without Scripting.Dictionary Please.
On sheet 1 I have a dynamic array B2:G.
looks like this:
Thank you for reading this
I am eager to receive your feedback Please.
Working on:
VBA Code:
Sub sheet3_val()
Set rngData = Sheet1.Range("B2", "B2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D2").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B9").Formula = "=COUNT(C2:WAK2)"
Range("C9").Formula = "=max(C2:WAK2)"
Range("D9").Formula = "=COUNTIF(C2:WAK2, 0)"
Set rngData = Sheet1.Range("C2", "C2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D3").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B10").Formula = "=COUNT(C3:WAK3)"
Range("C10").Formula = "=max(C3:WAK3)"
Range("D10").Formula = "=COUNTIF(C3:WAK3, 0)"
Set rngData = Sheet1.Range("D2", "D2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D4").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B11").Formula = "=COUNT(C4:WAK4)"
Range("C11").Formula = "=max(C4:WAK4)"
Range("D11").Formula = "=COUNTIF(C4:WAK4, 0)"
Set rngData = Sheet1.Range("E2", "E2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D5").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B12").Formula = "=COUNT(C5:WAK5)"
Range("C12").Formula = "=max(C5:WAK5)"
Range("D12").Formula = "=COUNTIF(C5:WAK5, 0)"
Set rngData = Sheet1.Range("F2", "F2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D6").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B13").Formula = "=COUNT(C6:WAK6)"
Range("C13").Formula = "=max(C6:WAK6)"
Range("D13").Formula = "=COUNTIF(C6:WAK6, 0)"
Set rngData = Sheet1.Range("G2", "G2902")
m = 1
For Each cell In rngData
If cell = 25 Then
Sheet3.Range("D7").Offset(0, m) = n
n = 0
m = m + 1
Else
n = n + 1
End If
Next
Range("B14").Formula = "=COUNT(C7:WAK7)"
Range("C14").Formula = "=max(C7:WAK7)"
Range("D14").Formula = "=COUNTIF(C7:WAK7, 0)"
Range("B15").Formula = "=sum(B9:B13)"
End Sub
Like if cell = range(“A1”).value then.
I tried but do not work.
Do you think it is possible to resolved, without Scripting.Dictionary Please.
On sheet 1 I have a dynamic array B2:G.
looks like this:
Thank you for reading this
I am eager to receive your feedback Please.