Forum Discussion

Iron Contributor

Mar 29, 2020

Solved

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 Selectio...

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

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