Excel ba error 91

[housemd]

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:

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?

 

[Andrew Poulsom]

Welcome to MrExcel.

You can't do this:

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.

 

[housemd]

Welcome to MrExcel.

You can't do this:

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?

 

[Andrew Poulsom]

What should rangww8 be?

 

[housemd]

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

 

[Andrew Poulsom]

Try:

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

 

[housemd]

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

 

[Andrew Poulsom]

If you Google for arrays vba you will get lots of hits.