VBA trying to use range.offset with a cell address held in a named variable

Question

I am trying to get a small matrix of data from one workbook and paste it into ThisWorkbook using offset from a cell reference. The cell reference changes though as I'm cycling down a range looking for values. The variable I've set called 'WhereCell' holds the address of the currently held value, and I want to offset from whatever is held in WhereCell. It works if I hard code a cell reference, eg Range("A1") but with the code as it is I get a 1004: Method 'range' of object '_Global' failed.

How can I change it to offset from 'WhereCell'?

Sub GetFlows()

Dim rng As Range
Dim row As Range
Dim cell As Range
Dim dem1 As String
Dim WhereCell As Range
Dim valueRng As Range
Dim x As Long
Dim y As Long

Set rng = Range("A9:A200")

For x = 1 To rng.Rows.Count

dem1 = rng.Cells(x).Value

If dem1 <> "" Then
    Set WhereCell = ThisWorkbook.ActiveSheet.Range("A9:A200").Find(dem1, lookat:=xlPart)
    Windows("GetFilenames v2.xlsm").Activate
    Worksheets(dem1).Range("A1").CurrentRegion.Copy
    ThisWorkbook.ActiveSheet.Range(Range(WhereCell), Range(WhereCell).Offset(, 2)).PasteSpecial Paste:=xlPasteValues
    'MsgBox dem1
    'If Not WhereCell Is Nothing Then
    'MsgBox WhereCell.Address
    'End If
    Else
    ThisWorkbook.Activate
End If

Next x

End Sub

Wherecell is a range already you do not wrap it in Range() See my answer here for more explanation on that: stackoverflow.com/questions/46302871/… — Scott Craner
– Scott Craner, Commented Sep 19, 2017 at 14:39

Vityata · Accepted Answer · 2017-09-19 14:48:03Z

4

Try like this:

WhereCell.Offset(0, 2)).PasteSpecial Paste:=xlPasteValues

instead of this:

ThisWorkbook.ActiveSheet.Range(Range(WhereCell), Range(WhereCell).Offset(, 2)).PasteSpec...

To understand more about WhereCell, consider writing

Debug.Print WhereCell.Parent.Name
Debug.Print WhereCell.Parent.Parent.Name
Debug.Print WhereCell.Address

after declaring it. It will show you its Worksheet, the name of the Application and its address in the immediate window. Thus, it already "knows" them and if you try to "redeclare" them, you get an error.

edited Sep 19, 2017 at 14:48

answered Sep 19, 2017 at 14:39

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rich Prag Over a year ago

I don't seem to get anything with the Debug.Print statements. What format is the output in?

Vityata Over a year ago

@RichPrag - press Ctrl+G the output will be in the immediate window.

Collectives™ on Stack Overflow

VBA trying to use range.offset with a cell address held in a named variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related