Range.Formula does not lead to cell "selected" or "active"

[heathball]

I am trying to simplify and reduce the amount of code i have.
For some reason i expected the "range" to act as some kind of 'identifier' for the code immediately following it....

Is there a way to select/activate a dynamic cell or range of cells (in this case, "AU2"), so that it is identified by the macro that is called after the formula is entered, on the same line of code?

( the called macro is operating via the 'selection' method. )
or does it have to be a seperate "range(AU2).select" line of code to allow the macro to identify the cell/range of the formula.
Is there a way to build the "select" part into the range.Formula idea?

I was hoping it could be simplified and exist on just one line? or am i dreaming?
I have 1000's of these.
Any help is greatly appreciated.

        range("AU2").FormulaR1C1 = "=RC[-1]&""_""&RC[-44]"
Call EXCEL_COPYDOWN_A  '________copydown___paste____A______________________

 

[StephenCrump]

It's inefficient and rarely necessary to use .Select and .Selection.

If you know the last row (N) to be copied down to, you could simply use:

Range("AU2:AU" & N).Formula = "=AT2&""_""&C2"

I have 1000's of these.

Can you provide more examples? Are they all copied down the same number of rows?

Please also post your code for EXCEL_COPYDOWN_A.

 

[heathball]

I see the point you are making with your code. It provides a solution...i am trying to get my head around how it effects the various things i am doing (or the way i am currently doing them)...... i think your code example is what i am looking for....my mind is heading towards a dim ....As selection, or a set range = selection/active cell concept, which is probably not adhering to the structure/rules of excel.

I am thinking i will make the change and use the example you have provided.....
I know there are examples of xlookup in a similar style online, but if you can offer a quick xlookup pathway to match the efficiency of your previous example, that would be awesome, as they are about 35% of my work. They are constantly changing and updating.

Can you provide more examples?

range("BW2").FormulaR1C1 = "=XLOOKUP(RC[-6],HX_qual_ddtr,HR_number,""#"")"
Call MrExcel_copydown_calculate_ONLY   'copydown CALCULATE ONLY-------------------------------------

        range("T2").FormulaR1C1 = _
        "=RC[-18]&""_""&TEXT(RC[-18],""d/mm/yyyy"")&""_""&RC[-1]"
Call MR_EXCEL_COPYDOWN_A  '________copydown___paste____A______________________


range("CK2").FormulaR1C1 = "=XLOOKUP(RC70,fc_ddt,CUONG,""#"")"
        range("CL2").FormulaR1C1 = "=XLOOKUP(RC70,fc_ddt,fc_member,""#"")"
        range("CM2").FormulaR1C1 = "=XLOOKUP(RC70,fc_ddt,fc_auto,""#"")"
    range("CN2").FormulaR1C1 = "=XLOOKUP(RC70,fc_ddt,POST_CHANGE,""#"")"

'non-duplicate rankings on sorted data.
 range("DV30").FormulaR1C1 = _
        "=IF(RC[-67]=""#"",""#"",(COUNTIFS(R2C43:R60C43,RC43,R2C[-67]:R60C[-67],""<""&RC[-67])+COUNTIFS(R2C43:RC43,RC43,R2C[-67]:RC[-67],RC[-67])))"

Are they all copied down the same number of rows?
-the files/sheets are different sizes, but i spend a long time on each file...so they are kind of constant, but i deal with the row count well so far. (with my current method, which is probably changing very soon)
I have big files, some are 950,000 rows.

"i have these flexible/selection codes as i do a lot of work on excel (non coding work) with "quick access" macro buttons on constantly changing data sets and i need speed/flexibility when doing so.
these two codes are efficient on very large sets of data. I got them both from Mr Excel

Sub EXCEL_COPYDOWN_A()
'column A
    Selection.AutoFill range(Selection, Intersect(Selection.EntireColumn, range("A" & Rows.Count).End(xlUp).EntireRow))
    Selection.EntireColumn.Select
    If TypeName(Selection) = "Range" Then Selection.Calculate
    Selection.copy
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

I also use this code to keep formulas in row2

Sub autofill_areas_row3_paste()
Selection.copy
With Selection
.Offset(Rowoffset:=1, Columnoffset:=0).Select
End With
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

Dim rng As range
    Dim rArea As range
    Dim LastRowA As Long
    Set rng = Selection
    LastRowA = ActiveSheet.cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Set rng = Intersect(Rows(rng.Row & ":" & LastRowA), rng.EntireColumn)
    For Each rArea In rng.Areas
        rArea.Rows(1).AutoFill rArea, Type:=xlFillCopy
        rArea.Calculate
        rArea.Value2 = rArea.Value2
    Next rArea

Set rArea = Nothing
Set rng = Nothing
End Sub

 

[heathball]

i did a little test on 700,00 rows, and it works well. and i believe the code will be easier to read, and less of it

Sub testv1()
n = ActiveSheet.cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
range("AU2:bz" & n).Formula = "=$AT2*$C2"
With range("AU3:bz" & n)
.Value2 = .Value2
End With

End Sub

 

[StephenCrump]

Based on just a quick scan of your code, I think you're doing something like this?

Sub Test()
   
    Range("AU2").Formula = "=AT2 & ""_"" & C2"
    Range("BW2").Formula = "=1"
    Range("CK2").Formula = "=2"
    Range("CL2").Formula = "=3"
    Range("CM2").Formula = "=4"
    Range("CN2").Formula = "=5"
   
    Call CopyDown(Range("AU2,BW2,CK2:CN2"))

End Sub
Sub CopyDown(rng As Range)

    Dim r As Range
    Dim LastRow As Long
   
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
   
    For Each r In rng
        With r.Offset(1).Resize(LastRow - r.Row)
            .Formula = r.Formula
            .NumberFormat = r.NumberFormat
            .Value = .Value
        End With
    Next r

End Sub

I'm not sure what you mean by ...

I know there are examples of xlookup in a similar style online, but if you can offer a quick xlookup pathway to match the efficiency of your previous example, that would be awesome, as they are about 35% of my work.

I just find A1 formulae easier to read than R1C1, so I'll only use R1C1 if I need relative referencing.

 

[heathball]

Based on just a quick scan of your code, I think you're doing something like this?
-some of what you have on the page is similar. I alter the recorded vba with code snippets and try things and choose the best of what i can find...I do different things to test speed, which leads me to answer the next point, which is similar to "i was not looking in the right area, and therefore i did not find it.' it's not directly related to the RC >> A1 concept, it is just the recorded VBA (with changes) works for me, with what i do, so i have followed that path and it did not lead to = range("AU2:bz" & n).Formula = "=$AT2*$C2".
When i did look online, i found it it in 45 seconds. With the right question.

so thank you for your time and its a great help!