filter a matrix

[Marc Baumann]

hello,

i have created the following macro to filter a matrix based on a
certain level. in the original matrix the values are between 1 and 5 in
the different cells. the macro is filtering these values and writes in
a new matrix with the same size values for the cells that are either 1
(if the brother cell's value in the original matrix is >= the filter
value) or 0. with the below macro code this takes ~25 minutes for a
158x158 matrix on my laptop. any idea how i can simplify the code to
have it faster done?

thanks,
marc baumann

Sub filtermatrixcom()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Dim PartNum As Integer
Dim FilterNum As Integer
PartNum = Worksheets("Participants").Range("B2").Value
FilterNum = Worksheets("Control").Range("C14").Value
StartRow = 2
StartCol = 2
EndRow = PartNum + 1
EndCol = PartNum + 1
Worksheets("Matrix ComF").Range("B2:IV256").Delete
For RowNdx = StartRow To EndRow
For ColNdx = StartCol To EndCol
If Worksheets("Matrix Com").Cells(RowNdx, ColNdx).Value >=
FilterNum Then
CellValue = 1
Else
CellValue = 0
End If
Worksheets("Matrix ComF").Cells(RowNdx, ColNdx).Value =
CellValue
Next ColNdx
Next RowNdx

End Sub

 

[NickHK]

Marc,
Does this work, if you replace your 2 For/Nexts with this :

With Worksheets("Matrix ComF")
.Range("B2:IV256").ClearContents
.Range(.Cells(StartRow, StartCol), .Cells(EndRow, EndCol)).FormulaR1C1 =
"=IF('Matrix Com'!RC>" & FilterNum & ",1,0)"
End With

NickHK

 

[Marc Baumann]

Nick,

that works exactly how I want it. Thanks a lot!

Is it possible to do this also for this where I make a calculation in
the matrix with cell XY and cell YX:
For RowNdx = StartRow To EndRow
For ColNdx = StartCol To EndCol
Worksheets("Matrix Com").Cells(RowNdx, ColNdx).Value = CalA
Worksheets("Matrix Com").Cells(ColNdx, RowNdx).Value = CalB
TotVar = CalA * CalB
AvVar = TotVar / 2
If AvVar >= FilterNum Then
CellValue = 1
Else
CellValue = 0
End If
Worksheets("Matrix ComC").Cells(RowNdx, ColNdx).Value =
CellValue
Next ColNdx
Next RowNdx

Thanks,
Marc

 

[NickHK]

Marc,
Possibly, but you have to switch the row and column value to get the calB
value.
Let me think on it.

NickHK

 

[NickHK]

Marc,
Just noticed that my formula used "=", whereas yours used ">=" , so just
edit that bit .

NickHK