Max IF function in vba

[dss28]

i want to find the maximum value in column C corresponding to a value mentioned on Cell M1 of which data is given in Column B
the code:
max(if(B:B=M1,C:C) when written in one of the cells in the sheet with Ctrl+Shift+Enter gives the result correctly. however when applying the code in vba I am using the following code as found in this forum. i have the following code in module :

Public Sub MaxNo() '

Dim myVar As Long
ThisWorkbook.Sheets("Data").Activate
myVar = Evaluate("=MAX(IF(B:B=M1,C:C))")
ThisWorkbook.Sheets("Data").Range("O1").Value = myVar

ThisWorkbook.Sheets("Data").Range("Q1").Value = 1 + myVar

'End If
End Sub

I have another code in userform to transfer the search value in cell M1 of the sheet.

Private Sub TextBox37_Change()

With Sheet14
ThisWorkbook.Sheets("Data").Range("M1").Value = UserForm3.TextBox37.Value '
Call MaxNo
End With

End Sub

I am interested in the value as given in cell Q1. The code does not work every time but when i change the value in the sheet in M1 cell and run the macro it works. but not through userform everytime and gives value 1 meaning the search text is not present.

i am not able to figure out what is wrong in the procedure or executing the code.
request for help

 

[Fluff]

What are you putting into the textbox.

 

[dss28]

What are you putting into the textbox.

I am entering date in dd-mm-yyyy format in textbox37 through a pop up calendar.

1. Now if I add a line "on error resume next" by passing the error line and is giving the required results. Is this a Ok procedure to use?.
2. If I execute the code through private sub textbox37_Afterupdate code and then press enter - it does not give error. But I want to avoid pressing enter.

But why it should give error on line

ThisWorkbook.Sheets("Data").Range("M1").Value = CDate(UserForm3.TextBox37.Value)

3. I transfer the data from usrform to a listbox where the user can edit the data if required and finally transfer the data from listbox to a sheet. Here, Can I use the CDate function like this for transferring the date value from listbox to worksheet?

ws.Cells(eR, 14).Value = CDate(ListBox1.Column(3, i))                    ' date

 

[Fluff]

Is this a Ok procedure to use?.

Probably not, but without seeing all the code & knowing exactly what the values are, are cannot advise anything.

 

[dss28]

Probably not, but without seeing all the code & knowing exactly what the values are, are cannot advise anything.

Here is the sequence of steps and required code:
1. on textbox37, date is entered through a pop up calendar, and data is transferred to sheet "Data" and to listbox1 on the userform3
2. After data is finalized, data is transferred from textboxes and listbox to another sheet

waiting for the reply sir.

code on frm3

Private Sub TextBox37_Change() ' scheduled on date textbox 37

On Error Resume Next

With Sheet14

ThisWorkbook.Sheets("Data").Range("M1").Value = CDate(UserForm3.TextBox37.Value) ' sheet2

Call Test2

End With

End Sub



Private Sub TextBox8_Change() ' total value of each row in textbox9

On Error Resume Next

TextBox9.text = Val(TextBox8.text) * Val(TextBox7.text)

UserForm3.TextBox34.Value = ThisWorkbook.Sheets("Data").Range("Q1").Value

End Sub





Sub SaveToData()

Dim i As Integer
Dim j As Integer
Dim final As Double
Dim actual As Double
Dim iRow As Long

Sheet14.Activate

final = ThisWorkbook.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Offset(1).row ' sheet14
If UserForm3.TextBox36.Value = "" Then ' hidden textbox
iRow = Sheet14.Range("A" & Rows.Count).End(xlUp).row + 1

Else
iRow = UserForm3.TextBox36.Value

End If

With Sheet14.Range("A" & iRow) ' sheet14 = Data

.Offset(0, 0).Value = "=Row()-1"
Sheet14.Cells(final, 2) = CDate(UserForm3.TextBox37) ' date colB
Sheet14.Cells(final, 3) = Format(UserForm3.TextBox34, "0") ' task No. col C
Sheet14.Cells(final, 4) = UserForm3.TextBox1 ' name marathi colD
Sheet14.Cells(final, 5) = UserForm3.TextBox21 ' name colE
Sheet14.Cells(final, 6) = UserForm3.ComboBox2 ' colE

ThisWorkbook.Sheets("Data").Columns(3).NumberFormat = "0" ' sr no columnC

End With

End Sub


Private Sub CommandButton4_Click() ' save data from frm3 and listbox1 on frm3 to sheet10

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False


Dim i As Long
Dim ws As Worksheet
Dim eR As Long
Dim iRow As Long
Dim eR1 As Long
Dim rowId As Long
Set ws = Sheet10


On Error Resume Next

If UserForm3.TextBox22.Value = "" Then ' hidden box
iRow = ThisWorkbook.Sheets("SatDon").Range("A2:A" & Rows.Count).End(xlUp).row + 1
Else
iRow = UserForm3.TextBox22.Value

Application.ScreenUpdating = False
End If
With ThisWorkbook.Sheets("SatDon ").Range("A" & iRow)

For i = 0 To ListBox1.ListCount - 1
eR = WorksheetFunction.CountA(ws.Range("B:B")) + 1

eR1 = WorksheetFunction.CountA(ws.Range("A:A")) + 1

ws.Cells(eR1, 1).Value = "=Row()-1" ' irow no.
ws.Cells(eR1, 2).Value = Year(Now()) ' year now
ws.Cells(eR1, 3).Value = UserForm3.TextBox23.Value ' serial no.
ws.Cells(eR, 4).Value = ListBox1.Column(6, i) ' Year / No.format
ws.Cells(eR, 5).Value = CDate(ListBox1.Column(7, i)) ' date
ws.Cells(eR, 6).Value = ListBox1.Column(8, i) ' name
ws.Cells(eR, 7).Value = ListBox1.Column(9, i) ' contact no.

Next i
End With
MsgBox "Data saved"

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
ActiveSheet.DisplayPageBreaks = True

End Sub

and code in Module1

Public Sub Test2()

Dim myVar As Long

On Error Resume Next

With ThisWorkbook.Sheets("Data")
ThisWorkbook.Sheets("Data").Range("M1").Value = CDate(UserForm3.TextBox37.Value)
myVar = .Evaluate("=MAX(IF(B:B=M1,C:C))")
.Range("O1").Value = myVar
.Range("Q1").Value = 1 + myVar
End With

End Sub

 

[Fluff]

I would advise removing the On Error Resume Next as this just masks any errors. Also you should be using an AfterUpdate or Exit event as the change event fires when ever you type something into the textbox.

 

[dss28]

I would advise removing the On Error Resume Next as this just masks any errors. Also you should be using an AfterUpdate or Exit event as the change event fires when ever you type something into the textbox.

Thanks for your suggestions.
I had earlier tried "AfterChange" event for the textbox37 but it did not work . Today tried the "exit" event but it also do not give me the output.
Any change in the above code required? to implement this events if I want to avoid theOn Error Resume Next

 

[Fluff]

Not really as I have no idea what you are putting into the textbox, or how.