Need the Cell Location for Formula
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
VB Code:
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
Re: Need the Cell Location for Formula
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.
Code:
'=================================================
'- 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
