MULTIPLY NEGATIVE NUMBERS

%3CLINGO-SUB%20id%3D%22lingo-sub-1555539%22%20slang%3D%22en-US%22%3EMULTIPLY%20NEGATIVE%20NUMBERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555539%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20following%20macro%20needs%20to%20be%20false%20if%20BD%20an%20BE%20are%20negative%20values.%20I'm%20not%20clear%20how%20to.%26nbsp%3B%20The%20formula%20used%20in%20cell%20BF...%20is%26nbsp%3B%3D(BD...)*(BE...)%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20BFSORT()%0A%0A%0A%0A%0AApplication.Calculation%20%3D%20xlManual%0AApplication.EnableEvents%20%3D%20False%0ARange(%22C41%3ABF3880%22).Select%0ARange(%22BF3880%22).Activate%0ASelection.ClearContents%0A%0A%0ADim%20rng%20As%20Range%0ADim%20lr%20As%20Integer%0ASet%20rng%20%3D%20Range(%22B41%3AB3880%22)%0Alr%20%3D%20WorksheetFunction.CountA(rng)%20%2B%2040%0A%0ARange(%22BB3881%3ABF3881%22).Select%0AApplication.CutCopyMode%20%3D%20False%0ASelection.Copy%0AActiveWindow.ScrollRow%20%3D%2041%0ARange(%22BB41%22).Select%0ASelection.PasteSpecial%20Paste%3A%3DxlPasteFormulas%2C%20Operation%3A%3DxlNone%2C%20_%0ASkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DFalse%0ASelection.AutoFill%20Destination%3A%3DRange(%22BB41%3ABF%22%20%26amp%3B%20lr)%2C%20Type%3A%3DxlFillDefault%0ARange(%22BB41%3ABF%22%20%26amp%3B%20lr).Select%0A%0AWith%20Application%0A.Calculation%20%3D%20xlCalculationAutomatic%0A.ScreenUpdating%20%3D%20True%0A.DisplayStatusBar%20%3D%20True%0A.EnableEvents%20%3D%20False%0AEnd%20With%0A%0AApplication.Calculation%20%3D%20xlManual%0A%0ARange(%22B41%3ABF%22%20%26amp%3B%20lr).Select%0ARange(%22BF%22%20%26amp%3B%20lr).Activate%0AActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Clear%0AActiveWorkbook.Worksheets(%22Sheet1%22).Sort.SortFields.Add2%20Key%3A%3DRange(%20_%0A%22BF41%3ABF%22%20%26amp%3B%20lr)%2C%20SortOn%3A%3DxlSortOnValues%2C%20Order%3A%3DxlDescending%2C%20DataOption%3A%3D%20_%0AxlSortNormal%0AWith%20ActiveWorkbook.Worksheets(%22Sheet1%22).Sort%0A.SetRange%20Range(%22B41%3ABF%22%20%26amp%3B%20lr)%0A.Header%20%3D%20xlGuess%0A.MatchCase%20%3D%20False%0A.Orientation%20%3D%20xlTopToBottom%0A.SortMethod%20%3D%20xlPinYin%0A.Apply%0A%0A%0A'Range(%22BG14%22).Select%0A'Selection.Copy%0A'Range(%22BI6%22).Select%0A'%20ActiveSheet.Paste%0AApplication.Calculation%20%3D%20xlAutomatic%0AApplication.Calculation%20%3D%20xlManual%0AMsgBox%20%22BFSort%20is%20completed.%22%0AEnd%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1555539%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555573%22%20slang%3D%22de-DE%22%3ERE%3A%20MULTIPLY%20NEGATIVE%20NUMBERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555573%22%20slang%3D%22de-DE%22%3EYou%20can%20simply%20convert%20them%20to%20positive%20values%20with%20ABS%20().%20Explicit%20option%20Public%20Sub%20test%20()%20Dim%20variable%20as%20double%20variable%20%3D%20-123%20MsgBox%20variable%20%3D%20Abs%20(variable)%20MsgBox%20variable%20End%20Sub%20If%20you%20have%20solved%20your%20problem%20with%20this%20solution%2C%20please%20mark%20it%20as%20the%20correct%20answer%20so%20that%20others%20can%20also%20get%20this%20information.%20If%20this%20is%20not%20what%20you%20are%20looking%20for%2C%20please%20give%20us%20a%20quick%20feedback.%20Nikolino%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555656%22%20slang%3D%22en-US%22%3ERe%3A%20MULTIPLY%20NEGATIVE%20NUMBERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555656%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F235152%22%20target%3D%22_blank%22%3E%40Greg%20Bonaparte%3C%2FA%3E%26nbsp%3B%20That%20macro%20appears%20to%20be%20a%20recording%20(i.e.%20you%20didn't%20write%20it)%20and%20has%20a%20lot%20of%20extra%20unnecessary%20things%20so%20it%20is%20very%20unclear%20to%20me%20what%20it%20is%20that%20you%20actually%20need.%26nbsp%3B%20Furthermore%20you%20say%20it%20should%20be%20FALSE%20but%20it%20is%20a%20SUB%20not%20a%20FUNCTION%20so%20it%20doesn't%20have%20a%20value.%26nbsp%3B%20Do%20you%20need%20is%20a%20macro%2Ffunction%20that%20return%20will%20return%20FALSE%20if%20both%20those%20cells%20are%20negative%20or%20is%20there%20a%20particular%20cell%20on%20the%20sheet%20being%20set%20by%20the%20macro%20that%20should%20be%20FALSE%20in%20that%20case%3F%26nbsp%3B%20And%20why%20don't%20you%20just%20use%20a%20formula%20on%20the%20sheet%3F%26nbsp%3B%20For%20example%20is%20this%20supposed%20to%20be%20a%20part%20of%20the%20formula%20in%20BF%3F%26nbsp%3B%20Should%20each%20cell%20in%20BF%20either%20have%20a%20value%20of%20BD*BE%20unless%20both%20are%20negative%20and%20then%20be%20FALSE%3F%26nbsp%3B%20If%20so%20maybe%20this%20formula%20in%20BF%20would%20help%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(OR(BD1%26gt%3B%3D0%2CBE1%26gt%3B%3D0)%2CBD1*BE1%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555758%22%20slang%3D%22de-DE%22%3ESubject%3A%20MULTIPLY%20NEGATIVE%20NUMBERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555758%22%20slang%3D%22de-DE%22%3E%3DPRODUCT(ABS(BD1%3ABE1))%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Without%20VBA%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20Nikolino%20I%20know%20I%20%3CBR%20%2F%3E%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1555786%22%20slang%3D%22de-DE%22%3ESubject%3A%20MULTIPLY%20NEGATIVE%20NUMBERS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1555786%22%20slang%3D%22de-DE%22%3Esry%20this%20is%20the%20formulas%20%3CBR%20%2F%3E%20%3DPRODUCT(ABS(BD1*BE1))%3C%2FLINGO-BODY%3E
Frequent Contributor

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

 

4 Replies
You can simply convert them to positive values with ABS (). Explicit option Public Sub test () Dim variable as double variable = -123 MsgBox variable variable = Abs (variable) MsgBox variable End Sub If you have solved your problem with this solution, please mark it as the correct answer so that others can also get this information. If this is not what you are looking for, please give us a quick feedback. Nikolino I know I don't know anything (Socrates)

@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)
=PRODUCT(ABS(BD1:BE1))

Without VBA

Nikolino
I know I don't know anything (Socrates)
sry this is the formulas
=PRODUKT(ABS(BD1*BE1))