Range Object with Find and Offset
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" wrote in message
ups.com...
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,intT2Co unt).Value
Set Rng
=wbWorking.Sheets(strSheet2).Range("A11:IV11").Fin d(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
|