Now I'm making a function to copy data in an arbitrary range with VBA.
Below is the program.
Function Copy function ()
Dim input_rg As Range
Dim output_rg As Range
Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)
Set output_rg = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8)
If input_rg = False Or output_rg = False Then
End
End If
input_rg.Copy Destination: = output_rg
End Function
If i do this, you will get a "type mismatch" error.
The location is [Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8)].
Receiving this error message
I did as follows.
1. I checked the cell address and type in the Immediate window.
? input_rg.address
$C $3: $F $3
? typename (input_rg)
Range
? typename (output_rg)
Range
? output_rg.address
$C $10: $F $10
2. I changed the type from range type to Variant type, but it spits out the same error.
Thanks for your response.
Receiving an answer
First of all, thanks for your answer. I'm wondering whether to write in the comments, but I'll write here because it's going to be long.
After getting the answer, it was executed again, without any errors.
However, if cancel is selected in theinputbox
dialog box ofapplication
, the error [Type does not match] will be spit out again.
After examining it, the return value of inputbox cancellation seems to be False.
I'm sorry to get your answer, but I would be happy if you could answer again based on this point.
-
Answer # 1
-
Answer # 2
I thought about how to determine cancellation with an expected error.Function Copy function () Dim input_rg As Range, output_rg As Range On Error GoTo myErr Set input_rg = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8) Set output_rg = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8) On Error GoTo 0 Answer from 'hatena19 If input_rg Is Nothing Or output_rg Is Nothing Then Exit Function End If input_rg.Copy Destination: = output_rg myErr: If Err.Number = 424 Then MsgBox "Cancelled" Err.Clear Else Err.Raise Err.Number End If End Function
Since the return value of Inputbox cancellation is False, an error occurs in the Range type.
You may not like it, but if you use Variant type, there is a way to judge cancellation.Function Copy function () 'Variant type for cancel decision Dim input_vt As Variant, output_vt As Variant Set input_vt = Application.InputBox ("Select a cell to copy", Title: = "Input form", Type: = 8) Set output_vt = Application.InputBox ("Please select the paste destination", Title: = "Output form", Type: = 8) 'Decision of cancellation If VarType (input_vt) = vbBoolean Or VarType (output_vt) = vbBoolean Then Exit Function End If Answer from 'hatena19 If input_vt Is Nothing Or output_vt Is Nothing Then Exit Function End If input_vt.Copy Destination: = output_vt End Function
Related articles
- vba - about index error
- vba - syntax error: multiple area selection
- [vba] an error occurs when trying to save a recordset to adodbstream
- i get a compile error for a program that uses a pointer
- ruby - about error of update action when nesting
- vba - about the use of wildcards in conditional branching (if)
- eclipse - about specifying @multipartconfig (location = "")
- about ie operation pull-down and td tag in vba
- ruby on rails 5 - about lint/syntax error of rubocop
- mvc - about the description location of rails count method
- vba - about id number when registering a record from the access form
- ruby - about the error content displayed in "rails" rspec
- about unpickling error in python socket communication
- python - about the error cannot reshape array of size
- python - about openpyxl error avoidance
- i get a compile error during vba processing
- parameters - about error when calculating python sarimx parameter optimization
- ruby on rails 6 - about rails error "wrong number of arguments (given 1, expected 0)"
- about management screen error in wordpress version 55
- duplicate key error when trying to add to dictionary in vba
- vba - i want to make a copy (2 places) between books
- vba error handling index is not in the valid range
- how to resolve vba error messages (object required)
- vba - i want to select multiple csv files by pressing the "import" button and display each data vertically in excel
- vba - task table: i want to get the number of statuses for each task priority
- vba - i want to extract arbitrary values using worksheet functions (index and match functions)
- i want to find the date in the combo box with the find method using excel vba
- i want to copy a specific sheet to another file with vba
- vba - fetch the row with the maximum value among multiple conditions
- questions about excel vba
Is there an error, is it really there? I tested it and it passed without error.
but
An error occurred.
Use Is Nothing to determine whether an object exists.
Code corresponding to cancellationIt was possible to execute without error if corrected below.
Since it is a Function, I tried to make it a specification that returns whether copying was successful.
BonusIn the question code,
End
tries to end the function, butExit Function
usually leaves the function.On the other hand,
End
will completely terminate the running macro, butExit Function
will execute the next code if it exits from the function . You should be aware of the differences and use them properly.Reference code
Immediate window