将范围复制到一个变量，然后偏移该范围变量并将值保存到另一个变量

Question

所以我试图搜索具有特定字符串的单元格范围，然后复制单元格3空格下的内容以保存到另一个电子表格。

到目前为止，我的代码做到了这一点，但似乎笨拙的需要选择单元格，然后用variableName偏移它。选择activeccell . offset (blahblah)

是否可以通过调用variableName.Offset(3,0)来偏移到所需的单元格(并从中提取值)。值或其他性质的东西来清理代码?

这里是我到目前为止的代码，并提前感谢您的帮助!

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

Answer 1

效果相同:

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  

我已经把它复制到If ra Is Nothing测试中的testing页。

按照你的代码，它会显示消息"Search Error: Not found">然后尝试从没有找到Object variable or With block variable not set错误的值偏移三行。