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

Greg Bonaparte
Iron Contributor
Mar 30, 2020
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.
- Greg Bonaparte
  Iron Contributor
  Mar 30, 2020
  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
  - Greg Bonaparte
    Iron Contributor
    Mar 30, 2020
    It appears the corrections worked. Thank you so much for your help