Range Object with Find and Offset

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)


"Ctal" <[email protected]> wrote in message
news:[email protected]...
> 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
>