I have this BeforeDoubleClick event, below, that I am trying to run.
Everything was working fine yesterday now, it just jumps from the "If" to
the "End If" statements. I can't figure out what's going on.
Any ideas?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim errorWS As Worksheet, siteWS As Worksheet
Dim i As Integer, rw As Integer

Set siteWS = Worksheets("Site Milestone Dates")
Set errorWS = Worksheets("Error_MarketList")
errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row

If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
errorWS_lastRow Then
Application.EnableEvents = False
Application.ScreenUpdating = False

siteWS.Range("C4").Value = Target.Value
siteWS.Range("E4").Value = UCase(Target.Offset(0, 1).Value)

rw = 7
For i = 2 To 23 Step 2
siteWS.Range("E" & rw).Value = Target.Offset(0, i).Value
siteWS.Range("G" & rw).Value = Target.Offset(0, i + 1).Value
rw = rw + 2
Next i

rw = 7
For i = 24 To 45 Step 2
siteWS.Range("M" & rw).Value = Target.Offset(0, i).Value
siteWS.Range("O" & rw).Value = Target.Offset(0, i + 1).Value
rw = rw + 2
Next i

Application.EnableEvents = True
Application.ScreenUpdating = True
End If
siteWS.Select
End Sub

It seems to me your If...Then statement is setup wrong. I assume you are
wanting the code inside the If...Then statement to run if the user double
clicks any cell in Col.A that is greater that 4, right? If so, change your
If...Then statement from

If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
errorWS_lastRow Then

to this...

If Not Intersect(Target, errorWS.Range("A5:A" & errorWS_LastRow)) Is Nothing
Then

Plus, don't forget to declare your variables. errorWS_LastRow should be
declared as Long.

Plus, use this code to find you last cell with data. This will work for all
versions of Excel.

errorWS_LastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row

Hope this helps! If so, let me know, click "YES" below.

--
Cheers,
Ryan


"Ayo" wrote:

> I have this BeforeDoubleClick event, below, that I am trying to run.
> Everything was working fine yesterday now, it just jumps from the "If" to
> the "End If" statements. I can't figure out what's going on.
> Any ideas?
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> Dim errorWS As Worksheet, siteWS As Worksheet
> Dim i As Integer, rw As Integer
>
> Set siteWS = Worksheets("Site Milestone Dates")
> Set errorWS = Worksheets("Error_MarketList")
> errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row
>
> If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
> errorWS_lastRow Then
> Application.EnableEvents = False
> Application.ScreenUpdating = False
>
> siteWS.Range("C4").Value = Target.Value
> siteWS.Range("E4").Value = UCase(Target.Offset(0, 1).Value)
>
> rw = 7
> For i = 2 To 23 Step 2
> siteWS.Range("E" & rw).Value = Target.Offset(0, i).Value
> siteWS.Range("G" & rw).Value = Target.Offset(0, i + 1).Value
> rw = rw + 2
> Next i
>
> rw = 7
> For i = 24 To 45 Step 2
> siteWS.Range("M" & rw).Value = Target.Offset(0, i).Value
> siteWS.Range("O" & rw).Value = Target.Offset(0, i + 1).Value
> rw = rw + 2
> Next i
>
> Application.EnableEvents = True
> Application.ScreenUpdating = True
> End If
> siteWS.Select
> End Sub

Thanks Ryan. Works great so far.

"Ryan H" wrote:

> It seems to me your If...Then statement is setup wrong. I assume you are
> wanting the code inside the If...Then statement to run if the user double
> clicks any cell in Col.A that is greater that 4, right? If so, change your
> If...Then statement from
>
> If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
> errorWS_lastRow Then
>
> to this...
>
> If Not Intersect(Target, errorWS.Range("A5:A" & errorWS_LastRow)) Is Nothing
> Then
>
> Plus, don't forget to declare your variables. errorWS_LastRow should be
> declared as Long.
>
> Plus, use this code to find you last cell with data. This will work for all
> versions of Excel.
>
> errorWS_LastRow = errorWS.Cells(Rows.Count, "A").End(xlUp).Row
>
> Hope this helps! If so, let me know, click "YES" below.
>
> --
> Cheers,
> Ryan
>
>
> "Ayo" wrote:
>
> > I have this BeforeDoubleClick event, below, that I am trying to run.
> > Everything was working fine yesterday now, it just jumps from the "If" to
> > the "End If" statements. I can't figure out what's going on.
> > Any ideas?
> >
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
> > Dim errorWS As Worksheet, siteWS As Worksheet
> > Dim i As Integer, rw As Integer
> >
> > Set siteWS = Worksheets("Site Milestone Dates")
> > Set errorWS = Worksheets("Error_MarketList")
> > errorWS_lastRow = errorWS.Range("A65536").End(xlUp).Row
> >
> > If Target.Address() >= "$A$5" Or Target.Address() <= "$A$" &
> > errorWS_lastRow Then
> > Application.EnableEvents = False
> > Application.ScreenUpdating = False
> >
> > siteWS.Range("C4").Value = Target.Value
> > siteWS.Range("E4").Value = UCase(Target.Offset(0, 1).Value)
> >
> > rw = 7
> > For i = 2 To 23 Step 2
> > siteWS.Range("E" & rw).Value = Target.Offset(0, i).Value
> > siteWS.Range("G" & rw).Value = Target.Offset(0, i + 1).Value
> > rw = rw + 2
> > Next i
> >
> > rw = 7
> > For i = 24 To 45 Step 2
> > siteWS.Range("M" & rw).Value = Target.Offset(0, i).Value
> > siteWS.Range("O" & rw).Value = Target.Offset(0, i + 1).Value
> > rw = rw + 2
> > Next i
> >
> > Application.EnableEvents = True
> > Application.ScreenUpdating = True
> > End If
> > siteWS.Select
> > End Sub