Consulting

Results 1 to 7 of 7

Thread: Run code on each cell until month changes

  1. #1
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location

    Run code on each cell until month changes

    Good evening

    Currently I have worksheet code that runs if I double-click a cell in row 2 (row 2 contain only dates)

    What I'd like to do is: when I double click a cell in row 2 (say, column D) it runs the code and then looks at the cell in E2 and, if that cell contains a date in the same month, then perform the code again. Do this until it finds a date in a different month - then stop.

    Assuming all my code does is say "Match" in row 3 if Row 2 is the same month. How do I get it to cycle through the cells in row 2, to check that the date is in same month?
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Quote Originally Posted by p45cal View Post
    hi P45cal. My apologies. But in these particalular instances either they did not work or I’d figured an alternative before I saw the reply. In the first instance, the “Dim TalkNumber
    TalkNumber = ActiveCell.Value, stilldidn’t get the retired result. And as I’d found a workaround, I didn’t research further.

    in the second, the validation lists in question are all found in the same row across the sheet. So it occurred to me soon after (I’d been trying to figure it out for ages) that all I needed to do was specify the row number of the active column and get the values that way.

    i do usually reply. But I do forget sometimes, especially as lately I’ve been working well into the early hours with these issues. I’ve got tons to do on these projects and I’m trying to figure ost of it out myself. I’m. It so bad with the formula side. I’m useless with VBA.

    anyway, thanks again. But still need help with this particular question
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Try something along these lines:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Cells.Count = 1 And Target.Row = 2 Then
      If IsDate(Target.Value) Then
        Cancel = True
        Set cll = Target
        mnth = Month(cll.Value)
        yr = Year(cll.Value)
        Do
          cll.Offset(1).Value = "Match" 'your code here.
          Set cll = cll.Offset(, 1)
        Loop Until Month(cll.Value) <> mnth Or Year(cll.Value) <> yr
      End If
    End If
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Hi

    Thanks for this. So for the sake of trying to keep my original question as clear as possible, I kept the side details out (like what the main macro actually does - which is open a website and scrape info relating to the date on my sheet. then place it in specific rows of the active column). To do this, the Worksheet_BeforeDoubleClick code calls a routine in a module.

    So, when I tried your code it ran in an eternal loop, always performing the job on the same column - not moving over. It may be that the called macro somehow interferes with the Worksheet_BeforeDoubleClick code - resetting the target cell to the same one each time?
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by Sir Babydum GBE View Post
    then place it in specific rows of the active column
    Yes, the active column doesn't change in my code (I don't like relying on the activecell because it can be moved around by who knows what), instead I use cll (a (my) range object variable). So there are two ways around this; (1)keep activating a new cell or (2)use cll as a reference:
    1. After:
    Set cll = cll.Offset(, 1)
    add:
    cll.activate
    This is not my preferred solution for the reason above but might be a quick and dirty one.

    2. Instead of using activecell.column or whatever you're using (perhaps he likes of cells(3,activecell.column)?) use cll.column (cells(3,cll.column))





    Quote Originally Posted by Sir Babydum GBE View Post
    So, when I tried your code it ran in an eternal loop, always performing the job on the same column - not moving over. It may be that the called macro somehow interferes with the Worksheet_BeforeDoubleClick code - resetting the target cell to the same one each time?
    I would need a glimpse of the snippet which writes to the sheet to give you a more definitive answer.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Mentor Sir Babydum GBE's Avatar
    Joined
    Mar 2005
    Location
    Cardiff, UK
    Posts
    499
    Location
    Apologies for the delay... Needed to get away from the computer for a few days.

    So I just tried adding
    cll.activate
    after
    Set cll = cll.Offset(, 1)
    And it works perfectly.

    Thanks Pascal
    Have a profound problem? Need a ridiculous solution? Post a question in Babydum's forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •