Help with VBA code to Select cells above named range

[DavidH56]

Hi,

Can someone help with the VBA code to select the cells in the row above a named range and fill it with a formula? For example if my range is in C4:G12, I would to add a formula in cells c3:G3.

I have a named range with titles in the first row; I'm trying to add an hlookup formula to the cells above the title that will insert a number indicating the column sort order sequence, then I would do a sort by columns on the original data plus the new row with the sort order sequence.

Thanks,

David

 

[AlphaFrog]

This will select the cells one row above the named range "TEST"

Range("TEST").Resize(1).Offset(-1).Select

This will add a completely made-up HLOOKUP formula

Range("TEST").Resize(1).Offset(-1).FormulaR1C1 = "=HLOOKUP(R1C1,R1C10:R2C20,2,0)"

 

[DavidH56]

Thanks, the code works perfectly and playing around with it led me to another question:

How can I assign a name to the range above the current range?

Here's the code I tried, but couldn't get it to work:

Sub testrangeabove()
Dim NewTest As Range
Set NewTest = Range("TEST").Resize(1).Offset(-1).address
Range(Newtest).Select
End Sub

Once I get NewTest created I'd like to combine it with Test to create a range that I can sort; here's the code I'm trying for the union:

Sub Together()
Dim Combined As Range
Set Combined = Union(Range("Test"), Range("NewTest"))
Range(Combined).Select
End Sub

Thanks again for the help!!

 

[AlphaFrog]

You're on the right track.

Sub testrangeabove()

    Dim NewTest As Range, Combined As Range
    
    Set NewTest = Range("TEST").Resize(1).Offset(-1)
    'NewTest.Select
    
    Set Combined = Union(Range("Test"), NewTest)
    Combined.Select
    
[COLOR="Green"]    'Or this...
    'Set Combined = Range("TEST").Offset(-1).Resize(Range("TEST").Rows.Count + 1)
    'Combined.Select[/COLOR]
    
End Sub

NewTest and Combined are not named ranges like TEST. They are range variables.