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

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
– vacip, Commented May 16, 2016 at 20:19
This worked until I tried to do the .end(xlDown) and then it broke on me. Any thoughts? — TonyP
– TonyP, Commented May 16, 2016 at 20:34

SierraOscar · Accepted Answer · 2016-05-16 20:24:47Z

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, 2016 at 20:24

answered May 16, 2016 at 20:21

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

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

8 Comments

Mathieu Guindon Over a year ago

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)

SierraOscar Over a year ago

@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!

TonyP Over a year ago

@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?

SierraOscar Over a year ago

@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.

TonyP Over a year ago

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?

|

Collectives™ on Stack Overflow

Syntax for Range when using variables

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related