Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert eye needed
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert eye needed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert eye needed
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. -- jb "gootroots" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert eye needed
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" wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expert eye needed
Hi
I got your XL SS and replied with an updated version of your workbook. Did you get that? Take care Marcus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Some expert help needed | Excel Programming | |||
Expert-ish Help needed | Excel Worksheet Functions | |||
expert with formulas needed again | Excel Discussion (Misc queries) | |||
Macro Expert Help needed... | Excel Programming | |||
Expert help needed | Excel Programming |