Partial match in VBA

[Bo2DK]

Hi,

I'm trying to get Excel to run through a worksheet and copy those rows which match the criterias that has been chosen in a userform. The only thing that i can't get to work, is the month value since the ref. cell is in the "dd-mmm-yy", and my vba code can only find the row if I change the format to "mmm" which isn't acceptable (in the userform you can which month to copy).

I do have most of the code, I think its only the part in bold that needs to be changed so that it looks for a partial match.

Month = SelMonth.Value
...
If Range(ColumnMon & CStr(LSearchRow)).Value = [B]Month[/B] Then

So how do I get Excel to find eg. "Jan" in a cell with the date format that looks like eg "11-Jan-15"?

 

[FormR]

Hi - maybe like this:

If Format(Range(ColumnMon & CStr(LSearchRow)).Value, "mmm") = Month Then

 

[Bo2DK]

Hi - maybe like this:

If Format(Range(ColumnMon & CStr(LSearchRow)).Value, "mmm") = Month Then

Thanks for the answer, unfortunatly it doesn't work.

 

[FormR]

unfortunatly it doesn't work.

You should try using a different variable name, Month is a reserved word in VBA, try again with sMonth for example.

 

[Bo2DK]

You should try using a different variable name, Month is a reserved word in VBA, try again with sMonth for example.

Thanks for the input, I've changed the variable name, but it doesn't change the result.

 

[FormR]

If difficult to provide much more advice - you've only posted one line of code. You probably need to step though the code and do a little de-bugging.

FYI - this is how I tested it.

Sub Test()
Dim sMonth As String, ColumnMon As String, LSearchRow As Long


sMonth = "Jun"
ColumnMon = "A"


For LSearchRow = 1 To 5
    If Format(Range(ColumnMon & CStr(LSearchRow)).Value, "mmm") = sMonth Then
        MsgBox ColumnMon & LSearchRow & " is Match"
            Else
        MsgBox "No Match"
    End If
Next LSearchRow


End Sub

Excel 2012
	A	B	C
1	Date	Comment	Msgbox Result
2	01-Mar-2015	< Formatted as dd-mmm-yyyy	No Match
3	01-Apr-2015		No Match
4	01-May-2015		No Match
5	01-Jun-2015		A5 is Match
6	01-Jul-2015		No Match
Sheet1