Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem show message when match date in combobox with header



I don't understand why pops message box despite of matching selected date from combobox1 with headers in row4 .

by the way the original code was working when indicates to row1.

I'm talking about th problem in theses lines 

 With Sheets("stock").[a4].CurrentRegion
        col = Application.Match(CLng(d), .Rows(4), 0)
        If IsError(col) Then MsgBox "Can not get column matches to " & Me.ComboBox1: Unload Me: Exit Sub

I hope the experts guide me where is my bad !



Selected Answer

Hi again Tubruk

You're trying to use Match to look up a date (e.g. 02/12/2023 in dd/mm/yyyy format) in the header row but your code has:

    With Sheets("stock").[a4].CurrentRegion

which is the range $A$4:$H$1599 i.e. the area inside the first fully blank rows and fully blank columns you reach when moving away from the sratr range i.e. cell A4 . You can check by putting this in the VBA's Immediate window:

Debug.Print Sheets("stock").[a4].CurrentRegion.Address

When you have:

        col = Application.Match(CLng(d), .Rows(4), 0)

.Rows(4) equates to row 7 of your worksheet (not row 4) so you get an error since there aren't any matching dates in row 7.

To correct it, just change your test line to:

        col = Application.Match(CLng(d), .Rows(1), 0)

Hope this makes sense and helps. If so, please remember to mark this Answer as Selected.



thank you so much ,John
tubrak (rep: 24) Mar 28, '24 at 2:06 pm
Glad that helped. Thanks for selecting my Answer, Tubrak
John_Ru (rep: 6142) Mar 28, '24 at 3:41 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login