vba lookup cell value on another worksheet and rename value?

Question

How to get the value from a cell on an active sheet and look it up on a non active sheet and then rename the value?

Dim rw As Long

    rw = ActiveCell.Row
    If Sheets("Home").Range("D" & rw).Value = "Tender" Then
        With Worksheets("Time Allocation").Columns("B:B")
            Set cell = .Find(What:=.Range("B" & rw).Value, After:=Range("B" & rw), LookIn:=xlFormulas, _
                            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                            MatchCase:=False, SearchFormat:=False)
            If Not cell Is Nothing Then
                cell.Value = "test"
            Else
                cell.Value = "test"
            End If
        End With
    End If

I have tried using cell.value = "test" but this causes an error: object variable or block with variable not set

please can someone show me where I am going wrong?

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

The bad news is that you cannot .Select one or more cells on an inactive worksheet. The good news is that there is absolutely no requirement that you do so and in fact it is generally less efficient than directly addressing the cell, cells or column(s).

Dim rw As Long, cell as range

rw = ActiveCell.Row
If Sheets("Sheet1").Range("D" & rw).Value = "Tender" Then
    With Worksheets("Sheet2").Columns("B:B")
        Set cell = .Find(What:=Sheets("Sheet1").Range("B" & rw).Value, LookIn:=xlFormulas, _
                        LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Not cell Is Nothing Then
            cell = "test"  '<~~the default property is the .Value
        Else
            MsgBox "cannot test. not found. cell is nothing and cannot be referenced."
        End If
    End With
End If

The way you are bouncing around between two worksheets and referring to the ActiveCell property like it is on one worksheet sometimes and another worksheet other times is a little confusing. I'm not sute I got the What parameter right in the Range.Find method.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

edited May 23 '17 at 11:58

Community

1
1

answered Sep 10 '15 at 11:14

thanks for this it works great, please could I ask how I would be able to now change the value found in the cell once its found what I need? please see updated question – Mark harris Sep 10 '15 at 11:35
You may be trying to set `cell` when `cell` is nothing (aka not found). As mentioned, I am not sure I got the search term parameter correct. If you think it should be found, that is the place to start looking. Right not it is set for the cell in the Sheet2's column B that is the same row as the `rw` assigned with Sheet1's `ActiveCell.Row`. – Sep 10 '15 at 11:41
thanks but I can't figure out what I've done wrong now, all I keep getting is a type mismatch error and I've even tried reverting back to the original code you gave me which was working like 5 minutes ago but now it just keeps giving this error :/ – Mark harris Sep 10 '15 at 12:02
Where *exactly* is the term you are searching for coming from? – Sep 10 '15 at 12:06
My criteria value is on sheet 1 in column b and is a number with the cell format as general. where I want to lookup the value this cell will always be a number in column b on sheet 2. The format of all cells in column b on sheet 2 are also general. I have no idea why I'm getting the error? – Mark harris Sep 10 '15 at 12:09

vba lookup cell value on another worksheet and rename value?

1 Answers1