Jul 30 2020 04:35 AM
The following macro needs to be false if BD an BE are negative values. I'm not clear how to. The formula used in cell BF... is =(BD...)*(BE...)
Sub BFSORT()
Application.Calculation = xlManual
Application.EnableEvents = False
Range("C41:BF3880").Select
Range("BF3880").Activate
Selection.ClearContents
Dim rng As Range
Dim lr As Integer
Set rng = Range("B41:B3880")
lr = WorksheetFunction.CountA(rng) + 40
Range("BB3881:BF3881").Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollRow = 41
Range("BB41").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.AutoFill Destination:=Range("BB41:BF" & lr), Type:=xlFillDefault
Range("BB41:BF" & lr).Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayStatusBar = True
.EnableEvents = False
End With
Application.Calculation = xlManual
Range("B41:BF" & lr).Select
Range("BF" & lr).Activate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
"BF41:BF" & lr), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B41:BF" & lr)
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
'Range("BG14").Select
'Selection.Copy
'Range("BI6").Select
' ActiveSheet.Paste
Application.Calculation = xlAutomatic
Application.Calculation = xlManual
MsgBox "BFSort is completed."
End With
End Sub
Jul 30 2020 04:46 AM
Jul 30 2020 05:13 AM
@Greg Bonaparte That macro appears to be a recording (i.e. you didn't write it) and has a lot of extra unnecessary things so it is very unclear to me what it is that you actually need. Furthermore you say it should be FALSE but it is a SUB not a FUNCTION so it doesn't have a value. Do you need is a macro/function that return will return FALSE if both those cells are negative or is there a particular cell on the sheet being set by the macro that should be FALSE in that case? And why don't you just use a formula on the sheet? For example is this supposed to be a part of the formula in BF? Should each cell in BF either have a value of BD*BE unless both are negative and then be FALSE? If so maybe this formula in BF would help:
=IF(OR(BD1>=0,BE1>=0),BD1*BE1,FALSE)
Jul 30 2020 05:30 AM
Jul 30 2020 05:33 AM