VBA code (macro) to replace array formula

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:
Re: Help needed to find a VBA code (macro)

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
OK, thanks. Interested to hear how it goes. :)
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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):

Col A =
DB Nummer
Col P ISPCol Q
Date
Col R ClusterCol S
Rollout-Project
Col T
G-Strecken
Col U
Lines
Col V
Funnel-Status (FS)
Col W
most recent entry
Col X
Project ID
(returned with code)
I did
expect
105.02.2016Paris195
205.10.2016Rome#102
323.02.2017New York1117
401.02.2016Toronto1124
505.02.2015Paris#95
605.10.2017Rome1102
723.04.2014New York#117
801.02.2016Toronto1124
905.02.2016Paris195
1005.10.2016Rome#102
1123.02.2017New York1117
12 01.02.2016 Toronto 1124

<colgroup><col><col><col span="13"><col><col><col><col><col span="3"><col span="2"><col></colgroup><tbody>
</tbody>

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)

I hope it works.
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)

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

Is there a way to automate the vba command, when you enter a new "Rollout-Project" in column S?
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

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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