[vba] looping array

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
634
Office Version
  1. 2010
Platform
  1. Windows
Hi, I am trying to loop through a dynamic array B2:G and results on M:V and this is what I tryed.
VBA Code:
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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
I'm confused, there is no loop in your code. :confused:
In what way are the result wrong?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You need to explain your objective so we can evaluate the code.
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
634
Office Version
  1. 2010
Platform
  1. Windows
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.
1612470682719.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This will return E for even, O for odd (not prime), P for prime, or D for decimals.
VBA Code:
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

Well-known Member
Joined
Jan 26, 2011
Messages
634
Office Version
  1. 2010
Platform
  1. Windows
Thank you so much for your time Fluff, work perfect.
Sorry about the delay reply to thanks.
 

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
634
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
It will look at everything in B2:K to last row in column B
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,920
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top