Choose two numbers

[Tony]

Please try this - choose two numbers and write them

I have two same size number arrays (numbers between -10 and +10), two
columns apart, one in (B3:K22) and the other in (N3:W22). The following
code finds the (i) largest number in the first row of the first array
and writes it in another column (Z3), and (ii) finds the number in the
corresponding column of the second array and writes that in the next
column (AA3). By writing these two numbers from each row of the arrays
to the two new columns, I end up with two columns of selected numbers
that have same number of rows as the arrays.

Try it with two arrays of random numbers between -10 and +10, to fill
the arrays you can use =ROUND(10.5*(RAND()-RAND()),0)
------------------------------------
Sub FindMaxInfo()
Dim lastrow As Long
Dim lastCol As Long
Dim c1 As Integer
Dim row1 As Integer
Dim mx As Integer
Dim mxCol As Integer
Dim maxRow As Integer

Dim colcnt As Integer


row1 = 3
maxRow = 22
While row1 <= maxRow
c1 = 2 ' starting column of first batch on left
colcnt = 12 ' end column of first batch on left
mx = -33555 ' set some max value that is not in list
While c1 < colcnt
lastCol = ActiveSheet.Cells(Columns.Count, c1).End(xlUp).Column
'MsgBox (ActiveSheet.Cells(row1, c1))
If (ActiveSheet.Cells(row1, c1)) > mx Then
mx = (ActiveSheet.Cells(row1, c1))
mxCol = c1
End If
'
'
c1 = c1 + 1

Wend
Cells(row1, 27) = Cells(row1, ((mxCol - 1) + 13))
'
'

row1 = row1 + 1
Wend
End Sub
---------------------------------------------

Now what I want to do is the same for (ii) but for (i) I want to find,
not the largest number in each row of the first array, but I want to
find the number that is most different from the average of the row
(the maximum deviation from the average of the row)

Can you adjust to code to do this?

Eventually I will want to use this code with two same size arrays that
have more columns and rows than in this test case, so the code should
note which parameters to change for this.


Thank you for helping.

Tony.

 

[Guest]

The deviation for a particular row would be given by:
deviation = Abs(ActiveSheet.Cells(row1, c1) -
WorksheetFunction.Average(Range(ActiveSheet.Cells(Row1,c1),
ActiveSheet.Cells(row1, c1+9))))

So use this formula in your code to calculate mx:
If (ActiveSheet.Cells(row1, c1)) > mx Then
mx = Abs(ActiveSheet.Cells(row1, c1) -
WorksheetFunction.Average(Range(ActiveSheet.Cells(Row1,c1),
ActiveSheet.Cells(row1, c1+9))))
mxCol = c1
End If

Start by setting mx = 0 (no deviation) to ensure you find the maximum, but
other than that the rest of your code should be able to be the same.

 

[Tony]

Thank's K Dales for your super fast response.

I can't wait to try out your code tonight as soon as I get back from
work.
Thanks again - Tony

 

[Tony]

I ran the new code below. Why did i get a "compile error: syntax
error" on line
mx = Abs(ActiveSheet.Cells(row1, c1)-
------------------------------------
Sub FindMaxInfo()
Dim lastrow As Long
Dim lastCol As Long
Dim c1 As Integer
Dim row1 As Integer
Dim mx As Integer
Dim mxCol As Integer
Dim maxRow As Integer

Dim colcnt As Integer


row1 = 3
maxRow = 22
While row1 <= maxRow
c1 = 2
colcnt = 12
mx = -33 ' set some minimum value that is not in list
mxCol = c1
While c1 < colcnt
lastCol = ActiveSheet.Cells(Columns.Count, c1).End(xlUp).Column
'MsgBox (ActiveSheet.Cells(row1, c1))
If (ActiveSheet.Cells(row1, c1)) > mx Then
mx = Abs(ActiveSheet.Cells(row1, c1)-
WorksheetFunction.Average(Range(ActiveSheet.Cells(Row1,c1),
ActiveSheet.Cells(row1, c1+9))))
mxCol = c1
End If
'
'
c1 = c1 + 1

Wend
Cells(row1, 27) = Cells(row1, ((mxCol - 1) + 13))
'
'

row1 = row1 + 1
Wend
End Sub

 

[Rowan Drummond]

The line has been wrapped by your newsreader. Try:

'----------------------------------------
Sub FindMaxInfo()
Dim lastrow As Long
Dim lastCol As Long
Dim c1 As Integer
Dim row1 As Integer
Dim mx As Integer
Dim mxCol As Integer
Dim maxRow As Integer

Dim colcnt As Integer

row1 = 3
maxRow = 22
While row1 <= maxRow
c1 = 2
colcnt = 12
mx = -33 ' set some minimum value that is not in list
mxCol = c1
While c1 < colcnt
lastCol = ActiveSheet.Cells(Columns.Count, c1).End(xlUp).Column
'MsgBox (ActiveSheet.Cells(row1, c1))
If (ActiveSheet.Cells(row1, c1)) > mx Then
mx = Abs(ActiveSheet.Cells(row1, c1) - _
WorksheetFunction.Average(Range _
(ActiveSheet.Cells(row1, c1), _
ActiveSheet.Cells(row1, c1 + 9))))
mxCol = c1
End If
'
'
c1 = c1 + 1

Wend
Cells(row1, 27) = Cells(row1, ((mxCol - 1) + 13))
'
'
row1 = row1 + 1
Wend
End Sub
'-----------------------------------------------------

Hope this helps
Rowan

 

[Tony]

Yes - Thank you Rowan. That was it. Now it works

Best

Tony