Trying to get a cell to equal to a variable location

Question

Ok noob at vba here. I am trying to update a cells formula on sheet 2 to equal to another cell on sheet 1. The location of the cell sheet can and will change. The only constant is the cell with "InsertC' in relation to the cell I need.

Range("O252").Select
ActiveCell.Formula = "=Sheet1!" & Cells.Find(What:="InsertC", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(-2, 8).Address

Current error is

run time error '91' Object variable or with block variable not set

Side note: you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code. In this case `Range("O252").Formula = ...` would do the job just fine (if the find is successful) — cybernetic.nomad, Aug 21 '19 at 19:29
`Cells.Find` will look on the ActiveSheet - if you want to look on some other sheet then you need to specify that explicitly. eg `Sheet1.Cells.Find(...)` — Tim Williams, Aug 21 '19 at 19:32
Thank you for tips. They help me fix my formula to work just had to add sheets.cells you mention. — OfficeNoob007, Aug 21 '19 at 19:42

score 2 · Answer 1 · answered Aug 21 '19 at 19:37

You're cramming too many things into a single executable statement. Split. Things. Up.

Dim ws As Worksheet
Set ws = ActiveSheet 'TODO: get the actual sheet you want to work with.

Dim searchResult As Range
Set searchResult = ws.Cells.Find(What:="InsertC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
'TODO: reconsider whether ActiveCell needs to be involved in the search.

If searchResult Is Nothing Then Exit Sub 'couldn't find what we're looking for; bail out!

Dim offsetCell As Range
On Error Resume Next 'next instruction may raise an error - ignore it
Set offsetCell = searchResult.Offset(-2, 8)
If offsetCell Is Nothing Then Exit Sub 'couldn't legally offset negative rows; bail out!
On Error GoTo 0 'resume raising runtime errors

ws.Range("O252").Formula = "=" & offsetCell.Address(External:=True)

Note the sheet's name doesn't need to be hard-coded in the formula - offsetCell.Address can work it out for you if you specify True for the External parameter.

That will make the formula be e.g. =[Book1]Sheet1!$A$1, but the [Book1] part will be optimized away by Excel after the formula is successfully assigned.

Trying to get a cell to equal to a variable location

1 Answers1