Range Object with Find and Offset

  • Thread starter Thread starter Ctal
  • Start date Start date
C

Ctal

I'm working on a project that in essence will update many workbooks
using one as a template. One of the updates requires me to use data in
the 'old' book if available else use data from the template book. I'm
attempting to use find for a range and then offset from there to
populate some variables. Here's my code, I'm getting an 'object
doesn't support this method' error. Thanks in advance for any advice.
Note: the workbooks are global in scope as are any other variables not
defined in the sub.

Sub FixTab2()

Dim strT2Headings As String, strT2Value As String
Dim intT2Count As Integer
Dim Rng As Range
Dim wsSheet As Worksheet


wbWorking.SaveAs "P:\TestMosOld\" & Right(strSheet, 4) & "\" &
Right(strSheet, 4) & wbWorking.Name
wbTemplate.SaveAs Filename:=strCurPath


strCompany = wbWorking.Sheets(1).Range("COMPANY").Value
strSeries = wbWorking.Sheets(1).Range("SERIES").Value


For Each wsSheet In wbWorking.Worksheets

If wsSheet.Name Like "*tbl_type" Then
strSheet2 = wsSheet.Name
End If

Next

For intT2Count = 0 To 27

strT2Headings =
wbTemplate.Sheets(2).Range("A11").Offset(0,intT2Count).Value

Set Rng
=wbWorking.Sheets(strSheet2).Range("A11:IV11").Find(what:=strT2Headings)

If Not Rng Is Nothing Then

'Error occurs here

strT2Value = wbWorking.Sheets(strSheet2).Rng.Offset(1, 0).Value


wbTemplate.Sheets(2).Range("A11").Offset(1, intT2Count).Value =
strT2Value

End If

Next intT2Count

Set Rng = Nothing
wbTemplate.Sheets(2).Name = strSheet2

End Sub
 
Haven't tested it myself, but try

strT2Value = Rng.Offset(1, 0).Value

Rng already knows the book and sheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top