Thread: Loop thru two ranges

Hi,

I'm trying to speed up my work in excel. I need to fill some cells in first range to get + values in 2nd range. Loop seems to be best way for it, but i'm not to good in VBA. In attached file you can find 3rd worksheet. Loop must go thru all cells in range B25:AN32 and change related cells in range B15:AN22 until cell value in bottom range will be > 0.

There is also 2nd problem I'd like to solve. In sheet KANBAN I've got simillar situation, but now i need to paste text into green range, to get values in bottom range until.

Both loops must run from top to bottom, column by column.

Thanks in advance for any help.

Can you elaborate? I don't understand.

Loop must go thru all cells in range B25:AN32

And do what?

and change related cells in range B15:AN22

How?

until cell value in bottom range will be > 0

What is the bottom range?

An illustration usually helps in these cases.

Hi,

Bottom range is B25:AN32. Macro must fill top table. On attached file you can see direction of lookup. When loop will find first cell with negative value will change value in related cell from top range +1 until cell value in bottom range will be >0 and then will move to next cell. Hope this looks clear

Thanks

Still not quite there. The cells in range B15:AN22 don't appear to have any connection with cells in the range B25:AN32. In fact, the latter reference the range B5:AN12. I should have made it clearer, but as an illustration, could you add the results you expect from the first few iterations?

Uploaded file with output and recorded macro how things should go. Once again thanks.

OK, my mistake, I didn't see before how the cells were connected. Try this:

Code:

Sub x()

Dim r As Long, c As Long, rTop As Range

Application.ScreenUpdating = False

Set rTop = Range("B15").CurrentRegion
With rTop
    Set rTop = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
    rTop.ClearContents
    For c = 1 To .Columns.Count
        For r = 1 To .Rows.Count
            Do While .Cells(r, c).Offset(.Rows.Count + 1).Value < 0
                .Cells(r, c).Value = .Cells(r, c).Value + 1
            Loop
        Next r
    Next c
End With

Application.ScreenUpdating = True

End Sub

Works great :), many thanks.
Is there is any chance you could help me solve similar problem in sheet KANBAN ?
This one is more complex (for me) because I need to paste proper text in proper sequence.

OK, again, can you clarify as I don't fully understand. Your file says

CELLS MUST BE PASTED UNTIL MIN / MAX WILL BE REACHED, THEN MUST GO TO NEXT COLUMN ON THE RIGHT

Which cells must be pasted and where must they go? Which cells must reach the min/max? As before, a small sample of expected results would help. Also, how, if at all, is this connected to the previous code?

Hi,

Sorry for not being precise. Output from previous sheet "'LAKIERNIA" in input for sheet KANBAN ( range1 B56:AL63) multiplied by 16. They are also input data for range2 B45:AN52. In range2 we have some negaitve values, which are connected with green marked fields (range D6:D37, F6:F37...). With the same way as it happend in sheet LAKIENIA they must be >0.
This example will be little twisted, but it works like this.
If you will paste "5D SAB" into cell D6, it will be catched in cell BI6 and will change its value into 1, then it will be multiplied by 16 in cell AC6. In result cell A56 will change by this16 and it will result, that cell J45 will increase its value. Next values 5D SAB must be pasted below cell D6 (D7:D12) until J45 > 0. Then loop is going row down. Macro "KANBAN' will ilustrate how it should work.
For range D6:D37 cells must be filled until D38 = D40. I've quit MIN/MAX - just TARGET.

OK, that makes things a bit clearer, but still a couple of questions:

- what exactly is the link between the ranges D6:D37, F6:F37 etc and the range B45:AN52? (These two sets are different sizes.) In other words, why does J45 being <0 mean you put "5D SAB" into D6? If B45 were <0 where would "5DSAB" go?

- does meeting target have priority over the lower range being >=0? Or if one is met is the other automatically met? Your calculations are quite involved so I have not followed this through all the way.

Let me explain what this is for. Range D6:D37 and F6:F37 represent welding sequence, left and right hand parts. Range B45: AN52 : each column represent truck - quantites that need to be welded in order to satisfy painting line (sheet LAKIERNIA) and painting line must satisfy trucks (parts for shipment in given quantites / sequence). I know what i should ship and what my stocks are ( sheet SUMMARY). Just need to find proper sequence and quantities for welding. In fist row, red cells shows truck 6 and top-bottom order of welding = 5D NSAB (cell G49). Each cell with value 3/5D N/SAB represent value of 16.

Back to Q1
When loop will find first cell with negative value (G49) should paste 5D NSAB into D6 in order to satisfy painting line with this item. By pasting this value into D6 cell BH6 will change its value into 1, then it will be multiplied by 16 (this is given batch for box on welding). Then it will be sum in cell AB39 and transfeted into cell AG43. Then it will be transfeted into sheet SUMMARY cell H20 and from all 3 shifts will be sum in K20. This sum will increase my overall quantities of item 5D NSAB LH and affect cell A60 in sheet KANBAN. This increase will also affect on range B45:AN52 cell B49,C49 .. G49 will be increased by this quantity and in result G49>=0. just to make clear cells A45:A52 represents values that should be pasted in given order (attached picture). According to sequence first 5D SAB will go into D16 (because it's LH). This was just example to picture links.

Q2.

If loop will meet target in 1st shift first column, should start filling cells in 2nd continuing sequence. In order words for LH columns: we are filling 31 cells in D6:D37, after target of filled cells was reached macro is moving into range K6:K37 and filling with 31 cells, then into range R6:R37 and after reaching target stop.

OK, will have a look later today or tomorrow.

Quite possibly I'm being dense, but I'm afraid I still cannot get my head completely round what is needed here.

Perhaps either you can find another way to explain it, or somebody else will chip in. This problem is not dependent on the other code so it is a self-contained question as far as I can see.