VBA loop to multiply by negative one

**mkeys4** · 08-14-2013, 10:28 AM

Hi guys

I'm looking for a VBA code that does a type of multiply loop. Take a look at the sheet. The goal is: if one of the numbers in column A:A is greater than zero, the NEXT five numbers in the column L:L should be multiplied by negative one, and outputted in Y:Y. If the number in column A:A is less than zero the next five numbers in the L:L should just be outputted in Y:Y (not multiplied by negative 1). If there's no number or an N/A, the loop should just ignore these cells. The should be done with every pair (A&L, B&M, C&N, ect).

So for example, the loop will run and find a A2 populated with a negative number, so it would output L3:L7 into Y3:Y7. The loop would then start again at A8, find A12 populated with a negative number, and then output L13:L17 into Y13:L17. When the loop runs again, it will find a positive number in A:20, and will therefore multiply L21:L25 by negative one and output that into Y21:Y25. This should happen with B&M, C&N, ect

Hope I'm being clear! Any help is really appreciated!

**mkeys4** · 08-14-2013, 04:01 PM

Any ideas?

**hamjam** · 08-14-2013, 04:21 PM

Hello
There are basically 2 loops you need, the main one that runs through A:A and the next runs from 1 to 5 and does the multiplication and fill in the values. The skipping is done using offset to the activecell.
Do you want to give it a bash ?

Regards

**mkeys4** · 08-14-2013, 04:23 PM

Hi hamjam,

Thanks for responding! I unfortunately don't know VBA at all, I just know I can't use normal excel equations for a loop. Would you mind posting an example code of what you mean?

**hamjam** · 08-14-2013, 05:27 PM

Hello
I have put something together, just for the initial column calculation. Please expand on the moving across the columns.
Also do you know how much data is available as my initial loop stops after a blank value so we need to try a different logic there.

Sub test()
    Range("A2").Select
    myvalue = ActiveCell.Value
    While myvalue <> ""
        Ignore = False  'used to check whether there are valid values or not
        If myvalue < 0 Then
            factor = 1
        ElseIf myvalue > 0 Then
            factor = -1
            Else: Ignore = True  'where it is blank or N/A
        End If
        
        If Ignore = False Then  'we need to do something
            For n = 1 To 5
                ActiveCell.Offset(n, 24).Value = factor * ActiveCell.Offset(n, 11).Value
            Next n
            ActiveCell.Offset(n - 1, 0).Select
        End If
        ActiveCell.Offset(1, 0).Select
        myvalue = ActiveCell.Value
        If IsError(myvalue) Then myvalue = 0
    Wend
End Sub

Regards

**mkeys4** · 08-14-2013, 08:28 PM

Hi hamjam,

Thanks for the code. In regards to your question: If the code comes across a blank cell or a cell with #N/A, it should just continue to look for a number in A:A, and begin the loop again once a number has been found, So in other words, Y:Y would be blank until a number is found in A:A, and then the next five cells in L:L would be multiplied by -1 or not (depending on if the number found in A:A is >/< 0).

In terms of moving across the columns, what I mean is that if a number is found in A:A, then the rule will be applied to the next five cells in L:L. So we're working down the rows of those two columns. Then we start on a new column (B). If a number is found in B:B, then the rule will be applied to the next five cells in M:M. Then we start on a new column (C). If a number is found in C:C, then the rule will be applied to the next five cells in N:N. And so on and so forth. The idea is that each column in A1:K1 has a corresponding pair in L1:W1, where A&L, B&M, C&N.

**hamjam** · 08-15-2013, 07:39 AM

Hello
Thanks for the feedback.
I have added a routine that determines the last row of the data, before starting the data evaluation process.
Also have added another loop that runs through the different columns to perform the calculations
Added some bits and bobs to complete the code

Have a look and see if that does the job.

Regards

**hamjam** · 08-15-2013, 07:41 AM

One more item, to run macros, you can enter the shortcut <ALT F8> and select test and run it

Regards

**mkeys4** · 08-15-2013, 08:39 AM

Sorry, one more thing. In the past I've had codes that allow you to actually name your ranges. In the sheet that I'll be using this on, the two data sets are not next to each other, which seems to be a problem in this code. Is there a way to fix this? So that no matter where the data is on the sheet, I can target the two arrays.

**mkeys4** · 08-15-2013, 08:40 AM

By the way, here's the code for others with the same question

Public LastRow As Integer  'used as a constant once its value is known
Public myvalue As Variant

Sub DetermineEndRow()  'this works out the longest column from columns A to K, so runs just at start
    Dim temp As Long
    temp = 1
    Range("A2").Select
    For n = 1 To 10
        currentRow = ActiveCell.Row
        temp = Selection.End(xlDown).Row
        If temp < 1000 Then  'I assume the list is less than 1000 rows long
            LastRow = temp
        Else: temp = currentRow
        End If
        ActiveCell.Offset(LastRow - currentRow, 1).Select
    Next n
