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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

housemd

New Member
Joined
Sep 3, 2014
Messages
10
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

housemd

New Member
Joined
Sep 3, 2014
Messages
10
What should rangww8 be?

My bad,

rangww8=sheet1.range("e241")

Some how only this is giving an error and not till rangeww7. So I removed this from the code. My bad.

The rangww8 is giving same error 91, no idea why
 
Upvote 0

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

housemd

New Member
Joined
Sep 3, 2014
Messages
10
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,191,069
Messages
5,984,451
Members
439,888
Latest member
c3rberus

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
Top