Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunTime Error 91 (Leo Asked)
Dear Experts,
I have a worksheet with full of formulas, whose data parts need to be filled by other users , and then I have to transfer this data to my worksheet. I created the below Sub to copy all data , except formula cells, to my original sheet at once, without selecting data parts and copy them one by one. '==== Sub CopyValues() Dim SourceSheet As Worksheet Dim TargetSheet As Worksheet Dim TargetRange As Range Dim c As Range Dim stAddress As String 'target sheet is selected by user Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!", Title:="Target?", Type:=8) Set TargetSheet = TargetRange.Parent For Each c In SourceSheet.Range("A1:X2000") If Not c.HasFormula Then stAddress = c.Address TargetSheet.Range(stAddress).Value = c.Value End If Next c End Sub '==== on the line of setting TargetSheet, I encountered error # 91, which I do not know the reason. Could you please tell me what is wrong, and how to correct??? -- Thans & Best regards Leo, InfoSeeker |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunTime Error 91 (Leo Asked)
Hi Leo,
When the user gets the InputBox asking to select a sheet as the prompt says "Go to Target WorkSheet!" all he needs to do is select a sheet he can do this with out selecting any range which will cause 2 problems 1. If you chose 8 for the type argument of InputBox method then it has to reurn a valid Range. 2. If it doesn't return a range then the return value i.e. TargetRange is not set to a Range than when you want to Set TargetSheet = TargetRange.Parent it causes an error because the Parent can't be a sheet if the TargetRange is Nothing. Also you have to keep in mind that the user can choose to Cancel the InputBox and that will cause a error. Ideally you should create a UserForm for this but, if you want to use InputBox you can refrase the Prompt as Go to Target Sheet and select a range, also in case the user cancels the InputBox you should put On Error Resume Next before Setting TargetRange, after setting TargetRange you should check that TargetRange is NOT Nothing before Setting TargetSheet as follows: On Error Resume Next Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet, And Select A Cell!",Title:="Target?", Type:=8) If NOT TargetRange Is Nothing Then Set TargetSheet = TargetRange.Parent Rest of the code.... Else MsgBox You have not selected any cells End If Another aproach can be that instead of using Type 8 use Type 2 Which lets the user enter a Text, so the user can enter the name of a sheet and do away with TargetRange, as follows: Dim ShtName As String ShtName = Application.InputBox(Prompt:="Go to Target WorkSheet!",Title:="Target?", Type:=2) Set TargetSheet = ThisWorkbook.Worksheets(ShtName) Of course if you use this method you have to provide error handling in case the user enters text which doesn't correspond to ant sheet or as before the user cancels Hope I didn't drag on to much and, hope this helps -- A. Ch. Eirinberg "Leo" wrote: Dear Experts, I have a worksheet with full of formulas, whose data parts need to be filled by other users , and then I have to transfer this data to my worksheet. I created the below Sub to copy all data , except formula cells, to my original sheet at once, without selecting data parts and copy them one by one. '==== Sub CopyValues() Dim SourceSheet As Worksheet Dim TargetSheet As Worksheet Dim TargetRange As Range Dim c As Range Dim stAddress As String 'target sheet is selected by user Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!", Title:="Target?", Type:=8) Set TargetSheet = TargetRange.Parent For Each c In SourceSheet.Range("A1:X2000") If Not c.HasFormula Then stAddress = c.Address TargetSheet.Range(stAddress).Value = c.Value End If Next c End Sub '==== on the line of setting TargetSheet, I encountered error # 91, which I do not know the reason. Could you please tell me what is wrong, and how to correct??? -- Thans & Best regards Leo, InfoSeeker |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunTime Error 91 (Leo Asked)
To ask for the destination sheet.
Sub copynonformulas() Dim mysheet As String Dim c As Range mysheet = InputBox("which sheet") For Each c In Range("c7:c11") If Not c.HasFormula Then Sheets(mysheet).Range(c.Address).Value = c.Value End If Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Adapt this simplified version to your needs. Option Explicit Sub copynonformulas() Dim c As Range For Each c In Range("c7:c11") If Not c.HasFormula Then Sheets("sheet2").Range(c.Address).Value = c.Value End If Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Leo" wrote in message ... Dear Experts, I have a worksheet with full of formulas, whose data parts need to be filled by other users , and then I have to transfer this data to my worksheet. I created the below Sub to copy all data , except formula cells, to my original sheet at once, without selecting data parts and copy them one by one. '==== Sub CopyValues() Dim SourceSheet As Worksheet Dim TargetSheet As Worksheet Dim TargetRange As Range Dim c As Range Dim stAddress As String 'target sheet is selected by user Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!", Title:="Target?", Type:=8) Set TargetSheet = TargetRange.Parent For Each c In SourceSheet.Range("A1:X2000") If Not c.HasFormula Then stAddress = c.Address TargetSheet.Range(stAddress).Value = c.Value End If Next c End Sub '==== on the line of setting TargetSheet, I encountered error # 91, which I do not know the reason. Could you please tell me what is wrong, and how to correct??? -- Thans & Best regards Leo, InfoSeeker |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RunTime Error 91 (Leo Asked)
thanks a lot,
I suppose the second one suits my purpose, where typing the name of sheet is asked and less headache on error handling. -- Thans & Best regards Leo, InfoSeeker "Howard31" wrote: Hi Leo, When the user gets the InputBox asking to select a sheet as the prompt says "Go to Target WorkSheet!" all he needs to do is select a sheet he can do this with out selecting any range which will cause 2 problems 1. If you chose 8 for the type argument of InputBox method then it has to reurn a valid Range. 2. If it doesn't return a range then the return value i.e. TargetRange is not set to a Range than when you want to Set TargetSheet = TargetRange.Parent it causes an error because the Parent can't be a sheet if the TargetRange is Nothing. Also you have to keep in mind that the user can choose to Cancel the InputBox and that will cause a error. Ideally you should create a UserForm for this but, if you want to use InputBox you can refrase the Prompt as Go to Target Sheet and select a range, also in case the user cancels the InputBox you should put On Error Resume Next before Setting TargetRange, after setting TargetRange you should check that TargetRange is NOT Nothing before Setting TargetSheet as follows: On Error Resume Next Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet, And Select A Cell!",Title:="Target?", Type:=8) If NOT TargetRange Is Nothing Then Set TargetSheet = TargetRange.Parent Rest of the code.... Else MsgBox You have not selected any cells End If Another aproach can be that instead of using Type 8 use Type 2 Which lets the user enter a Text, so the user can enter the name of a sheet and do away with TargetRange, as follows: Dim ShtName As String ShtName = Application.InputBox(Prompt:="Go to Target WorkSheet!",Title:="Target?", Type:=2) Set TargetSheet = ThisWorkbook.Worksheets(ShtName) Of course if you use this method you have to provide error handling in case the user enters text which doesn't correspond to ant sheet or as before the user cancels Hope I didn't drag on to much and, hope this helps -- A. Ch. Eirinberg "Leo" wrote: Dear Experts, I have a worksheet with full of formulas, whose data parts need to be filled by other users , and then I have to transfer this data to my worksheet. I created the below Sub to copy all data , except formula cells, to my original sheet at once, without selecting data parts and copy them one by one. '==== Sub CopyValues() Dim SourceSheet As Worksheet Dim TargetSheet As Worksheet Dim TargetRange As Range Dim c As Range Dim stAddress As String 'target sheet is selected by user Set TargetRange = Application.InputBox(Prompt:="Go to Target WorkSheet!", Title:="Target?", Type:=8) Set TargetSheet = TargetRange.Parent For Each c In SourceSheet.Range("A1:X2000") If Not c.HasFormula Then stAddress = c.Address TargetSheet.Range(stAddress).Value = c.Value End If Next c End Sub '==== on the line of setting TargetSheet, I encountered error # 91, which I do not know the reason. Could you please tell me what is wrong, and how to correct??? -- Thans & Best regards Leo, InfoSeeker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
runtime error '1004' application or object defined error | Excel Programming | |||
runtime error 13 - type mismatch error in Excel 97 on Citrix | Excel Programming | |||
runtime error '1004' application or object defined error. Please help | Excel Programming | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
Syntax Error Runtime Error '424' Object Required | Excel Programming |