Run code on each cell until month changes

[Sir Babydum GBE]

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?

[p45cal]

How did you get on with
http://www.vbaexpress.com/forum/showthread.php?61627-VBA-i-want-the-value-not-the-formula&p=374612&viewfull=1#post374612
and
http://www.vbaexpress.com/forum/showthread.php?61628-Getting-VBA-to-decide-which-cell-to-use&p=374605&viewfull=1#post374605
?


(If I've helped…)

[Sir Babydum GBE]

How did you get on with
http://www.vbaexpress.com/forum/showthread.php?61627-VBA-i-want-the-value-not-the-formula&p=374612&viewfull=1#post374612
and
http://www.vbaexpress.com/forum/showthread.php?61628-Getting-VBA-to-decide-which-cell-to-use&p=374605&viewfull=1#post374605
?

(If I've helped…)

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

[p45cal]

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

[Sir Babydum GBE]

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?

[p45cal]

then place it in specific rows of the active columnYes, 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))






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.

[Sir Babydum GBE]

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 :hi: