Help make Formula talk to macro

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?

excel

Formulas and Functions

Macros and VBA

Charla74
Mar 30, 2020
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

7 Replies

Charla74
Iron Contributor
Mar 29, 2020
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
- Greg Bonaparte
  Iron Contributor
  Mar 29, 2020
  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 youCharla74
  - Charla74
    Iron Contributor
    Mar 30, 2020
    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

Forum Discussion

Help make Formula talk to macro

7 Replies

Resources