CODEWITHSUNDEEP
excelvba
karamell
karamell

Reputation: 713

VBA Excel handling ranges with for each

Quick question:

I set three ranges sourceRng1 and sourceRng2 targetRng. All equal of size.

I want to achieve this:

For each cell in targetRng
    cell.value = sourceRng1 / sourceRng2
next cell

where the index of sourceRng1 and sourceRng2 jumps one step in each iteration.

So if the first and second values of sourceRng1 and sourceRng2 are 4 7 and 1 3

then cell.value = 4/1 in the first iteration. cell.value = 7/3 in the second.

Any suggestion on how to do this operation?

Upvotes: 2

Views: 78

Answers (2)

Aprillion
Aprillion

Reputation: 22324

There is a non-VBA solution as well:

  1. select targetRng
  2. type formula =sourceRng1 / sourceRng2
  3. press Ctrl + Shift + Enter to enter this as an Array formula

enter image description here

Downside being you cannot edit - e.g. delete - parts of Array formulas separately, only when you select the whole area => make sure to use a different background color or comments to distinguish targetRng from other cells.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166196

Dim x as long
For x=1 to targetRng.cells.count
    targetRng.Cells(x).value = sourceRng1.cells(x).value / sourceRng2.Cells(x).value
next cell

Upvotes: 2

Related Questions