Calculate max value based on another columns and write it down with concatenation-CodePudding

I need to write in VBA a code describe as follows

Looking at the picture or table, first I want to check the value contained in cell(Ax), where x is the next empty row (x=8 in my example). If the value of this cell(A8) is equal to "M" I want to find the max value in column(B) that contains the highest value and write down in cell(B8) the following next value: in this case "8". Then write in Column(C) the value "M8". If instead is equal to "F" the value in cell(B8) would be "10" and in cell(C8) I would like to get "F10).

P.S. The value "M" or "F" in the new rows comes from a Textbox as it possible to see in the following code.

Here the code I wrote so far:

Private Sub CommandButton2_Click()
Dim Lastrow As Long 
Lastrow = Sheets("Database finale").Range("A1").End(xlDown).Row   1

Sheets("Database finale").Select
    Cells(Lastrow, 1).Value = TextBox1.Value
    
    If Cells(Lastrow, 1).Value = "M" Then
    Cells(UltimaRiga, 2).Value = WorksheetFunction.Max(Range("B1:B"))
    Else
    Cells(UltimaRiga, 2).Value = WorksheetFunction.Max(Range("B1:B"))
   End If
End Sub

This is the code I wrote so far, but I don't know how to fix the double condition (equal to M or equal to F) and add 1 to the previous maximum value and report it in Cells(Lastrow, 2).

Could you please help me with the code?

CodePudding user response：

You can use the maxifs function to check the maximum for either M or F:

Dim Lastrow As Long
Lastrow = Sheets("Database finale").Range("A1").End(xlDown).Row   1

Sheets("Database finale").Select
Cells(Lastrow, 1).Value = Worksheets("Database finale").TextBox1.Value

If Cells(Lastrow, 1).Value = "M" Then
    'Setting the maximum number for M using MaxIfs
    Cells(Lastrow, 2).Value = WorksheetFunction.MaxIfs(Range(Cells(1, 2), Cells(Lastrow - 1, 2)), Range(Cells(1, 1), Cells(Lastrow - 1, 1)), "M")   1
Else
    'Setting the maximum number for F using MaxIfs
    Cells(Lastrow, 2).Value = WorksheetFunction.MaxIfs(Range(Cells(1, 2), Cells(Lastrow - 1, 2)), Range(Cells(1, 1), Cells(Lastrow - 1, 1)), "F")   1
End If
    'Setting the concatenated string in the third column
    Cells(Lastrow, 3).Value = Cells(Lastrow, 1).Value & Cells(Lastrow, 2).Value

Note that I created a TextBox inside the sheet. You might need to modify this based on how you used this.