Need the Cell Location for Formula

[Borg7]

Hi,

I am in the sheet "Line Expenses" iterating through the rows and finding an account... I then take that value and look it up in the "Payroll Entry" Sheet... when it's found, say A30, i have it move over to to Cell, for example K30... I need that location "K30" and throw that into my formula back in the
"Line Expenses" to get = "='APRIL Payroll Entry'!" & oCell1 & ""...

I am getting no where, type mismatch, object required... oh and the ocell.address never works...

Please help

John

Set oBook = ThisWorkbook
    Set oSheet = oBook.Worksheets("APRIL Line Expenses")
 
 For iRow = 25 To 744
 
'_________________________
        'Column C
        'Only some of the accounts on the Line expense reside in the Payroll tab
        'if they do then I want the value in the payroll tab to be here
    If Mid(oSheet.Cells(iRow, 1), 4, 1) = "-" Then
        Set oCell = oSheet.Cells(iRow, 1)
        sCell = oCell.Value
 
            'now that i have an account I am going to look up and see if it
            'exists in this worksheet
            Set oSheet1 = oBook.Worksheets("APRIL Payroll Entry")
                Set oCell1 = Cells.Find(what:=sCell, After:=ActiveCell, lookin:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
                Set oCell2 = oCell1.Offset(0, 10)
                    oCell2.Select
                Set oCell3 = oCell2.Address(RowAbsolute:=True, ColumnAbsolute:=True)
               
'oCell2.AddressLocal(RowAbsolute:=False, ColumnAbsolute:=False)
'Set oCell1 = oSheet1.Range("A13", Range("A13").End(xlDown)).Find(what:=sCell, lookin:=xlValues)
'oCell1.Offset(0, 10).Address(RowAbsolute:=False, ColumnAbsolute:=False)
 
'I find the account form the Line expense tab in the Payroll tab
'I want to get its address i.e. K15 and use that for the forumula
                        
 
 
        'Want column K  to be the value in Column C of the April Line Expenses
        'sCell2 = oCell2.Offset(0, 10)
        
        'oCell.Offset(0, 2).Activate = "='APRIL Payroll Entry'!" & oCell1 & ""
        
    End If
Next iRow

[BrianB]

I found your code very difficult to read and understand, as well as there being a couple of minor errors. In the end I decided a rewrite in a more generic format was best so you can adapt it to what you need. This sort of code can be used over and over again.

As our macros become more complicated it is more necessary to have meaningful variable names and be more explicit about defining our variables eg. giving the full information -
WorkBooks("Book1.xls").Worksheets("Sheet1").Range(A1")

Hope this helps.

'=================================================
'- FIND USING A LOOP
'- TRANSFER DATA AS VALUE AND CELL REFERENCE
'=================================================
Sub TransferData3()
    Dim ToSheet As Worksheet
    Dim ToRow As Long
    Dim FromSheet As Worksheet
    Dim FromRow As Long
    Dim FindValue As String   ' value to find
    Dim LastRow As Long
    Dim FoundCell As Range
    Dim FoundCell3 As Range
    Dim FoundRef3 As String
    '------------------------------------------------
    ' initialise
    Set ToSheet = ThisWorkbook.Worksheets("Summary")
    LastRow = ToSheet.Range("A65536").End(xlUp).Row
    Set FromSheet = ThisWorkbook.Worksheets("DataSheet")
    '------------------------------------------------
    '- main loop
    For ToRow = 2 To LastRow
        FindValue = ToSheet.Cells(ToRow, 1).Value
        '- Find
        Set FoundCell = _
            FromSheet.Columns(1).Find(FindValue, LookIn:=xlValues)
        If FoundCell Is Nothing Then
            ToSheet.Cells(ToRow, 2).Value = "not found"
        Else
            FromRow = FoundCell.Row
            '-----------------------------------------------
            '- transfer as value
            ToSheet.Cells(ToRow, 2).Value = _
                FromSheet.Cells(FromRow, 2).Value
            '------------------------------------------------
            '- transfer as cell reference
            Set FoundCell3 = FromSheet.Cells(FromRow, 3)
            FoundRef3 = "='" & FromSheet.Name & "'!"
            FoundRef3 = FoundRef3 & FoundCell3.Address _
                    (RowAbsolute:=True, ColumnAbsolute:=True)
            ToSheet.Cells(ToRow, 3).Value = FoundRef3
            '-------------------------------------------------
        End If
    Next
    '--------------------------------------------------------------
    MsgBox ("Done.")
End Sub