Excel ba error 91

housemd

New Member
Joined
Sep 3, 2014
Messages
10
Hi,

I am trying to write a vba code which copies stuff from a given set of ranges and pastes them in other ranges

The code is as follows:

Code:
Option Explicit

Sub finding()
Dim i, j As Integer
Dim re As Range
Dim rw As Range

Dim range1, range2, range3, range4, range5, range6, range7, range8 As Range
Dim rangww1, rangww2, rangww3, rangww4, rangww5, rangww6, rangww7, rangww8 As Range

Set range1 = Sheet2.Range("A1:A100").Find(What:="Baseline", LookIn:=xlValues)
Set range2 = Sheet2.Range("A1:A100").Find(What:="1Y3M", LookIn:=xlValues)
Set range3 = Sheet2.Range("A1:A100").Find(What:="1Y6M", LookIn:=xlValues)
Set range4 = Sheet2.Range("A1:A100").Find(What:="1Y9M", LookIn:=xlValues)
Set range5 = Sheet2.Range("A1:A100").Find(What:="1Y12M", LookIn:=xlValues)
Set range6 = Sheet2.Range("A1:A100").Find(What:="2Y3M", LookIn:=xlValues)
Set range7 = Sheet2.Range("A1:A100").Find(What:="2Y6M", LookIn:=xlValues)
Set range8 = Sheet2.Range("A1:A100").Find(What:="2Y9M", LookIn:=xlValues)
 
 rangww1 = Sheet1.Range("E10")
 rangww2 = Sheet1.Range("E43")
 rangww3 = Sheet1.Range("E76")
 rangww4 = Sheet1.Range("E109")
 rangww5 = Sheet1.Range("E142")
 rangww6 = Sheet1.Range("E175")
 rangww7 = Sheet1.Range("E208")
 
 

For j = 1 To 8
 re = "range" & j
 rw = "rangww" & j
 
    If Not re Is Nothing Then
        For i = 0 To 3
            Sheet2.Activate
            Range(re.Offset(i, 2), re.Offset(i, 7)).copy
            Sheet1.Activate
            Range("rw").Offset(i * 7, 3).Activate
            Selection.PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Next i
    End If
Next j


 End Sub

But I am getting an error at 're' creation saying error 91: object variable or with variable not set.

Kindly help me as to what is wrong with the code?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Welcome to MrExcel.

You can't do this:

Code:
re = "range" & j
rw = "rangww" & j

References to variables must be explicit. Even if it were possible you would need the Set keyword (hence Object variable not set). You can use arrays.
 
Upvote 0
Welcome to MrExcel.

You can't do this:

Code:
re = "range" & j
rw = "rangww" & j

References to variables must be explicit. Even if it were possible you would need the Set keyword (hence Object variable not set). You can use arrays.

You mean to say that I cannot use this inside a for loop?

Also, I have very little knowledge of arrays

Could you help as to how to proceed?
 
Upvote 0
Try:

Code:
Sub finding()
    Dim Arr1 As Variant
    Dim Arr2 As Variant
    Dim j As Long, i As Long
    Arr1 = Array("Baseline", "1Y3M", "1Y6M", "1Y9M", "1Y12M", "2Y3M", "2Y6M", "2Y9M")
    Arr2 = Array("E10", "E43", "E76", "E109", "E142", "E175", "E208", "E241")
    Dim re As Range
    Dim rw As Range
    For j = LBound(Arr1) To UBound(Arr1)
        Set re = Sheet2.Range("A1:A100").Find(What:=Arr1(j), LookIn:=xlValues)
        Set rw = Sheet1.Range(Arr2(j))
        If Not re Is Nothing Then
        For i = 0 To 3
            Range(re.Offset(i, 2), re.Offset(i, 7)).Copy
            rw.Offset(i * 7, 3).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        Next i
        End If
    Next j
 End Sub
 
Upvote 0
worked like a charm
Could you link me to any material where I can learn more about arrays and using them in vba?
That would be really helpful
Thanks a lot for taking time and helping me out :)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top