Copy a range to a variable, then offset that range variable and save the value to another variable-CodePudding

So I am trying to search for a cell range with a specific string, then copy the contents of the cell 3 spaces under it to save to another spreadsheet.

The code I have so far does this, but it seems clumsy to need to select the cell then offset it with variableName.Select then ActiveCell.Offset(blahblah).

Is it possible to offset to (and pull the value from) the desired cell by calling variableName.Offset(3,0).Value or something of that nature to clean up the code?

Here is the code I have so far, and thanks in advance for your help!

    Dim ra As Range

Sheets("starting").Activate
    
Set ra = Cells.Find(What:="Product Name", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
'finds correct cell
  
  If ra Is Nothing Then
        MsgBox ("Search Error: Not found")
        Else
        MsgBox (ra.Address) ' to test
    End If

Dim foundCell

ra.Select

ActiveCell.Offset(3, 0).Select

'foundCell = Cells(ra).Offset(0, 3) '.Address '.value        '   <<< not grabbing the data

foundCell = ActiveCell.Value


MsgBox foundCell   'shows the value of the desired cell now! (to test)

Sheets("testing").Activate

Sheets("testing").Cells(2, "F").Value = foundCell      '2,"F" will be replaced by a range stored in a variable

CodePudding user response：

This will do the same thing:

Sub Test()

    Dim ra As Range
    Set ra = Sheets("starting").Cells.Find(What:="Product Name", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    If ra Is Nothing Then
        MsgBox ("Search Error: Not found")
    Else
        MsgBox (ra.Address) ' to test
        Sheets("testing").Cells(2, 6) = ra.Offset(3, 0)
    End If

End Sub

I've put the copying it into the testing sheet within the If ra Is Nothing test.

As your code stands it would display the message "Search Error: Not found" and then try offset three rows from the value it didn't find giving an Object variable or With block variable not set error.