Finding Max Value with a Loop

Hi all,

I need a VBA code to find first 5 max values in the matrix. The process lasts as follows:

1-Find the max value
2-Write that max value in the second sheet on A1 cell
3-Erase that max value in the matrix
4-Find the max value
5-Write that max value in the second sheet on A2 cell

and go on...

I tried to code but I encounter error messages always.

My code for finding the first max value:

For i=1 to 25
     For j=1 to 25
          If Cells(i,j)<Cells(i,j+1) Then
          TheMax= Cells(i,j+1)
     Next j

     If Cells(i,j+1)<Cells(i+1,j+1) Then
     TheMax = Cells(i+1,j+1)
Next i

Sheet2.Range("A1") = Cells(i+1,j+1)

Thanks for helping

Thanks patel45, it works well but I also have to need erased cell value in that matrix.

any reason why you wonīt use the worksheetfunction Large for that? OK, Large that wonīt delete the Max but for using the function thereīs no need to delete the max.

Place this formula into A1 on the results sheet and adjust the name of the data sheet as well as the range

Yes, you're right about that HaHoBe, it is easier to find and sort the values with a formula. However, I have a complex algorithm actually and this is the first step of my algorithm. Because of this reason, I can not use a formula to reach what I wanted. Thanks anyway

You can examine http://www.excelforum.com/excel-prog...algorithm.html

Hi oxco,

Please check this code,

Option Explicit

Sub CopyDeleteMaxVal()
    Dim maxVal As Double
    Dim rangeToTest As Range
    Dim cell As Range
    Dim i As Integer
    
    Set rangeToTest = Worksheets("Sayfa1").Range("Matrix") 'Select the range and name it to "Matrix"
    maxVal = Worksheets("Sayfa1").Range("A1").Value
    i = 0
    
    For Each cell In rangeToTest
        If cell.Value > maxVal Then
            i = i + 1
            maxVal = cell.Value
            Worksheets("Sayfa2").Range("A" & i).Value = maxVal
            cell.Delete
        End If
    Next cell
End Sub

Thanks for both.

Holger, it works well. I have still need to cell to be erased because sorting is not my goal. Actually, I will choose the max. value according to the results of if function. Thanks again.

Tangangtanga, the code you write does not find the second max value.
Your results as follows;

121561
1891981
5841651
113213213
187630463
198790549
339608409
373368945
391739447
566003605
584688345
792398801
1018793997
1245189193
1471584389
1697979585
1924374781
32123184731

However I want to first 5 max value. The first max. value found and written in the second sheet and then the second max. value should be found after deleting the first max. value. You can observe the attached file.

Thanks,
Oxco

Yes, I try to narrow the problem

Thanks for helping but I could not run the code. Nothing is changed in my workbook.

May be I should tell what I actually need. You can observe in the attached file.

Thanks

Hi Holger,

I meant I could not run the code you post it;

Sub DeleteValues()
Dim wsList As Worksheet
Dim wsData As Worksheet
Dim rngFound As Range
Dim lngCounter As Long

Set wsList = Sheets("Sorted Like That")
Set wsData = Sheets("View for Sheet-1")

For lngCounter = 1 To 5
  Set rngFound = wsData.UsedRange.Find(what:=wsList.Cells(lngCounter, "A").Value)
  If Not rngFound Is Nothing Then
    rngFound.ClearContents
  End If
Next lngCounter

Set wsData = Nothing
Set wsList = Nothing
End Sub

I have no experience and knowledge about any coding language. The example file is a small version of my project but I can not progress. And that's the situation.

Thanks for your interest.

Thanks, Holger.

Now, I must use your code to find the max. value according to the result of "if" object.

Thanks, Holger.

Now, I must use your code to find the max. value according to the result of "if" object.