0

My worksheet consists of several rows - Row 3 is a list of (increasing) Month End dates from 42400 (31/01/2016) to 44926 (31/12/2022). The dates are listed as numbers, formatted as dd/mm/yyyy. The object of the exercise is to find the column which is headed 31/12/2021 (Check_Year)

    Dim Check_Year As Integer                                                                   'Line 1
    Dim ColNo As Integer                                                                        'Line 2
    
    Check_Year = 2121                                                                           'Line 3
    
    
    
    Windows("My Workbook.xlsm").Activate                                                        'Line 4 - open "My Workbook" workbook
    Sheets("My worksheet").Select                                                               'Line 5 - Select the worksheet "My worksheet"
    ActiveWindow.Panes(1).Activate                                                              'Line 6 - Activate worksheet
    ActiveCell(3, 1).Select                                                                     'Line 7 - select Cell Column A, Row 3
    Range("3:3").Select                                                                         'Line 8 - Select (the whole of) Row 3
    Selection.Find(What:="31/12/" & Check_Year, After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate                                             'Lines 9 to 11 - select the month end date row (Row 3) and find the 31st December for the Check_Year
    
    ColNo = (ActiveCell.Column - 1)                                                             'Line 12 - "ColNo" is the column immediately before the column "31/12/2021"

'I keep getting error "Object variable or With block variable not set" for Lines 9 to 11, I understand that I should be defining an "Object" (with "Set"), but I can't figure out what should be set and where in the programme it fits. Further more, I'm not sure I need lines 6 & 7. I would be most grateful for your help.

mikesingleton
  • 27
  • 4
  • 1
    You're getting that error because the `Find` failed, so there is no cell to activate. – BigBen Nov 03 '20 at 18:16
  • Try `What:=CDate("31/12/" & Check_Year)`, though I think `Find` is finicky with dates so might not work. – BigBen Nov 03 '20 at 18:18
  • If all else fails then looping through about 2000 lines (worst case) isn't that bad. It won't take long to find it manually with a loop and if – Andreas Nov 03 '20 at 18:26
  • 2
    You could probably benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). Also, note that we don't have access to your code editor, so don't know what things like "line 7" refer to. Please find a way to refer to lines of your code which doesn't depend on information not readily gleaned from the question itself. – John Coleman Nov 03 '20 at 18:26
  • Hi John, The line numbers simply refer to the lines of code you can see on the screen – mikesingleton Nov 03 '20 at 19:20
  • I might need more than just 5 minutes to read "How to avoid ,,,,etc". I have started it but it's getting late here.I'll look again tomorrow, but it does look an interesting discussion – mikesingleton Nov 03 '20 at 19:30
  • Thanks to BigBen (that actually does work - my bad, as I've (successfully) used it before, but a thousand thanks to John Coleman for the pointer to "How to avoid using Select in Excel VBA". It takes quite a while to a) read it and b) understand it. The article covers both the use (or I should say the "misuse") of Select and Activate, both of which are scattered through my code like confetti. My only excuse is that I have NO formal tuition in VBA, and have relied heavily on the Macro recorder which, as you know, uses Select and Activate freely. I doubt I have the patience to change them all. – mikesingleton Nov 04 '20 at 15:54
  • P.S. The article also covers neat ways of copying data - I would HIGHLY recommend that beginners read it – mikesingleton Nov 04 '20 at 15:59

0 Answers0