End Sub
Sub test()
    DetermineEndRow  'to find the last row of data
    For a = 1 To 10    'this is the loop to cycle through the columns
        Cells(2, a).Select
        myvalue = ActiveCell.Value
        checkForError
        temp = LastRow
        While b < temp  'main loop to cycle through rows
            Ignore = False
            If myvalue < 0 Then
                factor = 1
            ElseIf myvalue > 0 Then
                factor = -1
                Else: Ignore = True  'where it is blank or N/A
            End If
            
            If Ignore = False Then  'we need to do something
                For n = 1 To 5
                    ActiveCell.Offset(n, 24).Value = factor * ActiveCell.Offset(n, 11).Value
                    temp = temp - 1
                Next n
                ActiveCell.Offset(n - 1, 0).Select
            End If
            ActiveCell.Offset(1, 0).Select
            myvalue = ActiveCell.Value
            checkForError
            b = b + 1
        Wend
        b = 1   'reset b as the placeholder
    Next a
End Sub

Sub checkForError()
    If IsError(myvalue) Then myvalue = 0
End Sub

**mkeys4** · 08-15-2013, 08:27 AM

Hi hamjam,

Thank you so so much. The code seems to run perfectly, except it isn't outputting the last pair (K:K&V:V).

One question about running the code: there seems to be three codes in one module, so do I have to run all three each time? Is there anyway to consolidate all three codes into one so I can just run one code and have it do all three parts?

**hamjam** · 08-15-2013, 09:37 AM

Hello there
1. Don't worry as the main block of code references the others, so you only need to run test
2. If it omits the last column, it is probably the counter stopping short, that can be easily fixed
3. You can change the range references to named ranges, this was not provided from the info thus far. It is not clear whether the names will refer to static ranges or not.

I can tweak it a bit when I get back to my PC

Regards

**mkeys4** · 08-15-2013, 09:40 AM

Great. Thanks for your help. Sorry I wasn't clear about the ranges. I'm constantly added or deleting rows in my workbook, so the ranges are never static.

**hamjam** · 08-15-2013, 12:01 PM

Hello
Just to get you finished with the values, you can change the variable "a" in the main loop to be from 1 to 11 instead of stopping at 10.
Having a look at the named ranges.

Regards

**mkeys4** · 08-15-2013, 12:10 PM

Awesome that did the trick!! I changed n=1 to 11 as well. Is that correct?

**hamjam** · 08-15-2013, 12:43 PM

Hello
I manually named the ranges and got the code to work with these names (block1 and block2). You could go similar route especially considering that the ranges are changing so it would be pointless to hardcode cell references.

Keep well

**mkeys4** · 08-15-2013, 12:57 PM

I tried to replace A12 with a positive number to see if the code would multiply L13:L17 by negative one, and it doesn't. Does it work for you?

**hamjam** · 08-15-2013, 12:13 PM

Absolutely correct.
Still testing the named ranges.

**mkeys4** · 08-15-2013, 12:52 PM

I need the ranges to be named as cell references, however. When I use this code on my workbook, how will it know what block1 refers to?

**mkeys4** · 08-15-2013, 01:14 PM

I'm hoping for something like

Const block1 As String = "A:K"
Const block2 As String = "M:W"

**hamjam** · 08-15-2013, 01:59 PM

Hello
There are 2 ways I know to name ranges. The shortcut / method 1 is to select a block of cells and go to the top left block above the A1 position and the name of the cell is shown. You can click on the arrow there to see any other defined names, should see block1 + 2. Selecting the name, selects the cells making up that range.
Method 2 - in Excel 2010, you can go > formulas > Defined names
There are a few options, to define a range, create from selection and name manager. For existing ranges, open the name manager and you should see the names and their cell definitions.

Aaaahhh - I forgot the "factor" in the new formula. Just add it in and re-run. The downside of replacing code without looking at the previous code.
(look for this piece)

                    Range("block2").Item(currRow + n, a).Value = Range("block1").Item(currRow + n, a).Value * factor

I thought you said the ranges dynamically change, your latest post implies that the columns remain the same and perhaps only the number of rows change. You could try something like :

HTML Code:

As mentioned before, this will not be any quicker in code than using the shortcut above. I prefer to stick to automating the items that provide benefit from automation, i.e. you will get to your results quicker with code than using the Excel functions. In truth, the Excel functions are just already defined code presented in a nice way.

Ok, seems to wrap up the questions

Keep learning, I still have much knowledge to cover

**mkeys4** · 08-15-2013, 03:12 PM

Hi hamjam,

The only issue that I'm having with the code is that it doesn't know how to handle the N/As in L:V. Instead of just outputting a blank, the code stops. It runs in the attached sheet because the N/As happen to be on days that aren't outputted by the code. For example, L23 has an N/A, but the code starts on row 24. I need the code to output a blank with there;s an N/A in L:V

**mkeys4** · 08-15-2013, 08:43 PM

