Set a range object with variables and offset from an activecell

Question

Can someone pls point out my error.

I'm trying to set a range object to be comprised of a Range1 and an Offset from Range1. There is a table of values I will be passing from into; hence the variables in the range object variable.

Original Error:Run-time Error '1004' Method "Range" of object_global failed. Essentially I'm looking for (example) result: Range("A2:B94") where A is known, 2-is unknown, B94 is offset n-columns and n-rows from A2.

Below is a revised sub using resize method, yet it still has an error.

Sub comptst()
Dim line0 As Long, nrow0 As Long, ncol0 as long, diff0 As Long
Dim k As Integer
Dim rng0 As Range

Application.DisplayAlerts = False 'turns off alert display for deleting files sub-routine

For k = 6 To Sheets.Count 'tst_1 is indexed at position 5.
ThisWorkbook.Sheets(k).Activate
    Set fsobj = New Scripting.FileSystemObject
    If Not fsobj.FileExists(Range("A1")) Then MsgBox "File is missing on sheet index-" & k
Cells(1, 1).Select 'find starting row number
    Do
        If ActiveCell.Value = "Latticed" Then
            b0 = ActiveCell.row 'starting row position
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Select
        End If
        DoEvents '
        
    Loop
    
    
Cells(4, 1).Select
Do Until ActiveCell.row = b0
line0 = ActiveCell.Value
nrow0 = ActiveCell.Offset(0, 1).Value
ncol0 = ActiveCell.Offset(0, 2).Value - 1
diff0 = b0 - 1

Set rng0 = ThisWorkbook.Sheets(k).Cells(line0 + diff0, 1).Resize(nrow0, ncol0)
diff0 = diff0 - 1
Debug.Print rng0.Address
ActiveCell.Offset(1, 0).Select
DoEvents
Loop

Next k
End Sub

Not without more information. What are the values for the variables used in the set statement? What are you expecting and what are you getting? What is the error? — Olle Sjögren, Jun 16 '22 at 13:23
What error do you have now? If it is a runtime error: Which row throws it? — FunThomas, Jun 16 '22 at 14:47
And also: Have a look to https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba to avoid all the select-statements. — FunThomas, Jun 16 '22 at 14:48

score 2 · Accepted Answer · answered Jun 16 '22 at 13:28

You can use the Range.Resize-method. Also, it is often easier to use the Cells-property when coding as it takes numeric parameters for row and column.

Also, you should always specify on which sheet you are working, else VBA will use the ActiveSheet and that may or may not be the sheet you want to work with.

You have some issues with your variable declarations:
o Dim line0, nrow0, ncol0 As Double will declare line0 and nrow0 as Variant and only ncol0 as Double. You need to specify the type for every variable.
o You should declare all variables the deal with row or column numbers as Long. Integer may give you an overflow and Double makes not much sense as the numbers have never a fraction part.

If I understand your code correctly you could use

Dim line0 as Long, nrow0 as Long, ncol0 As Long, diff0 As Long
With ThisWorkbook.Sheets(1)   ' <-- Replace with the book & sheet you want to work with
    Set rng0 = .Cells(line0 + diff0, 1).resize(nrow0, ncol0)
End With

I was able to resolve the problem by removing the .select statements and used for loops with the resize method. Thanks. — D4N, Jun 16 '22 at 17:00

Set a range object with variables and offset from an activecell

1 Answers1