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 is the value of M1?

 

[dss28]

What is the value of M1?

thanks @Fluff for responding.

Values in Column B are dates in dd-mm-yyyy format while column C are serial numbers for the events ocuring on a particular date. I enter the value of date in cell M1 through a userform.
If there are two entries of same date in column B, they will have values of 1 and 2 in column C.
For next entry of the same date I should get a value of 3 which will be captured in Column C while saving he data.

When I start to enter new data, it starts from value 1 even if there are entries of the same date previously. I get corresponding max+1 values for same date for new data being entered starting from 1 even if the date changes in this new data. But does not calculate taking into account the past data in the column B and C.

 

[Fluff]

If M1 is a date try

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

 

[dss28]

If M1 is a date try

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

I tried CDate option but not working.

However, if I open the sheet and run the macro through Macros---select the macro name MaxNo - it works properly in either case. Means the code is correct. May be the macro calling procedure is not.

So I tried the following code also using worksheet_change but not working.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$M$1" Then

Call MaxNo

End If

End Sub

any other method should I try?

 

[Fluff]

How about

Public Sub MaxNo()

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

End Sub

 

[dss28]

How about

Public Sub MaxNo()

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

End Sub

thanks ... always to my rescue.....

from your earlier suggestion to use CDate, prompted me to look further in my coding. I came to know that date value into sheet was being stored as text.

While searching the probable answer, I learnt that "Value in textbox is always stored as string value, never as a Date, Numeric. It has always to be converted into appropriate data type."

CDate as suggested earlier was giving me 'run-time error 13, type mismatch'. I could not resolve it. If you can help on this for future / to learn why it was occurring will be grateful.

Date value had always been a problem with my coding. now learnt a lesson.

So used the expression below for date transfer from textbox to sheet.

Sheet14.Cells(final, 2) = UserForm3.TextBox37    ' date colB
Sheet14.Cells(final, 2) = Date    ' date colB
Sheet14.Cells(final, 2).NumberFormat = "dd-mm-yyyy"

Is there a better option to save date as date in particular format
thanks a lot in advance.

 

[Fluff]

The first line in that code is redundant as you are overwriting the the cell with the current date, so it will never have the date from the textbox.

 

[dss28]

The first line in that code is redundant as you are overwriting the the cell with the current date, so it will never have the date from the textbox.

yes true, did not anticipate this. Thanks
let me try ...

 

[dss28]

thanks ... always to my rescue.....

from your earlier suggestion to use CDate, prompted me to look further in my coding. I came to know that date value into sheet was being stored as text.

While searching the probable answer, I learnt that "Value in textbox is always stored as string value, never as a Date, Numeric. It has always to be converted into appropriate data type."

CDate as suggested earlier was giving me 'run-time error 13, type mismatch'. I could not resolve it. If you can help on this for future / to learn why it was occurring will be grateful.

Date value had always been a problem with my coding. now learnt a lesson.

So used the expression below for date transfer from textbox to sheet.

Sheet14.Cells(final, 2) = UserForm3.TextBox37    ' date colB
Sheet14.Cells(final, 2) = Date    ' date colB
Sheet14.Cells(final, 2).NumberFormat = "dd-mm-yyyy"

Is there a better option to save date as date in particular format
thanks a lot in advance.

CDate(UserForm3.TextBox37.Value) is giving me r - run time error 13 : type mismatch
not able to resolve and go further. please help