When I tryt to use this on a different worksheet, it doesn't work. The only difference is that the data starts are A3. Now, however, it's populating way to many cells with data, as if A:J is not being referenced at all. Here's what I changed the code to:

Public LastRow As Integer  'used as a constant once its value is known
Public myvalue As Variant  'passed between routines, so need global declaration

Sub DetermineEndRow()  'this works out the longest column from columns A to K, so runs just at start
    Dim temp As Long
    temp = 1
    Range("A3").Select
    For n = 1 To 11
        currentRow = ActiveCell.Row
        temp = Selection.End(xlDown).Row
        If temp < 3000 Then  'I assume the list is less than 3000 rows long
            LastRow = temp
        Else: temp = currentRow
        End If
        ActiveCell.Offset(LastRow - currentRow, 3).Select
    Next n
End Sub

Sub test()
    DetermineEndRow  'to find the last row of data
    For a = 1 To 11    'this is the loop to cycle through the columns
        Cells(3, a).Select
        myvalue = ActiveCell.Value
        checkForError
        temp = LastRow  'initialise value
        While b < temp  'main loop to cycle through rows
            Ignore = False
            If myvalue < 0 Then
                factor = 1
            ElseIf myvalue > 0 Then
                factor = -1
                Else: Ignore = True  'where it is blank or N/A
            End If
            
            If Ignore = False Then  'we need to do something
                currRow = ActiveCell.Row
                For n = 1 To 5
                    Range("PnLStDev").Item(currRow + n, a).Value = Range("LogChanges").Item(currRow + n, a).Value * factor
                    temp = temp - 1    'reduce the rows to loop by the ones that are skipped
                Next n
                ActiveCell.Offset(n - 1, 0).Select
            End If
            ActiveCell.Offset(1, 0).Select
            myvalue = ActiveCell.Value
            checkForError
            b = b + 1
        Wend
        b = 1   'reset b as the placeholder
    Next a
End Sub

Sub checkForError()
    If IsError(myvalue) Then myvalue = 0
End Sub

**HaHoBe** · 08-16-2013, 12:51 AM

Hi, mkeys4,

maybe instead of mulitple subs just like this:

Sub EF947349()
Dim lngCounter As Long
Dim lngCopy As Long
Dim lngMultiply As Long
Dim lngColumn As Long

For lngColumn = 1 To 11
  For lngCounter = 1 To Cells(Rows.Count, lngColumn).End(xlUp).Row
    If IsNumeric(Cells(lngCounter, lngColumn).Value) Then
      lngMultiply = IIf(Cells(lngCounter, lngColumn).Value > 0, -1, 1)
      For lngCopy = 1 To 5
        If IsNumeric(Cells(lngCounter + lngCopy, lngColumn + 11).Value) Then
          Cells(lngCounter + lngCopy, lngColumn + 24).Value = Cells(lngCounter + lngCopy, lngColumn + 11).Value * lngMultiply
        End If
      Next lngCopy
      lngCounter = lngCounter + 5
    End If
  Next lngCounter
Next lngColumn
End Sub

Ciao,
Holger

**mkeys4** · 08-16-2013, 07:48 AM

Thanks Holger. Are these ranges named? How will the code know what's what?

**mkeys4** · 08-16-2013, 09:38 AM

I'm just not clear about which ranges

Dim lngCounter As Long
Dim lngCopy As Long
Dim lngMultiply As Long
Dim lngColumn As Long

are referring to.

As the code is now, it's outputting the first value in the original cell into all five cells. It needs to output the different values of the original five cells.

**HaHoBe** · 08-16-2013, 12:21 PM

Hi, mkeys4,

Are these ranges named?

No need for taht in my view.

How will the code know what's what?

By the way the loops are worked on. Starting with Column A working down that column than go to Column B to do the same.

I'm just not clear about which ranges

Dim lngCounter As Long
Dim lngCopy As Long
Dim lngMultiply As Long
Dim lngColumn As Long

are referring to.

None as I only dimmed the variables for certain situations:
lngCounter is the counter to loop through the cells in a row
lngCopy is the counter from 1 to 5 for copying the values over
lngMultiply is the indicator whether to just copy or multiply with minus 1
lngColumn will cover the columns to look at.

As the code is now, it's outputting the first value in the original cell into all five cells.

Not in the file I created from your basic example (and not by looking at the code pasted here).

The best practise here would be to place the line

Option Explicit

at the top of the module in which the code resides. This will indicate any typos which might have happened when not copying the code to a standard module.

Ciao,
Holger

VBA loop to multiply by negative one

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Re: VBA loop to multiply by negative one

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If cell is negative multiply by -1 formula

[SOLVED] VBA Loop to check range for Negative number

If criteria match, multiply then sum across multiply worksheets

Macro Loop until Negative number is reached and perform an interpolation

[SOLVED] Trying to execute function after negative if conditional nested in For each loop

Bookmarks

Bookmarks

Posting Permissions