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.)
Hi
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 SubI hope the experts guide me where is my bad !
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].CurrentRegionwhich 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.AddressWhen 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.