Syntax for Range when using variables

Question

I am trying to have my code select a cell then use .End(xlDown).Select then Selection.Copy to copy a data set but I am having trouble getting my code to select the first cell using variables within my loop. I know my syntax is off on the range function but I am not sure where. Any help would be greatly appreciated.

Sub Copy_Function_Data()
Dim Target As String
Dim X As Integer
Dim Y As Integer
Dim Target_2 As String
Dim Last_Row3 As Integer
Dim Last_Column3 As Integer
Dim Title_Count As Integer
Dim Country_Count As Integer
Dim Total_Count As Integer
Dim Title_Column As Long
Dim Country_Column As String

Y = ActiveWorkbook.Worksheets("Calculations").Range("B2", Worksheets("Calculations").Range("B2").End(xlDown)).Rows.Count

For X = 1 To Y

Sheets("Calculations").Select
Target = Range("B2").Offset(X, 0)
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "TempData"
Worksheets("TempData").Range("A1").Value = Target
ActiveSheet.Range("A1").Select
    Selection.Replace What:="Open Position - ", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Target_2 = Worksheets("TempData").Range("A1")
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = (Target_2)
Sheets("Raw Data").Select
Selection.AutoFilter
ActiveSheet.Range("$B$1:$Z$10000").AutoFilter Field:=5, Criteria1:= _
    (Target)
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(Target_2).Select
Range("B20").Select
ActiveSheet.Paste
Total_Count = ActiveWorkbook.Worksheets(Target_2).Range("B20", Worksheets(Target_2).Range("B20").End(xlDown)).Rows.Count
Last_Row3 = Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Row
Last_Column3 = Cells.Find(What:="*", _
            After:=Range("A1"), _
            LookAt:=xlPart, _
            LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False).Column
Title_Column = Cells.Find(What:="Title", _
            After:=ActiveCell, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False).Column
Country_Column = Cells.Find(What:="Country", _
            After:=ActiveCell, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False).Column


'************* This is where I need to add the code to copy and paste the range

Sheets("TempData").Delete






Next X
End Sub

[interesting read](https://msdn.microsoft.com/en-us/library/office/ff838238.aspx). — findwindow, May 16 '16 at 20:13
`Range("A1")` **or** `cells(1,2)`, don't mix them unless you understand what you are doing. `Cells` command takes **numbers** as parameters, not strings. So in your case, the first row should be something like `Cells(20, Title_Column).Select` — vacip, May 16 '16 at 20:19
Also, read [how to avoid using select in macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — vacip, May 16 '16 at 20:20
This worked until I tried to do the .end(xlDown) and then it broke on me. Any thoughts? — TonyP, May 16 '16 at 20:34

SierraOscar · Accepted Answer · 2016-05-16T20:24:47.887

2

The syntax you're after is:

Range(Title_Column & "20")

so your code can be written like so:

Range(Range(Title_Column & "20"), Range(Title_Column & "20").End(xlDown)).Copy

The Range() method can be used in a couple of ways:

'// return range covering everywhere between start_range and end_range
Range([start_range], [end_range])

'// This can be used with range or cells:
Range(Range("A1"), Range("C5"))
Range(Cells(1, 1), Cells(5, 3))
Range(Cells(1, "A"), Cells(5, "C"))

'// Pass a string argument as the identifier
'// Single range
Range("A1")
'// Multiple cell range
Range("A1:C5")
'// non-contiguous range
Range("A1,B1,C5")

edited May 16 '16 at 20:24

answered May 16 '16 at 20:21

SierraOscar

17,507
6
40
68

Better: qualify `Range` function calls with an actual `Worksheet` object, instead of implicitly referring to the `ActiveSheet` (which *may or may not be a `Worksheet`*) – Mathieu Guindon May 16 '16 at 20:24
1

@Mat'sMug absolutely agree, but baby steps... qualifying a range won't do OP any good if they aren't getting the syntax right for the range method in the first place! – SierraOscar May 16 '16 at 20:26
@MacroMan I tried the code as you mentioned and it is giving me the Range of Object_Global Failed error. Could that be solved by qualifying the sheet? – TonyP May 16 '16 at 20:33
@TonyP it certainly wouldn't do any harm - it's best practice to _always_ qualify the sheet when working with range objects becuase you can't code for what the user is going to do, if they chagne the sheet and your code doesn't expect it then it will fail. – SierraOscar May 16 '16 at 20:35
This is all within a loop so the name of the sheet will always be changing based on what position within the loop it would be. I have, in the past, been simply using activesheet.range etc as my qualifier with a sheets().select before it. How would I qualify the sheet in this case? – TonyP May 16 '16 at 20:38
Instead of `Sheets("Sheet1").Select: ActiveSheet.Range("A1").Select: Selection.Copy` just use `Sheets("Sheet1").Range("A1").Copy` etc. You don't need to use `.Select` or `Selection` in Excel VBA – SierraOscar May 16 '16 at 20:40
@MacroMan For some reason I still cannot get the above to work. Instead of using the xldown I think it would be better to use find to get the appropriate column and starting row then a different find to find the last row and name that range. I am not sure how to do that though. – TonyP May 16 '16 at 20:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/112084/discussion-between-tonyp-and-macro-man). – TonyP May 16 '16 at 20:49

Syntax for Range when using variables

1 Answers1

Linked