VBA code (macro) to replace array formula

M

MSchädler

Board Regular
Joined
Apr 27, 2017
Messages
95
I use this array formula in an excel sheet. The formula works fine in a small data base.
The database is currently 600 rows long and growing and now it takes time to calculate.
I think that a macro instead of the array formula could help to speed up the calculation.
Can somebody help me finding the macro code?

This is the general setup of my sheet:

Row/columnA to PQRST to WX
1DatesProject nameArray formula
220.09.2017Paris[=(xxx)]

<tbody>
</tbody>

This is the array formula in column X, limited to 1000 rows for speed

english:
[=IF(A2="","",IF(COUNTIF(S$2:S$1000,S2)=1,SMALL(IF(A$2:A$1000<>"",IF(ISNA(MATCH(A$2:$A$1000,X$1:X1,0)),A$2:A$1000)),1),INDEX(A$2:A$1000,MATCH(MIN(IF(S$2:S$1000=S2,Q$2:Q$1000)),IF(S$2:S$1000=S2,Q$2:Q$1000),0))))]
german:
[=WENN(A2="";"";WENN(ZÄHLENWENN(S$2:S$1000;S2)=1;KKLEINSTE(WENN(A$2:A$1000<>"";WENN(ISTNV(VERGLEICH(A$2:$A$1000;X$1:X1;0));A$2:A$1000));1);INDEX(A$2:A$1000;VERGLEICH(MIN(WENN(S$2:S$1000=S2;Q$2:Q$1000));WENN(S$2:S$1000=S2;Q$2:Q$1000);0))))]


Thanks for any suggestions.
M.
 
Last edited:
Sort by date Sort by votes
Re: Help needed to find a VBA code (macro)

MSchädler said:
I will test this vba it in my sheet tomorrow try to understand the codes and give you a feedback.
In the meantime enjoy your day and thanks again.
Marc
Click to expand...
OK, thanks. Interested to hear how it goes. :)
 
Upvote 0

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Re: Help needed to find a VBA code (macro)

Hello Peter
Here is my first reply. I quickly made a excel file with the settings described above (columns A to X) and used the titles (DB Nummer to Project ID), in row 1.
I then copied your vba code first into the workbook. Nothing happend.
Then I took the command and paste it into table 1 which is the worksheet. Nothing happend.

For some reason it does not start by itself. What do I do wrong?

I then went to the step by step (F8) command in the debug mode. There it went to the line "If d.exists(a(r, 3)) Then" and is just looped to "next r" and back to If.d.exists...


If d.exists(a(r, 3)) Then
If Split(d(a(r, 3)))(0) > a(r, 2) Then d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
Else
d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
End If
Next r

Also nothing was written into column X.
Do you have any idea what is wrong?
Thanks for you help.
M.
 
Upvote 0
Re: Help needed to find a VBA code (macro)

I didn't really understand your description of where you put the code. It should be in a standard Module:
With the worksheet active press Alt+F11 to bring up the vba window, use Insert -> Module and paste the code in the right hand pane that opens. Remove any other instances of the code in the workbook.

There was no mention of the code starting by itself. You need to run it when you want column X updated. One way is, from the worksheet, Alt+F8 to bring up the Macro dialog, Select the macro & click ‘Run’.

You described adding the headings but can you confirm that you also added the sample data as shown in A2:W13 in post #7 ?

Did you alter the at all? If so, please post the actual code you used.
 
Last edited:
Upvote 0
Re: Help needed to find a VBA code (macro)

Hello Peter
With your clarification and using Alt+F8 + enter I do get digits in column x.
Now we just have to find out, why the result is not as expected.

Here is the setup (sheet) that I'm using (not changed since post#7):

