Select based on header name

[doriannjeshi]

Hi,
I need to start a selection based on a header named ADR (the closest result to A1) then 1 cell down and shiftright to the last column -1 column , then shiftdown

 

[Rijnsent]

Hi, are you looking for VBA code? You could start by doing that manually with the macro recorder switched on. That code probably needs a bit of changing, but it's a good start. Please do post your code if you're stuck.

 

[doriannjeshi]

Hi Rijnsent
start a selection based on a header named ADR (the closest result to A1) then 1 cell down and shiftright to the last column -1 column , then shiftdown
This is from the recorded macro , when I change the position of ADR cell the macro selection goes past the range on the right

Cells.Find(What:="adr", After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Range("A1:AC1").Select
Range(Selection, Selection.End(xlDown)).Select

 

[Rijnsent]

Hi Dorian, based on that code you could transform it into this. I assume you're quite new to VBA? So if I minimize your code and adjust it a bit, StartMe is the first step. If you run this, the cell below the cell with ADR should be selected. The main change to your code:
I removed " After:=ActiveCell, " from the Find part, as you only want to search in the first row of your sheet.
Next, if you want to test code, don't run it all at once, but use the VBE-editor to go through it step by step. So: open your file, select the right sheet, go to the VBA-editor (ALT+F11) and run this code step by step (pressing F8 for every step). After every step you can check your sheet to see what happened.

Sub StartMe()

ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select

End Sub

Sub StartMe2()

ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

End Sub

 

[doriannjeshi]

Hi Dorian, based on that code you could transform it into this. I assume you're quite new to VBA? So if I minimize your code and adjust it a bit, StartMe is the first step. If you run this, the cell below the cell with ADR should be selected. The main change to your code:
I removed " After:=ActiveCell, " from the Find part, as you only want to search in the first row of your sheet.
Next, if you want to test code, don't run it all at once, but use the VBE-editor to go through it step by step. So: open your file, select the right sheet, go to the VBA-editor (ALT+F11) and run this code step by step (pressing F8 for every step). After every step you can check your sheet to see what happened.

Sub StartMe()

ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select

End Sub

Sub StartMe2()

ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

End Sub

Thank you Rijnsent for your guide
When I move the selection one cell to the left it fixes it,
ActiveCell.Range("A1:V1").Select
I need range selection offset by one cell to the left

 

[doriannjeshi]

I need range selection offset by one cell to the left
but when I record it it gives me a set selection ActiveCell.Range("A1:V1").Select

so I go shift right to the last column , I go down with shift down, then must go left 1 . So basically I don't select the last column

 

[Rijnsent]

Ah, not the last column... One thing to note: the ways the macro recorder works is normally not efficient code. You see a lot of .Select statements, that is generally not smart to have in normal code. I think you also tried the macro recorder for the last step? I tried, but it didn't record anything. So you will have to write it yourself. I hereby did that and added some simple error catching for the case when there is no "adr" in the first row.
The lines starting with a single quote are comments, the Debug.Print helps seeing what is happening when you run the macro all at once (starting it from the front end or running it by pressing F5/start in the VBEditor. If you want to do it step by step, use F8 to start the code, pressing F8 again will just do one step at a time. If you want to learn a bit more about VBA, you can e.g. check out this tutorial: Excel VBA Programming - a free course for complete beginners (free and has good basics).

Sub StartMe3()

    'Find in the first row of the active sheet the value adr
    Set adrCell = ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
    If adrCell Is Nothing Then
        'Error, not found, exit sub
        Debug.Print "adr not found in first row"
        Exit Sub
    End If
    
    'startCell is just below the cell with adr
    Set startCell = adrCell.Offset(1, 0)
    'endCell is to the right, down and than one cell to the left
    Set endCell = startCell.End(xlToRight).End(xlDown).Offset(0, -1)
    
    'All "Set" items are references to Ranges (a single cell in this case), so you can check the Value, the Formula, the Address and all the other properties of that Range
    Debug.Print adrCell.Address, startCell.Address, endCell.Address
    
    'Select the range starting from startCell, ending with endCell
    ActiveSheet.Range(startCell, endCell).Select

End Sub

 

[doriannjeshi]

Ah, not the last column... One thing to note: the ways the macro recorder works is normally not efficient code. You see a lot of .Select statements, that is generally not smart to have in normal code. I think you also tried the macro recorder for the last step? I tried, but it didn't record anything. So you will have to write it yourself. I hereby did that and added some simple error catching for the case when there is no "adr" in the first row.
The lines starting with a single quote are comments, the Debug.Print helps seeing what is happening when you run the macro all at once (starting it from the front end or running it by pressing F5/start in the VBEditor. If you want to do it step by step, use F8 to start the code, pressing F8 again will just do one step at a time. If you want to learn a bit more about VBA, you can e.g. check out this tutorial: Excel VBA Programming - a free course for complete beginners (free and has good basics).

Sub StartMe3()

    'Find in the first row of the active sheet the value adr
    Set adrCell = ActiveSheet.Cells(1, 1).EntireRow.Find(What:="adr", LookIn:=xlFormulas2, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      
    If adrCell Is Nothing Then
        'Error, not found, exit sub
        Debug.Print "adr not found in first row"
        Exit Sub
    End If
  
    'startCell is just below the cell with adr
    Set startCell = adrCell.Offset(1, 0)
    'endCell is to the right, down and than one cell to the left
    Set endCell = startCell.End(xlToRight).End(xlDown).Offset(0, -1)
  
    'All "Set" items are references to Ranges (a single cell in this case), so you can check the Value, the Formula, the Address and all the other properties of that Range
    Debug.Print adrCell.Address, startCell.Address, endCell.Address
  
    'Select the range starting from startCell, ending with endCell
    ActiveSheet.Range(startCell, endCell).Select

End Sub

Thank you very much Rijnsent for the solution, guide and link to learn