Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code no longer working
Hi, can someone look at the following code, which copies certain data from
sheet 'claims' to sheet 'charging', and suggest why it doesn't work? It is in a module and worked ok until recently so I am wondering if code elsewhere in the workbook is affecting it somehow. I get the error message "Compile error: Argument not optional" with 'Last Row' (line 5) highlighted: Sub Charge() If ActiveCell.Value = "C" Then RowNo = ActiveCell.Row With Sheets("Charging") LastRow = .Range("B" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 End With Sheets("Charging").Range("B" & Newrow).Value = _ Sheets("claims").Range("D" & RowNo) Sheets("Charging").Range("C" & Newrow).Value = _ Sheets("claims").Range("E" & RowNo) Sheets("Charging").Range("D" & Newrow).Value = _ Sheets("claims").Range("F" & RowNo) Sheets("Charging").Range("E" & Newrow).Value = _ Sheets("claims").Range("G" & RowNo) Sheets("Charging").Range("F" & Newrow).Value = "14.50" Sheets("claims").Activate Application.Goto Reference:=Worksheets("Claims").Range("F" & Newrow) End If End Sub Thanks for your suggestions -- Traa Dy Liooar Jock |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code no longer working
That code compiles so that is not our issue. My best guess is that you have
declared a funciton or sub somewhere called last row. Try this change to see if it starts working... Sub Charge() Dim lngLastRow As Long Dim lngNewRow As Long Dim lngRowNo As Long If ActiveCell.Value = "C" Then lngRowNo = ActiveCell.Row With Sheets("Charging") lngLastRow = .Range("B" & Rows.Count).End(xlUp).Row lngNewRow = lngLastRow + 1 .Range("B" & lngNewRow).Value = _ Sheets("claims").Range("D" & lngRowNo) .Range("C" & lngNewRow).Value = _ Sheets("claims").Range("E" & lngRowNo) .Range("D" & lngNewRow).Value = _ Sheets("claims").Range("F" & lngRowNo) .Range("E" & lngNewRow).Value = _ Sheets("claims").Range("G" & lngRowNo) .Range("F" & lngNewRow).Value = "14.50" Sheets("claims").Activate Application.Goto Reference:=Worksheets("Claims").Range("F" & lngNewRow) End With End If End Sub -- HTH... Jim Thomlinson "Jock" wrote: Hi, can someone look at the following code, which copies certain data from sheet 'claims' to sheet 'charging', and suggest why it doesn't work? It is in a module and worked ok until recently so I am wondering if code elsewhere in the workbook is affecting it somehow. I get the error message "Compile error: Argument not optional" with 'Last Row' (line 5) highlighted: Sub Charge() If ActiveCell.Value = "C" Then RowNo = ActiveCell.Row With Sheets("Charging") LastRow = .Range("B" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 End With Sheets("Charging").Range("B" & Newrow).Value = _ Sheets("claims").Range("D" & RowNo) Sheets("Charging").Range("C" & Newrow).Value = _ Sheets("claims").Range("E" & RowNo) Sheets("Charging").Range("D" & Newrow).Value = _ Sheets("claims").Range("F" & RowNo) Sheets("Charging").Range("E" & Newrow).Value = _ Sheets("claims").Range("G" & RowNo) Sheets("Charging").Range("F" & Newrow).Value = "14.50" Sheets("claims").Activate Application.Goto Reference:=Worksheets("Claims").Range("F" & Newrow) End If End Sub Thanks for your suggestions -- Traa Dy Liooar Jock |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code no longer working
Indeed I do - there's a Function called LastRow in a Module. I will
investigate in the morning! Thanks Jim. -- Traa Dy Liooar Jock "Jim Thomlinson" wrote: That code compiles so that is not our issue. My best guess is that you have declared a funciton or sub somewhere called last row. Try this change to see if it starts working... Sub Charge() Dim lngLastRow As Long Dim lngNewRow As Long Dim lngRowNo As Long If ActiveCell.Value = "C" Then lngRowNo = ActiveCell.Row With Sheets("Charging") lngLastRow = .Range("B" & Rows.Count).End(xlUp).Row lngNewRow = lngLastRow + 1 .Range("B" & lngNewRow).Value = _ Sheets("claims").Range("D" & lngRowNo) .Range("C" & lngNewRow).Value = _ Sheets("claims").Range("E" & lngRowNo) .Range("D" & lngNewRow).Value = _ Sheets("claims").Range("F" & lngRowNo) .Range("E" & lngNewRow).Value = _ Sheets("claims").Range("G" & lngRowNo) .Range("F" & lngNewRow).Value = "14.50" Sheets("claims").Activate Application.Goto Reference:=Worksheets("Claims").Range("F" & lngNewRow) End With End If End Sub -- HTH... Jim Thomlinson "Jock" wrote: Hi, can someone look at the following code, which copies certain data from sheet 'claims' to sheet 'charging', and suggest why it doesn't work? It is in a module and worked ok until recently so I am wondering if code elsewhere in the workbook is affecting it somehow. I get the error message "Compile error: Argument not optional" with 'Last Row' (line 5) highlighted: Sub Charge() If ActiveCell.Value = "C" Then RowNo = ActiveCell.Row With Sheets("Charging") LastRow = .Range("B" & Rows.Count).End(xlUp).Row Newrow = LastRow + 1 End With Sheets("Charging").Range("B" & Newrow).Value = _ Sheets("claims").Range("D" & RowNo) Sheets("Charging").Range("C" & Newrow).Value = _ Sheets("claims").Range("E" & RowNo) Sheets("Charging").Range("D" & Newrow).Value = _ Sheets("claims").Range("F" & RowNo) Sheets("Charging").Range("E" & Newrow).Value = _ Sheets("claims").Range("G" & RowNo) Sheets("Charging").Range("F" & Newrow).Value = "14.50" Sheets("claims").Activate Application.Goto Reference:=Worksheets("Claims").Range("F" & Newrow) End If End Sub Thanks for your suggestions -- Traa Dy Liooar Jock |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF no longer working | Excel Discussion (Misc queries) | |||
SUMIF no longer working | Excel Worksheet Functions | |||
hyperlinks no longer working | Excel Discussion (Misc queries) | |||
Macro no longer working - Don Guillett | Excel Programming | |||
Formulas no longer working | Excel Worksheet Functions |