[vba] looping array

[montecarlo2012]

Hi, I am trying to loop through a dynamic array B2:G and results on M:V and this is what I tryed.

Sub Monte_carlo()

With Range("M2:M" & Cells(Rows.Count, "B").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-11]="""","""",IF(MOD(RC[-11],12)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("N2:N" & Cells(Rows.Count, "C").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-12]="""","""",IF(MOD(RC[-12],13)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("O2:O" & Cells(Rows.Count, "D").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-13]="""","""",IF(MOD(RC[-13],14)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("P2:P" & Cells(Rows.Count, "E").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-14]="""","""",IF(MOD(RC[-14],15)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("Q2:Q" & Cells(Rows.Count, "F").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-15]="""","""",IF(MOD(RC[-15],16)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("R2:R" & Cells(Rows.Count, "G").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-16]="""","""",IF(MOD(RC[-16],17)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("S2:S" & Cells(Rows.Count, "H").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-17]="""","""",IF(MOD(RC[-17],18)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("T2:T" & Cells(Rows.Count, "I").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-18]="""","""",IF(MOD(RC[-18],19)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("U2:U" & Cells(Rows.Count, "J").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-19]="""","""",IF(MOD(RC[-19],20)=0,""E"",""P""))"
  .Value = .Value
End With
With Range("V2:V" & Cells(Rows.Count, "K").End(xlUp).Row)
  .FormulaR1C1 = "=IF(RC[-20]="""","""",IF(MOD(RC[-20],21)=0,""E"",""P""))"
  .Value = .Value
End With
End Sub

The results I got from here "all wrong"
Please any of you can help me here.

 

[Fluff]

I'm confused, there is no loop in your code.
In what way are the result wrong?

 

[JLGWhiz]

You need to explain your objective so we can evaluate the code.

 

[montecarlo2012]

Hello.
Thank you Fluff and JLGWhiz for reading this post.
I am repeating the statement with....end with, reason why I mentioned the loop, (i don't know how from all the "with's" I am using jump to looping and maybe looks shoter).
the results are wrong because in the images you will see here is obvious the numbers I have in my array are primes and evens, but the result show only P.
so my object is to display "E" when is even, and "P" for primes
Thank you again.

 

[Fluff]

You're code is showing E if it's divisible by 12 (for col M) otherwise it shows P.
What should happen if the number is odd, but not a prime number?

 

[JLGWhiz]

Good point @Fluff, there are many odd numbers that are not prime numbers, and the formulas being used are not adequate to identify which values are prime numbers, since a prime number can only be divided by 1 or itself. Each number would have to be tested if divisible by 2 thru 9 to identify the Primes.

 

[Fluff]

This will return E for even, O for odd (not prime), P for prime, or D for decimals.

Sub montecarlo()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("B2:K" & Range("B" & Rows.Count).End(xlUp))
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   
   For r = 1 To UBound(Ary)
      For c = 1 To UBound(Ary, 2)
         Nary(r, c) = CheckNumber(Ary(r, c))
      Next c
   Next r
   Range("M2").Resize(UBound(Nary), UBound(Nary, 2)).Value = Nary
End Sub
Function CheckNumber(Numb As Variant) As String
   Dim i As Long
   Select Case True
      Case Numb = "": CheckNumber = ""
      Case Numb <> Int(Numb): CheckNumber = "D"
      Case Numb Mod 2 = 0: CheckNumber = "E"
      Case Else
         For i = 3 To Sqr(Numb) Step 2
            If Numb Mod i = 0 Then
               CheckNumber = "O"
               Exit Function
            End If
         Next
         CheckNumber = "P"
   End Select
End Function

 

[montecarlo2012]

Thank you so much for your time Fluff, work perfect.
Sorry about the delay reply to thanks.

 

[montecarlo2012]

Sorry about this, Just in case is possible.
My array is dynamic ("B2:K2850")at the moment,
what line do I have to change in order to walk all the array through
thanks.

 

[Fluff]

It will look at everything in B2:K to last row in column B