.Find Malfunctioning, Object Issue

**Centexcel** · 10-02-2013, 01:39 PM

CopyWorksheet.xls
PPU.xlsx.xlsm

Excel Forum,

I am having what I think are object variable issues and I would like to learn why my macros break down when I paste them from my personal workbook into their respective workbooks. Part of this macro worked in my personal workbook.
Attached are 2 workbooks. In the PPU workbook, my macro executes well until it comes across the .find function. I have tried altering my object variables and format type (adding or taking away ' before the search numbers).

Why is my .find method not working?

If you need further context:
I am trying to fill out the PPU worksheet by playing the macro, selecting each file I need, then using the macro to copy and paste the data.

Thank you for your help. This forum is invaluable and one day I hope my skills are good enough to help others.

**MarvinP** · 10-02-2013, 01:48 PM

Hi,

I opened your PPU workbook and find your code behind a worksheet instead of in a module. This raises a red flag for me in terms of SCOPE (not the mouthwash). My general rule is that code behind a worksheet only works on that worksheet. If you want to pull stuff from another sheet or workbook you should have your code in a Module that can see other sheets or workbooks.
Read http://www.ozgrid.com/VBA/variable-scope-lifetime.htm
for more info on the topic.

Scope of VBA is like creating a variable using the Dim statement in a subroutine and trying to use it in another. The variable is destroyed as soon as the sub ends, unless you declare it as global.....

If I'm way off base, keep asking and we can read the code but having your VBA behind a worksheet instead of behind a module is my first guess of the problem.

**Centexcel** · 10-02-2013, 01:57 PM

Marvin,

I see your point and yes, you are correct. I am self taught and make a lot of simple errors like this.

However, as I run the code in a module, it breaks down when I am setting my variable. For example everything looks good in my watch window until I try to set my "advert" variable. I get an object required run-time error. What troubles me is this method executes in other modules just fine and I don't know why the object would change when I am setting the variables. Any ideas?

Thank you for your time. Now I will be more mindful to place my code in modules where they belong.

**Norie** · 10-02-2013, 01:58 PM

As far as I can see you've implemented the Find properly,

It's the code following it that doesn't look right.

For example what are you doing here?

            keyRow = keyRow
            Advert = (.Cells(keyRow.Row, 14).Value)

That first line is going to set keyRow to a Double variable, but on the next line you try to refer to it as if it's a range.

What is the first line supposed to do?

If I remove every instance of it in the code it runs without error until it reaches this section where the formulas fail as a lot of the variables are empty, some of them haven't been declared either, eg MakeRdy.

    .Cells(iRow + 1, 1).Value = Advert
        .Cells(iRow + 1, 2).Value = Admin
        .Cells(iRow + 1, 3).Value = "=" & Maint & "-" & MakeRdy & "-" & contSvc1 & "-" & contSvc2
        .Cells(iRow + 1, 4).Value = MakeRdy
        .Cells(iRow + 1, 5).Value = "=" & contSvc1 & "+" & contSvc2
        .Cells(iRow + 1, 6).Value = PayRoll
        .Cells(iRow + 1, 7).Value = Mgmt
        .Cells(iRow + 1, 8).Value = Utl
        .Cells(iRow + 1, 9).Value = iNS
        .Cells(iRow + 1, 10).Value = "=" & tAX & "-" & iNS
        .Cells(iRow + 1, 11).Value = Repairs
        .Cells(iRow + 1, 12).Value = Improvements
        .Cells(iRow + 1, 14).Value = rev

PS You should add Option Explicit at the top of the code, that should help weed out any undeclared variables.

**Centexcel** · 10-02-2013, 02:09 PM

Norie,

Thank you for your reply.

When I finish a loop I declare my variable again. I am self taught from a book, and the books mentions this is important to make sure the variable doesn't sum as it goes through the loop.
In the second line I am trying to identify the row my search key is on, then return the value in the 14th column of that row.

My apologies for the missing variables, while trouble shooting I cut and pasted portions of code that work from other workbooks.

I ran it again taking out keyrow=keyrow, but I got the same errors. What does your "advert =" line look like?

Thank you for your time.

**Centexcel** · 10-02-2013, 02:28 PM

I got the macro working, but only as a personal workbook module. It still turns up errors when I place the code in other modules.

I'll keep working to sort this out. Thank you for your time and patience with me.

**Norie** · 10-02-2013, 02:31 PM

What do you mean the 'line look like'?

The only thing I changed in the code was removing keyrow = keyrow, which is something you definitely shouldn't have.

Also, what loop and what summing?

As far as I can see there is no loop and there are no calculations, summing or otherwise.

By the way, why do you have this nine times?

        Set KeyRange = .Range("a1", .Range("a65536").End(xlUp))

KeyRange is never used in the code.

The following code works apart from the formulas which I've commented out, they don't work because the variables are empty.

Option Explicit

Sub HTMacro()
Dim KeyRow As Range
Dim KeyRange As Range

Dim Advert As Long, Admin As Long, Maint As Long, PayRoll As Long, Mgmt As Long
Dim Utl As Long, TaxIns As Long, Repairs As Long, Improve As Long, rev As Long
Dim ParentWB As String

Dim NewFN As Variant
Dim wbNew As Workbook
Dim nProperties As Integer, nExpItem As Integer, iRow As Integer, iCol As Integer
Dim MakeRdy As Variant
Dim contSvc1 As Variant
Dim contSvc2 As Variant
Dim iNS As Variant
Dim tAX As Variant
Dim Improvements As Variant
Dim nMonths As Variant
Dim NOI As Variant

    Application.ScreenUpdating = False

    ParentWB = ActiveWorkbook.Name

    NewFN = Application.GetOpenFilename(Title:="Please select a file")
    If NewFN = False Then
        ' They pressed Cancel
        MsgBox "Stopping because you did not select a file"
        Exit Sub
    Else
        Set wbNew = Workbooks.Open(Filename:=NewFN)
    End If


    With wbNew.Sheets("12 mo inc det")
        .Unprotect
        .Columns("A:B").EntireColumn.Hidden = False
        .Range("ah12").Value = "1"
        .Range("ah12").Copy
        .Columns("a:a").PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply

        'Find function does not find requested data************************************************************************
        'Note no period before columns and cells

        Set KeyRow = Columns(1).Find(what:="5199", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Advert = (.Cells(KeyRow.Row, 14).Value)
        End If
        ' period in front of columns and cells

        Set KeyRow = .Columns(1).Find(what:="5299", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Admin = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="5699", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Maint = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="5799", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            PayRoll = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="5899", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Mgmt = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="5999", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Utl = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="7999", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Repairs = (.Cells(KeyRow.Row, 14).Value)
        End If

        Set KeyRow = .Columns(1).Find(what:="8995", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            Improve = (.Cells(KeyRow.Row, 14).Value)
        End If



        Set KeyRow = .Columns(1).Find(what:="8999", LookIn:=xlValues, lookat:=xlPart)
        If KeyRow Is Nothing Then
            MsgBox "not found"
        Else
            rev = (.Cells(KeyRow.Row, 14).Value)
        End If

    End With


    Windows(ParentWB).Activate


    'Advert = 55555
    'Admin = 8888
    With Range("A3")
        'nExpItem = Range(.Offset(3, 3), .Offset(3, 3).End(xlToRight)).Columns.Count
        'nProperties = Range(.Offset(4, 1), .Offset(4, 1).End(xlDown)).Columns.Select
        iRow = Range(.Offset(2, 3), .Offset(2, 3).End(xlDown)).Rows.Count
    End With

    With DataInput.Range("D5")

        .Cells(iRow + 1, 1).Value = Advert
        .Cells(iRow + 1, 2).Value = Admin
       ' .Cells(iRow + 1, 3).Value = "=" & Maint & "-" & MakeRdy & "-" & contSvc1 & "-" & contSvc2
        .Cells(iRow + 1, 4).Value = MakeRdy
      '  .Cells(iRow + 1, 5).Value = "=" & contSvc1 & "+" & contSvc2
        .Cells(iRow + 1, 6).Value = PayRoll
        .Cells(iRow + 1, 7).Value = Mgmt
        .Cells(iRow + 1, 8).Value = Utl
        .Cells(iRow + 1, 9).Value = iNS
       ' .Cells(iRow + 1, 10).Value = "=" & tAX & "-" & iNS
        .Cells(iRow + 1, 11).Value = Repairs
        .Cells(iRow + 1, 12).Value = Improvements
        .Cells(iRow + 1, 14).Value = rev
        ' Check Cells
        If Abs(.Cells(21, nMonths + 2) - NOI) > 2 Then MsgBox "Check Error"

    End With

   'more action here
    wbNew.Close savechanges:=False    'close with changes
    Set wbNew = Nothing

End Sub