Expert eye needed

[gootroots]

Hi

I need an experts eye to scan over the following code to pinpoint why it
fall over at the point

lngLastRow =

When this is fixed is there anywhere else that needs addressed that might
then cause it to fall over too.


Here is the compete code:

Option Explicit
Option Compare Text

Private Sub AddRow_Click()


Dim rng As Range
Dim lr As Long
Dim sh As Worksheet
Dim ws As Worksheet
Dim i As Integer
Dim FD As String 'find string
Dim Frow As Integer 'found row
Dim sel As String
Dim shname As String
Dim x As Long
Dim ingLastRow As Long

' remove filter
For x = 1 To Worksheets.Count
If Sheets(x).FilterMode Then
Sheets(x).ShowAllData
End If
Next

' insert value in last blank cell in "B"
If IsEmpty(Range("b9")) Then
MsgBox "No record found in B9.", vbInformation
Else
lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

If lngLastRow <= 10 Then
Range("B10").Value = Range("B9").Value
Else
Cells(lngLastRow, "B").Value = Range("B9").Value
End If
End If


Application.ScreenUpdating = False

Set sh = ActiveSheet
shname = ActiveSheet.Name
FD = ActiveCell.Value
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("A11:H" & lr)
sel = Selection.Address
rng.Sort Range(sel), xlAscending

'Loop through the newly inserted row and copy formula from 1 cell above
Frow = Range("B:B").Find(FD, LookIn:=xlValues).Row
For i = 1 To 10 Step 2 'change to extend if Range grows.
Cells(Frow - 1, i).Copy Cells(Frow, i)
Next i

'Take new data and paste it on the Uses sheet.
For Each ws In ThisWorkbook.Worksheets
If Left((ws.Name), 4) = "Uses" And Not ws.Name = shname Then
Sheets(shname).Rows(Frow).Copy
ws.Cells(Frow, 1).Insert

Range("B10").Select
End If

Next ws

Application.ScreenUpdating = True

Application.CutCopyMode = False

End Sub

Much appreciate any help or suggestions.

 

[marcus]

Hi

Looks pretty simple to me. This line, Option Explicit is all about
declaring all your variables. A good way to start anything in VBA.
Your lngLastRow is not declared as a variable. A simple misspelling
where you have Dim ingLastRow As Long

Replace with

Dim lngLastRow As Long

Take care

Marcus

 

[john]

I am about to head of to a meeting but quickly tried your code & did not get
that problem in 2003 so can only guess that you are using 2007??

If so, whilst I am no 2007 expert and I could be wrong here, it is my
understanding that this version can be less tolerant when writing code in an
unqualified manner.

where you have:

lngLastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1

which falls over for you

I would suggest that you qualify it to the worksheet something like:

Set ws = Thisworkbook.Worksheets("Sheet1")

With ws

lngLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1

End With

Note the periods (full stops) in front of CELLS and ROWS do not omit them.

See if this correction helps you. Also and whilst you are at it, consider
doing same for all other unqualified range references you have in your code.
Apart from being considered good practice, it will ensure that your code
performs as intended.

Hope of some help.

 

[gotroots]

Marcus

Just want to let you know I sent the sample workbook to your email address
as before for you to take a look, hope you got it. Trust its ok for me do
that!

Thanks

 

[marcus]

Hi

I got your XL SS and replied with an updated version of your
workbook. Did you get that?

Take care

Marcus