0

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
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
Lopeker
  • 33
  • 8
  • Definitely is - rarely a need to select or activate anything. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Darren Bartrup-Cook May 25 '22 at 14:20

1 Answers1

0

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.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • Thanks for the help! This definitely looks much cleaner. Unfortunately now I am not getting any text copied into the cell on the "testing" sheet after this line (everything else seems to work just fine): Sheets("testing").Cells(2, 6) = ra.Offset(3, 0) calling this line to test does write to the cell specified: Sheets("testing").Cells(3, 3) = "Test" So I'm guessing the issues lies with this part somewhere: ra.Offset(3,0) – Lopeker May 25 '22 at 17:16
  • 1
    I check my code again and aparenty the formatting of my starting file changes the offset in this instance from 3 down to 2 down (the searched cell is actually a 2x7 range merged as a single cell somehow) Thanks again for the help solving this! – Lopeker May 25 '22 at 17:32