This code errors out

[Aussiebear]

The following code shows a compile error in the line "If target = "Hold" then DoHold target"

It used to work but its some time since I've had a look at this and I'm not sure why it no longer wants to run. Should there be an "Else" or something to this effect between the two "If target = ....." ? The user will be entering either Cleared or Hold as the value


[VBA]Private Sub Worksheet_Change(ByVal target As Range)

If target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If target.Column = 22 And target.Row > 1 Then
'Copy data to Bucket History and
' clear data from worksheet
If target = "cleared" Then DoClear target
If target = "Hold" Then DoHold target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub[/VBA]

[mdmackillop]

Hi Ted,
If you're not using Option Compare Text, try
[VBA] If UCase(target) = "CLEARED" Then DoClear target
If UCase(target) = "HOLD" Then DoHold target
[/VBA]

[Aussiebear]

Md, This was on one of the early versions here at home and I made some changes to the hidden row (row 2 on the worksheet "Whiteboard" and suddenly up pops the message Compile error.

Looking back at the coding for the sheet it has both Option Explicit and Option Compare Text as the header for the page.

[VBA]Option Explicit
Option Compare Text

Private Sub Worksheet_Change(ByVal target As Range)

If target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If target.Column = 22 And target.Row > 1 Then
'Copy data to Bucket History and
' clear data from worksheet
If target = "cleared" Then DoClear target

If target = "Hold" Then DoHold target
End If
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Sub DoClear(target As Range)
Dim lRow As Long
Dim cRow As Long

lRow = Sheets("Bucket History").Cells(Rows.Coount, 1).End(xlUp).Offset(1).Row
With ActiveSheet
Range(.Cells(target.Row, 1), .Cells(target.Row, 22)).Copy
With Sheets("Bucket History")
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlValue
.Range(.Cells(lRow - 1, 1), .Cells(lRow - 1, 22)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlFormats
End With
Application.CutCopyMode = False

' Reset the values for the row just copied
cRow = target.Row
.Range("B" & cRow & ":H" & cRow & ",J" & cRow & ",L" & cRow & ",N" & cRow & ",P" & cRow & ",R" & cRow & ",T" & cRow & ":V" & cRow).ClearContents
' **********************

'Reinstate formulae cells; copied down from above
InsertFormulae cRow
End With
End Sub

Sub DoHold()
Dim lRow As Long
Dim cRow As Long

lRow = Sheets("Bucket History").Cells(Rows.Coount, 1).End(xlUp).Offset(1).Row
With ActiveSheet
Range(.Cells(target.Row, 1), .Cells(target.Row, 22)).Copy
With Sheets("Bucket History")
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlValue
.Range(.Cells(lRow - 1, 1), .Cells(lRow - 1, 22)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 22)).PasteSpecial xlFormats
End With
Application.CutCopyMode = False

' Reset the values for the row just copied
cRow = target.Row
.Range("B" & cRow & ":H" & cRow & ",J" & cRow & ",L" & cRow & ",N" & cRow & ",P" & cRow & ",R" & cRow & ",T" & cRow & ":V" & cRow).ClearContents
' **********************

'Reinstate formulae cells; copied down from above
InsertFormulae cRow
End With
End Sub

Sub InsertFormulae(Rw As Long)
Dim Rg As Range
Dim Colls, c
Application.ScreenUpdating = False
Cols = Array(4, 5, 6, 9, 11, 13, 15, 17, 19)

Set Rg = Cells(Rw - 1, 4)
For Each c In Colls
Set Rg = Union(Rg, Cells(Rw - 1, c).Resize(2))
Next
Rg.FillDown

End Sub

[/VBA]

The one at work has two extra code sections. One which uses Worksheet_BeforeDoubleClick() allows me to double click a cell within the range $U$3:$U$157 to enter a date, and the other is a Worksheet_SelectionChange() event which allows me to more easily find the active cell in the range $B$3:$B$157.

[Bob Phillips]

iT is failing because you are trying to pass a parameter to DoHold, but that procedure doesn't have any arguments.

[mdmackillop]

...and the Target parameter is required for DoHold to run.

[Aussiebear]

Okay have corrected the DoHold(Target As Range) which has corrected the initial issue.

Second issue is now in the InsertFormulae sub

I've corrected the typo "Colls" & "Cols" to eliminate the have not dim c error message and it seems to work.

Thankyou

[Bob Phillips]

I found four other bugs in this code, three compile, one run. Are you saying it is working okay?

[Aussiebear]

As I rushed out the door last night it appeared to be working...