Selection.End(xldown).Select variable number of times

[Will85]

In column A, I have strategically placed the value of x in specific rows.

I use a macro that goes to column A and then executes selection.end(xldown).Select a certain number of times depending on which row I want it to land on.

For example if I want it to select the third row with an x I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select


I would prefer to be able to tell it to go down a specific # of times based on the value in a cell on another sheet.

Additionally, I need it to look up this value, instead of pointing to a specific cell reference.

For example, Id want to index match the value based on an account number, the account number its looking up can be hardcoded inside the macro itself.

Sheet2 has account numbers in column A and the value I am seeking in column B.

 

[DanteAmor]

You can use the .Find Method

https://docs.microsoft.com/en-us/office/vba/api/excel.range.find

If you need help on how to use it, explain with an example the data you have on each sheet and the result you expect

 

[Fluff]

Maybe

Sub Will85()
   Dim Fnd As Range
      
   Set Fnd = Sheets("Sheets2").Range("A:A").Find("ABC123", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   Range("A1").Resize(Fnd.Offset(, 1)).Select
End Sub

 

[Will85]

I get a subscript out of range error.

In the code you provided: Set Fnd = Sheets("Sheets2").Range("A:A").Find("ABC123", , , xlWhole, , , False, , False)

where doe sit say find abc123 in column A and return the corresponding value in column B? column b holds the value (ie number of times I need to xldown)

Maybe

Sub Will85()
   Dim Fnd As Range
      
   Set Fnd = Sheets("Sheets2").Range("A:A").Find("ABC123", , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then Exit Sub
   Range("A1").Resize(Fnd.Offset(, 1)).Select
End Sub

 

[Fluff]

You need to change the sheet name & the ABC123 to match your sheet & the value you want to find.

 

[Will85]

I did, it doesnt work.

I am now trying to use .Value = Application.WorksheetFunction.VLookup(("ABC123"), Range("account_position"), 2, 0)

This returns 2, meaning xldown two times, Im just trying to figure out how to put it all together.

 

[Fluff]

What error did you get & what line was highlighted?

 

[Will85]

Subscript out of range
Set Fnd = Sheets("Sheets2").Range("A:A").Find("ABC123", , , xlWhole, , , False, , False)


I created a brand new workbook.

Sheet1 A8="x", A15="x", A22="x"
Sheet2 A1="abc123", B1=3


After running the macro, I would expect Sheet1 A22 to be selected

What error did you get & what line was highlighted?

 

[Fluff]

That error means you don't have a sheet called "Sheet2" in the active workbook.
Check the name of the sheet on the Tab & make sure that there are no leading/trailing spaces.

You also need to explain exactly what you are trying to achieve, because it's not very clear to me.

 

[Will85]

Your right, you put "Sheets2", through me off, excel defaults to "Sheet2". The macro no longer throws an error, but it does not function as intended.

I currently use

On Sheet1 I have put the value of "x" in cells A8, A15, and A22.

I want to select range A22 in VBA, so I use:

Range("A1").Select
selection.end(xldown).Select
selection.end(xldown).Select
selection.end(xldown).Select

Instead of using the above, Id like to write some vba that could xldown a specific number of times.

Something like selection.end(xldown)3.Select


The value of 3 is based of a lookup. I am currently trying to loop the xldown as such, but it doesnt work:

Sub xldown_variable_number_of_times()
Dim position As Integer
Dim iCtr As Long
Dim DestCell As Range


position = Application.WorksheetFunction.VLookup(("ABC123"), Range("account_position"), 2, 0)


Set DestCell = Range("a1")


For iCtr = 1 To position
Set DestCell = DestCell.End(xlDown)
Next iCtr




End Sub

That error means you don't have a sheet called "Sheet2" in the active workbook.
Check the name of the sheet on the Tab & make sure that there are no leading/trailing spaces.

You also need to explain exactly what you are trying to achieve, because it's not very clear to me.