0

I'm trying to define a Range using VBA in Excel, 

Sub convertePerc()
Dim separador As String
Dim linhaInicial, linhaFinal, colunaInicial, colunaFinal, numAnos As Integer
Dim origem, destino As Range

    separador = "DRES(G)"

    colunaFinal = Sheets(separador).Cells(6, 5).End(xlToRight).Column
    linhaFinal = 40
    numAnos = 10
    origem = Sheets(separador).Range(Cells(10, 4), Cells(linhaFinal, colunaFinal))

    colunaInicial = CInt(4 + numAnos + 1)
    colunaFinal = CInt(numAnos + colunaFinal + 1)
    destino = Sheets(separador).Range(Cells(10, 4), Cells(11, 5))


End Sub

The first range origem is correctly defined without errors, but the second destino is throwing the error:

Object with block variable not set

On line:

destino = Sheets(separador).Range(Cells(10, 4), Cells(11, 5))

Can someone please explain me why, and how to fix this?

Thanks in advance!

Improve this question
12
  • 3
    Add a set infront? Also origem is declared as variable type variant fyi. Commented Oct 13, 2015 at 16:57
  • 1
    I guess that solves it :-) Commented Oct 13, 2015 at 16:59
  • 2
    Perhaps counter-intuitive, but Dim linhaInicial, linhaFinal, colunaInicial, colunaFinal, numAnos As Integer does not declare all those variables as Integer. Only numAnos is declared as an integer. You have to put the as Integer after each variable name. Without it, they'll be variant types. Commented Oct 13, 2015 at 17:00
  • 1
    Also, since you're using multiple worksheets, when using a Range(cells()) reference, you need to be explicit and make sure you tell each part of that (both the Range() and Cells()) what worksheet you're working with. I.e. destino = Sheets(separador).Range(Sheets(separador).Cells(10, 4), Sheets(separador).Cells(11, 5)) Commented Oct 13, 2015 at 17:01
  • 2
    @BruceWayne It doesn't matter which sheet if you use the the .Address property (string). You can try this and confirm: set rng = Range(Sheet1.Cells(1,1).Address, Sheet2.Cells(1,3).Address) will not throw an error, because the .Address property resolves to a valid string irrespective of the sheet. You can then use the address string to define the range on any sheet. Commented Oct 13, 2015 at 17:09

1 Answer 1

Reset to default
1

Ranges are object variables, and object variable require Set. Also, unqualified references to Range or Cells are to the active worksheet. So

With Sheets(separador)
  Set destino = Range(.Cells(10, 4), .Cells(11, 5))
End With
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ah! Don't forget the . in front of Range...Set destino = .Range(.Cells(10,4),.Cells(11,5)).
It's not necessary unless the first argument is a literal string, e.g., "D10"
Hmm - so is that because (and sorry for the lack of vocab), Range() looks to what's inside the ( ) to determine where the Range is (what worksheet)? So, since we specified we're using the Cells() in Sheets(separador), the Range part understands? Whereas using Range("A10") doesn't specify a sheet anywhere, it'll default to Active Sheet?
Range(cell1, cell2) defines a range by its corners, and both corners must be on the same worksheet (any sheet). The dot operator before Range confirms that a literal cell address argument refers to the worksheet referenced by the operator. And I should have said either argument, not just the first.