RE: Code no longer working

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
.

Relevant Pages

  • Re: 2105 says: "You cant go the specified record"
    ... >> End Sub ... which doesn't result in the 2105 error message. ... >> when using this vba code in my main form and i modify the control's ... >> Dim Future_Visit As Date ...
    (microsoft.public.access.formscoding)
  • Re: Code Error Message Excel 2000 - 2003
    ... Sub DateTesterBaselineIncrease() ... Dim DestWS As Worksheet ... What I meant with that line was, how do you need to use the 2 worksheets ... above error message highlighting the .Value after the Year: ...
    (microsoft.public.excel.programming)
  • Re: Userform for data pull
    ... One cause of that error message is there is no match. ... Private Sub FindButton_Click ... Dim x As Range, y As Range ... |> Chip Pearson ...
    (microsoft.public.excel.programming)
  • Re: Creating a chart after mail merge with VBA
    ... Sub MergeWithChart() ... Dim of As Word.OLEFormat ... Create Pie Chart Whilst Mail Merging ... indicating which line produces the error message. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: If Problem
    ... It would help if you told us *where* you are getting the error message. ... > Dim fname As String ... > Sub Import_Wizard ...
    (microsoft.public.excel.programming)