Home>

### vba - how to calculate multi-column worksheetfunction results

You are trying to uniquely aggregate duplicate information in VBA.
All you need is the summary result, and you don't want the formula to remain in the cell.
Currently, the calculation formula is entered once as follows and then converted into text.
For this reason, I feel it was useless to write to the same range twice.

``````[Data duplicated! A: A] .Copy [a1]
[a: a] .RemoveDuplicates
Range (Cells (Rows.Count, 1) .End (xlUp) .Offset (, 4), [b1]) = Array (_
"= VLOOKUP (A1, duplicate data! A: B, 2, FALSE)", _
"= SUMIF (Duplicate data! A: A, A1, Duplicate data! C: C)", _
"= SUMIF (Duplicate data! A: A, A1, Duplicate data! D: d)", _
"= VLOOKUP (A1, Data duplicated! A: e, 5, FALSE)" _
)
[a1] .CurrentRegion = [a1] .CurrentRegion.Value``````

If the formula is only one column

``````[Data duplicated! A: A] .Copy [A1]
[a: a] .RemoveDuplicates
Calculation result = WorksheetFunction.VLookup (Range ([A1], [A1] .End (xlDown)), [Duplicate data! A: B], 2, False)
Range ([b1], [A1] .End (xlDown) .Offset (, 1)) = calculation result``````
I think you can do

, but
If i have multiple columns, you can't make an array to put the result well (reserve the size and put in for?),
On the contrary, I think it will be late.

If i have a good way to solve this, please let me know.

I thought that the idea was wonderful because it was processed concisely.
On the other hand, using VBA and relying on the actual formula on the worksheet side is not very good (the role assignment is not clear).
If you want to do it without using mathematical formulas, prepare an array of number of rows x number of columns (4), throw the calculation result into it, and finally assign it to the target range.
The difference in speed depends on the number of lines, and you will not know unless you actually try it.
Could you make it while asking questions like this? I think.
The conclusion depends on what the questioner wants to prioritize.

We want to prioritize the processing like VBA
→ Replace with an implementation that does not use mathematical formulas

We want to give priority to simplicity as processing
→ Current status

We want to prioritize processing speed
→ Create an array (or another method) and compare the processing speed