VBA divide two tables?

[Dimitris254]

i have two tables (not Excel tables, but data in a table format) like this: Imgur: The most awesome images on the Internet
i'd like to divide table "part" with table "whole", so that i get the percentages like this: Imgur: The most awesome images on the Internet

there is no extreme formula here, just M4/B4 in cell B4, M5/B5 in cell B5 etc. (formatted as percentage)

is it possible to grab the whole range of table "part" and divide it by table "whole", ie. .Range(M4:U13) / .Range(B4:J13) ?
or the division has to be cell by cell?

 

[StephenCrump]

If you're happy using an array formula ...

Table1: =A2:B3
Table2: =D2:E3

Output: {=Table2/Table1} Array-entered

Excel 2010
	A	B	C	D	E	F	G	H
1	Table1			Table2			Output
2	10	20		2	2		20%	10%
3	30	40		6	20		20%	50%
Sheet1

 

[Dimitris254]

i'm looking for a macro, since this action is part of a bigger macro

as aforementioned, the following does not work ("type mismatch" error)

With ActiveSheet

    .Range("B4:J13") = .Range("M4:U13") / .Range("B4:J13")


End With

 

[Marcelo Branco]

Assuming data in Sheet1 results in Sheet2 maybe this

With Sheets("Sheet1")
    Sheets("Sheet2").Range("[B]B[/B]4:J13") = Evaluate( _
        Replace("@" & .Range("M4:U13").Address & "/@" & .Range("B4:J13").Address, "@", .Name & "!"))
End With

Hope this helps

M.

 

[Dimitris254]

i've heard stories told in the shadows about Evaluate, now i see it with my eyes
if you could explain the logic too, that would great

but in any case it does work perfectly, thanks!

 

[Marcelo Branco]

You are welcome. Glad to help

About the logic:
The code line just builds the formula_string used by Evaluate method. Observe that I used Replace to substitute "@" by the sheet name concatenated with the exclamation mark

About Evaluate
Take a look at
https://fastexcel.wordpress.com/201...to-make-excels-evaluate-method-twice-as-fast/

M.

 

[Dimitris254]

one quick follow-up: some cells have #DIV/0 (expected), how do i integrate this into the Evaluate, so that these cells return 0 ?

 

[Marcelo Branco]

one quick follow-up: some cells have #DIV/0 (expected), how do i integrate this into the Evaluate, so that these cells return 0 ?

Try

With Sheets("Sheet1")
    Sheets("Sheet2").Range("B4:J13") = Evaluate( _
        Replace("=IFERROR(@" & .Range("M4:U13").Address & "/@" & .Range("B4:J13").Address & ",0)", "@", .Name & "!"))
End With

M.

 

[StephenCrump]

i'm looking for a macro, since this action is part of a bigger macro

Sorry, didn't notice the reference in the heading to "VBA".

Here's another way you could do it:

With Sheets("Sheet2").Range("B4:J13")
    .Formula = "=IFERROR(Sheet1!M4/Sheet1!B4,0)"
    .Value = .Value     'optional
End With

 

[Dimitris254]

cheers all