Help make Formula talk to macro

[Greg Bonaparte]

I have the following formula: =COUNTA($B41:$B3880)+40

And the following Macro:

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'
Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:BE3043"), Type:=xlFillDefault
Range("BE41:BE3043").Select
Application.Calculation = xlAutomatic
Range("B41:BF3043").Select
Range("BF3043").Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:BE3043"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BF3043")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

The formula tells me that I have 3043 populated cells in column B. The macro need to auto fill 3043 cell. I have been manually telling the macro how many lines to auto fill. How can I get these 2 code to talk?

[#2]

@Greg Bonaparte 

I assume you have populated cells in column A41 and down from there and you want your formula in the equivalent number of cells in column B.......in that case, I would identify the last row from column A and use this for the range to enter your formula:

'Find the last used row in a Column: column A in this example

    Dim LastRow As Long

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    End With

You could then copy down a formula in B41 as follows:

    Range("B41:B" & LastRow).Filldown

[#3]

Thank You Charla. Im a little confused however. I have data in row B therefor can not put formula in the cells. Second, Im not clear where I would plug in your code to mind. Would you kindly input your code. Do not consider row A. Thank you@Charla74 

[#4 · Accepted solution]

@Greg Bonaparte 

Ok, seems I got the wrong end of the stick - try this, which incorporates your COUNTA in the code so no longer required in the worksheet:

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'

Dim rng As Range
Dim lr As Integer
Set rng = Range("B41:B3880")
lr = WorksheetFunction.CountA(rng) + 40

Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:B" & lr), Type:=xlFillDefault
Range("BE41:B" & lr).Select
Application.Calculation = xlAutomatic
Range("B41:B" & lr).Select
Range("B" & lr).Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:B" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:B" & lr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

[#5]

@Charla74 Thank you Charla74 for attempting to hep me. When I ran your code I see that instead of copying formula  be3881 to BE41:BE3043, it copied be3881 to B41:BE3043 overwriting all data in the table. I will compare code to see if I can find the problem.

[#6]

OK I think I corrected it, however my whole macro takes almost 30 mins to complete so I must wait for result. Here is the change I made. Let me know if you see any trouble

Sub CopySortCalcRSI()
'
' CopyRSITemp Macro
'

'

Dim rng As Range
Dim lr As Integer
Set rng = Range("B41:B3880")
lr = WorksheetFunction.CountA(rng) + 40

Range("BE3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BE41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BE41:BE" & lr), Type:=xlFillDefault
Range("BE41:BE" & lr).Select
Application.Calculation = xlAutomatic
Range("B41:BE" & lr).Select
Range("BE" & lr).Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.CLEAR
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BE41:BE" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BE" & lr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

Application.Calculation = xlManual

Range("BG14").Select
Selection.Copy
Range("BI5").Select
ActiveSheet.Paste

Call CleanRSIcolumn

End With
End Sub

@Greg Bonaparte 

[#7]

It appears the corrections worked. Thank you so much for your help

[#8]

@Greg Bonaparte 

Apologies for my B vs BE oversight - glad you got it figured out!