help with array macro

[Imran Azam]

Hello people

hope all is well

i have a group of number and i am trying to create a macro that would read these numbers into an array and also calculate the min value of each row and also store this into a new array.

is there anyone that can help me with this?

below is he group of numbers ( the range is e5 to m15)

50​	1​	2​	3​	46​	7​	2​	2​	1​
10​	3​	5​	7​	8​	9​	0​	12​	1​
20​	4​	6​	8​	9​	10​	1​	13​	2​
30​	5​	7​	9​	10​	11​	2​	14​	3​
40​	6​	8​	10​	11​	12​	3​	15​	4​
70​	7​	9​	11​	12​	13​	4​	16​	5​
71​	8​	10​	12​	13​	14​	5​	17​	6​
72​	9​	11​	13​	14​	15​	6​	18​	7​
73​	10​	12​	14​	15​	16​	7​	19​	8​
74​	11​	13​	15​	16​	17​	8​	20​	9​
75​	12​	14​	16​	17​	18​	9​	21​	10​

thank you

 

[DanteAmor]

Array b contains the minimum value of each row
Try this:

Sub test1()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("E5:M15").Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    k = 9 ^ 9
    For j = 1 To UBound(a, 2)
      If a(i, j) < k Then k = a(i, j)
    Next
    b(i, 1) = k
  Next
End Sub

 

[Imran Azam]

Array b contains the minimum value of each row
Try this:

Sub test1()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Range("E5:M15").Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    k = 9 ^ 9
    For j = 1 To UBound(a, 2)
      If a(i, j) < k Then k = a(i, j)
    Next
    b(i, 1) = k
  Next
End Sub

thanks let me try this

 

[Imran Azam]

thanks let

Array b contains the minimum value of each row
Try this:

Sub test1()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
 
  a = Range("E5:M15").Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1)
    k = 9 ^ 9
    For j = 1 To UBound(a, 2)
      If a(i, j) < k Then k = a(i, j)
    Next
    b(i, 1) = k
  Next
End Sub

Hi thanks for this, is there anyway i can add to this code so it can calculate the total of min value ( in array b) and print them on a different cell ( on cell p15)?

 

[DanteAmor]

Hi thanks for this, is there anyway i can add to this code so it can calculate the total of min value ( in array b) and print them on a different cell ( on cell p15)?

You can explain what you need, use your initial example.

 

[Imran Azam]

You can explain what you need, use your initial example.

yes below is what the code needs to do

1 ) Read the group of numbers (the range is e5 to m15) into an array
2 ) Calculate the min value of each row and also store this into a new array.
3) Calculate the max value in the array (created in step2) and print this on cell A1 on the excel sheet
4) Calculate the maximum value of each column ( of the group data in range is e5 to m15) and store this in a new array
5) calculate the min value of the last step (step 4) array and display this n cell A2 on the excel sheet

 

[DanteAmor]

3) Calculate the max value in the array (created in step2) and print this on cell A1 on the excel sheet

What is the purpose of storing in an array?

Based on your example, what is the result you expect in each cell and why?

 

[Imran Azam]

What is the purpose of storing in an array?

Based on your example, what is the result you expect in each cell and why?

the numbers can change so they want them stored in an arrey to ease the over all process and make the manipulation easier.

I would expect cell a1 = 9 N and A2=9

A1 cell - from the numbers provided if you take the min value of each row and calculate the over all max value of this it comes back to 9
A2 cell - from the numbers provided if you take the max value from each column and then from these value take the min value it comes back to 9

 

[DanteAmor]

Try this

Sub test1()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long
  
  a = Range("E5:M15").Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  ReDim c(1 To UBound(a, 2), 1 To 1)
  For i = 1 To UBound(a, 1)
    k = 9 ^ 9
    For j = 1 To UBound(a, 2)
      If a(i, j) < k Then k = a(i, j)
    Next
    b(i, 1) = k
  Next
    
  For j = 1 To UBound(a, 2)
    k = 0
    For i = 1 To UBound(a, 1)
      If a(i, j) > k Then k = a(i, j)
    Next
    c(j, 1) = k
  Next
  
  k = 0
  For i = 1 To UBound(b)
    If b(i, 1) > k Then k = b(i, 1)
  Next
  Range("A1").Value = k

  k = 9 ^ 9
  For i = 1 To UBound(c)
    If c(i, 1) < k Then k = c(i, 1)
  Next
  Range("A2").Value = k

End Sub

 

[Imran Azam]

Try this

Sub test1()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long, m As Long
 
  a = Range("E5:M15").Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  ReDim c(1 To UBound(a, 2), 1 To 1)
  For i = 1 To UBound(a, 1)
    k = 9 ^ 9
    For j = 1 To UBound(a, 2)
      If a(i, j) < k Then k = a(i, j)
    Next
    b(i, 1) = k
  Next
   
  For j = 1 To UBound(a, 2)
    k = 0
    For i = 1 To UBound(a, 1)
      If a(i, j) > k Then k = a(i, j)
    Next
    c(j, 1) = k
  Next
 
  k = 0
  For i = 1 To UBound(b)
    If b(i, 1) > k Then k = b(i, 1)
  Next
  Range("A1").Value = k

  k = 9 ^ 9
  For i = 1 To UBound(c)
    If c(i, 1) < k Then k = c(i, 1)
  Next
  Range("A2").Value = k

End Sub

THANK you so much this works, is it possible you can either comment or explain this step by step i dont quite understand the code