Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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,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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find last value in a range & then offset | Excel Worksheet Functions | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Nested Loop Offset Glitch - Object required (Error 424) | Excel Programming | |||
Range object to Array object conversion | Excel Programming | |||
Range object to Array object conversion | Excel Programming |