VBA loop to multiply by negative one

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!

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

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.

Please Login or Register  to view this content.

Regards

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

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

Regards

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

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

Absolutely correct.
Still testing the named ranges.

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

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)

Please Login or Register  to view this content.

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

Hi, mkeys4,

maybe instead of mulitple subs just like this:

Please Login or Register  to view this content.

Ciao,
Holger

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.

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

Please Login or Register  to view this content.

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