[TABLE="width: 1056"]
<colgroup><col><col><col span="13"><col><col><col><col><col span="3"><col span="2"><col></colgroup><tbody>[TR]
[TD]Col A =
DB Nummer[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Col P ISP[/TD]
[TD]Col Q
Date[/TD]
[TD]Col R Cluster[/TD]
[TD]Col S
Rollout-Project[/TD]
[TD]Col T
G-Strecken[/TD]
[TD]Col U
Lines[/TD]
[TD]Col V
Funnel-Status (FS)[/TD]
[TD]Col W
most recent entry[/TD]
[TD]Col X
Project ID
(returned with code)[/TD]
[TD]I did
expect[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.02.2016[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.10.2016[/TD]
[TD][/TD]
[TD]Rome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23.02.2017[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01.02.2016[/TD]
[TD][/TD]
[TD]Toronto[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.02.2015[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.10.2017[/TD]
[TD][/TD]
[TD]Rome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23.04.2014[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01.02.2016[/TD]
[TD][/TD]
[TD]Toronto[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.02.2016[/TD]
[TD][/TD]
[TD]Paris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05.10.2016[/TD]
[TD][/TD]
[TD]Rome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23.02.2017[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]01.02.2016[/TD]
[TD] [/TD]
[TD]Toronto[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Logic = expected results:
In Col A Paris is on Nummer 1 and 5 and 9. Paris with the "oldest" date (05/02/2015) is in Col A DB Nummer 5. Therefore all Rollout Project = Paris must get 5 in Col X.
In Col A Rome is on Nummer 2 and 6 and 10. Rome with the "oldest" date (05/10/2016) is in Col A DB Nummer 2. Therefore all Rollout Project = Rome must get 2 in Col X.
The logic is build this way.

I hope you can help me find why the result with the macro is not like expected --> see last Column.
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

I'm getting the red expected results. So clearly something is different between your sheet & mine.
Can you upload your sample workbook, with the code included, to a public file-share site (eg DropBox) and post a link here?
 
Last edited:
Upvote 0
Re: Help needed to find a VBA code (macro)

MSchädler said:
I hope it works.
Click to expand...
Yes, it worked, thanks. There are two issues:

1. You don't have the code in the correct place per my instructions in post #13. When you are in the vba window looking at the code, use the vba menu to Insert -> Module
That should create 'Module1' in a 'Modules' folder below the two items you have in the 'Microsoft Excel Objects' folder. Cut and Paste the code from the 'DieseArbeitsmappe' (ThisWorkbook) module into 'Module1'

2. There is an issue with the dates in your file that behave differently to my file and I am not exactly sure why. In any case make this change in the code & try it again.
Rich (BB code): 
If CLng(Split(d(a(r, 3)))(0)) > a(r, 2) Then d(a(r, 3)) = a(r, 2) & " " & a(r, 1)
 
Last edited:
Upvote 0
Re: Help needed to find a VBA code (macro)

Hi Peter
Thanks for your patience. I now understanding thatmy English knowledge with excel is limited.
Anyway, I did create a module1 in the sheet, inserted the code, made the correction you gave me and à it worked.EUREKA.
Is there a way to automate the vba command, when you enter a new "Rollout-Project" in column S?
Marc
 
Upvote 0
Re: Help needed to find a VBA code (macro)

MSchädler said:
Anyway, I did create a module1 in the sheet, inserted the code, made the correction you gave me and à it worked.EUREKA.
Click to expand...
At last! :)

MSchädler said:
Is there a way to automate the vba command, when you enter a new "Rollout-Project" in column S?
Click to expand...
Try this
1. Right click the 'Test' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("S")) Is Nothing Then
    With Application
      .ScreenUpdating = False
      .EnableEvents = False
      ProjectID
      .EnableEvents = True
      .ScreenUpdating = True
    End With
  End If
End Sub
 
Last edited:
Upvote 0
Re: Help needed to find a VBA code (macro)

Hello Peter
Thanks again. Now it works the way I wanted it thanks to you.
I now close this case and wish you a wonderful day or evening or both.:)

Kind regards
Marc
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
933
Alex Blakenburg
A
Y
  • Question Question
Macro to find & replace from source table - need to match whole source table string to partial string in workbook
Replies
1
Views
404
Kaper
Kaper
M
  • Question Question
VBA for different formulas to reduce the excel file
Replies
4
Views
207
MMM_84
M
I
  • Solved
VBA to remove formulas with values causing wrong calculations
2
Replies
12
Views
869
Peter_SSs
Peter_SSs
M
  • Question Question
Minimum, excl zero - non-array/range
Replies
7
Views
545
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top