A real puzzler:

Dim pcol as Long

pcol = Target.Column

Target.Column shows as 16 (this is correct)
pcol shows as 17 (how can this be?)

This is embedded in a standard
Worksheet_Change(ByVal Target As Excel.Range) event macro
Excel 97 SR2, Windows 2000

Does anyone have any idea why this isn't working right?

All help is appreciated...

steve

Hi Steve,
Post the entire routine.


--

John

johnf202 at hotmail dot com


"steve" wrote in message
...
A real puzzler:

Dim pcol as Long

pcol = Target.Column

Target.Column shows as 16 (this is correct)
pcol shows as 17 (how can this be?)

This is embedded in a standard
Worksheet_Change(ByVal Target As Excel.Range) event macro
Excel 97 SR2, Windows 2000

Does anyone have any idea why this isn't working right?

All help is appreciated...

steve

John,

Thanks for the quick reply. The code is below.

I closed Excel and restarted. Now it works fine!

Guess I had some corruption...

steve


=================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lrow As Long, prow As Long, pcol As Long, mnth As Integer, yr As Integer

If Target.Column 4 Then
If Target.Row 3 Then
Application.ScreenUpdating = False
Application.EnableEvents = False

' Last row of data
lrow = Cells(Rows.Count, "A").End(xlUp)
' Present column
pcol = Target.Column
' New Month & Year
mnth = Month(Cells(1, pcol - 1)) + 1
yr = Year(Cells(1, pcol - 1))
If mnth = 13 Then
mnth = 1
yr = yr + 1
End If
' Enter column total and month on Summary sheet
Sheets("Summary").Cells(3, pcol).Value =
WorksheetFunction.Sum(Sheets("Corr").Range(Cells(3 , pcol), Cells(lrow,
pcol)))
Sheets("Summary").Cells(1, pcol).Value = Sheets("Corr").Cells(1, pcol).Value
Sheets("Summary").Cells(4, pcol).FormulaR1C1 = "=R5C1*R1C+R6C1"

' Enter new month & year on sheet
Sheets("Corr").Cells(1, pcol).Formula = mnth & "/1/" & yr

' set names for chart
pcol = ActiveSheet.Cells(1, 256).End(xlToLeft).Column + 1

ActiveWorkbook.Names.Add Name:="cCorr",
RefersToR1C1:="=Summary!R3C2:R3C" & pcol + 1
ActiveWorkbook.Names.Add Name:="cTrend", RefersToR1C1:= _
"=Summary!R4C2:R4C" & pcol
ActiveWorkbook.Names.Add Name:="cDate", RefersToR1C1:= _
"=Corr!R1C2:R1C" & pcol

pcol = ActiveSheet.Cells(1, 256).End(xlToLeft).Column

Sheets("Summary").Cells(5, 1).FormulaR1C1 = "=SLOPE(R3C" & pcol - 11 &
":R3C" & pcol _
& ",R1C" & pcol - 11 & ":R1C" & pcol & ")"
Sheets("Summary").Cells(6, 1).FormulaR1C1 = "=Intercept(R3C" & pcol - 11 &
":R3C" & pcol _
& ",R1C" & pcol - 11 & ":R1C" & pcol & ")"

Application.ScreenUpdating = True
Application.EnableEvents = True

End If
End If
End Sub

"jaf" wrote in message
...
Hi Steve,
Post the entire routine.


--

John

johnf202 at hotmail dot com


"steve" wrote in message
...
A real puzzler:

Dim pcol as Long

pcol = Target.Column

Target.Column shows as 16 (this is correct)
pcol shows as 17 (how can this be?)

This is embedded in a standard
Worksheet_Change(ByVal Target As Excel.Range) event macro
Excel 97 SR2, Windows 2000

Does anyone have any idea why this isn't working right?

All help is appreciated...

steve