Menu
This is kind of silly, but I've been stuck for a while in this simple statement:
Dim range1 as Range
Dim mysheet as String
Dim myrange as String
mysheet = "Sheet1"
range = "A1:A10"
range1 = Worksheets(mysheet).Range(myrange)
I've testing all the solutions that I've found on the internet as for example this, this and this, but nothing.
All the time it gives me errors: 1004 "Error defined by the application" or "object variable or with not set".
I have tried the following:
range1 = ThisWorkbook.Worksheets(mysheet).Range(myrange)
range1 = ActiveWorkbook.Worksheets(mysheet).Range(myrange)
range1 = Sheets(mysheet).Range(myrange) (and the combinations above)
range1 = Worksheets(mysheet).Range(Cells(1,1), Cells(1,10)) (and the combinations with This/Active workbook)
and
with This/ActiveWorkbook
range1 = .Worksheets(mysheet).Range(myrange)
end with
None have worked.
This is a REALLY silly thing, but I've been stuck for a while now :s
Can anyone help me?
Really thanks in advance.
Best regards,
231
0. You can use the following solution to convert a range to a string in VBA: Sub convert () Dim rng As Range, cell As Range Dim filter As String filter = "" Set rng = Selection For Each cell In rng If Not cell Is Nothing Then filter = """" & cell & """" & "," & filter End If Next cell End Sub. Share.
A range is nothing but a cell or series of cells. In VBA we often refer the word range to select a particular cell or series of cells. While working with excel everything refers to cells & ranges, it is not different in VBA as well. In order to understand the VBA, understanding of the basics of range in VBA is very important.
VBA to Find String in a Cell You can also search for a certain text in a single cell of string and return a certain string. Copy the following code and paste it into the code window. Sub Find_String_in_Cell () If InStr (Range ("B5").Value, "Dr.") > 0 Then Range ("C5").Value = "Doctor" End If End Sub
Using Offset function, you can move the reference from a given Range (cell or group of cells) by the specified number_of_rows, and number_of_columns. To assign a range to a range variable: declare a variable of type Range then use the Set command to set it to a range.
You need to use Set to assign objects:
Set range1 = Worksheets(mysheet).Range(myrange)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
