convert excel formulas to vba [closed]
up vote
-2
down vote
favorite
i created this formulas for a specific work , i would like to convert them into vba , any help please?
formula 1:
=MIN(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 2:
=MAX(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 3:
=MIN(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
formula 4:
=MAX(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
excel vba
edited Nov 22 at 17:20
EvR
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
closed as too broad by Nathan_Sav, Comintern, Samuel Liew♦ Nov 30 at 13:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-2
down vote
favorite
i created this formulas for a specific work , i would like to convert them into vba , any help please?
formula 1:
=MIN(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 2:
=MAX(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 3:
=MIN(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
formula 4:
=MAX(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
excel vba
edited Nov 22 at 17:20
EvR
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
closed as too broad by Nathan_Sav, Comintern, Samuel Liew♦ Nov 30 at 13:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09
add a comment |
up vote
-2
down vote
favorite
up vote
-2
down vote
favorite
i created this formulas for a specific work , i would like to convert them into vba , any help please?
formula 1:
=MIN(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 2:
=MAX(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 3:
=MIN(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
formula 4:
=MAX(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
excel vba
edited Nov 22 at 17:20
EvR
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
i created this formulas for a specific work , i would like to convert them into vba , any help please?
formula 1:
=MIN(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 2:
=MAX(INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville2;Villes1;0));INDEX(Distances;MATCH(Q1_Ville1;Villes2;0);MATCH(Q1_Ville3;Villes1;0));INDEX(Distances;MATCH(Q1_Ville2;Villes2;0);MATCH(Q1_Ville3;Villes1;0)))
formula 3:
=MIN(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
formula 4:
=MAX(INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville2;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville1;Villes2;0);MATCH(Q2_Ville4;Villes1;0));INDEX(Distances;MATCH(Q2_Ville2;Villes2;0);MATCH(Q2_Ville3;Villes1;0));INDEX(Distances;MATCH(Q2_Ville3;Villes2;0);MATCH(Q2_Ville4;Villes1;0)))
excel vba
excel vba
edited Nov 22 at 17:20
EvR
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
edited Nov 22 at 17:20
EvR
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
edited Nov 22 at 17:20
EvR
1,2342313
edited Nov 22 at 17:20
EvR
1,2342313
edited Nov 22 at 17:20
EvR
1,2342313
1,2342313
asked Nov 22 at 12:37
Rabiai Tarek
12
asked Nov 22 at 12:37
Rabiai Tarek
12
asked Nov 22 at 12:37
Rabiai Tarek
12
12
closed as too broad by Nathan_Sav, Comintern, Samuel Liew♦ Nov 30 at 13:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Nathan_Sav, Comintern, Samuel Liew♦ Nov 30 at 13:05
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09
add a comment |
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09
add a comment |
2 Answers
2
active
oldest
votes
up vote
-1
down vote
accepted
Firstly, record your formula using Record_Macro, then you can see it visual basic editor in excel
answered Nov 22 at 12:48
Meh yit
141
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
add a comment |
up vote
0
down vote
Here a possible translation to VBA for your first formula, the others you can do yourself now
Function MinRabiai(dist As Range, Q1V1 As Range, Q1V2 As Range, Q1V3 As Range, Villes1 As Range, Villes2 As Range) As Long
With Application.WorksheetFunction
MinRabiai = .Min(.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V2, Villes1, 0)), _
.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V3, Villes1, 0)), .Index(dist, _
.Match(Q1V2, Villes2, 0), .Match(Q1V3, Villes1, 0)))
End With
End Function
Sub tst()
MsgBox MinRabiai(Range("Distances"), Range("Q1_Ville1"), Range("Q1_Ville2"), Range("Q1_Ville3"), Range("Villes1"), Range("Villes2"))
End Sub
answered Nov 22 at 14:57
EvR
1,2342313
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
-1
down vote
accepted
Firstly, record your formula using Record_Macro, then you can see it visual basic editor in excel
answered Nov 22 at 12:48
Meh yit
141
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
add a comment |
up vote
-1
down vote
accepted
Firstly, record your formula using Record_Macro, then you can see it visual basic editor in excel
answered Nov 22 at 12:48
Meh yit
141
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
add a comment |
up vote
-1
down vote
accepted
up vote
-1
down vote
accepted
Firstly, record your formula using Record_Macro, then you can see it visual basic editor in excel
answered Nov 22 at 12:48
Meh yit
141
Firstly, record your formula using Record_Macro, then you can see it visual basic editor in excel
answered Nov 22 at 12:48
Meh yit
141
answered Nov 22 at 12:48
Meh yit
141
answered Nov 22 at 12:48
Meh yit
141
answered Nov 22 at 12:48
Meh yit
141
141
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
add a comment |
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
and how can i make them all in one code and after it i click only on a button ? thanks
– Rabiai Tarek
Nov 22 at 13:41
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
For example:Sub Macro1() ' ' Macro1 Macro ' 'Formula1 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[1],R[-4]C[2])" 'Formula2 ActiveCell.FormulaR1C1 = "=MIN(R[-4]C[2],R[-4]C[3])" End Sub
– Meh yit
Nov 22 at 13:59
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
i recorded the first formula, and i need to do the same and combine them in one vba Sub minq1() ' ' minq1 Macro ' min q1 ' ' Range("C26").Select ActiveCell.FormulaR1C1 = _ "=MIN(INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville2,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville1,Villes2,0),MATCH(Q1_Ville3,Villes1,0)),INDEX(Distances,MATCH(Q1_Ville2,Villes2,0),MATCH(Q1_Ville3,Villes1,0)))" Range("C27").Select End Sub
– Rabiai Tarek
Nov 22 at 14:06
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
– Kurt Van den Branden
Nov 22 at 14:30
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
Change Distances to "Distances"
– Meh yit
Nov 22 at 20:47
add a comment |
up vote
0
down vote
Here a possible translation to VBA for your first formula, the others you can do yourself now
Function MinRabiai(dist As Range, Q1V1 As Range, Q1V2 As Range, Q1V3 As Range, Villes1 As Range, Villes2 As Range) As Long
With Application.WorksheetFunction
MinRabiai = .Min(.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V2, Villes1, 0)), _
.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V3, Villes1, 0)), .Index(dist, _
.Match(Q1V2, Villes2, 0), .Match(Q1V3, Villes1, 0)))
End With
End Function
Sub tst()
MsgBox MinRabiai(Range("Distances"), Range("Q1_Ville1"), Range("Q1_Ville2"), Range("Q1_Ville3"), Range("Villes1"), Range("Villes2"))
End Sub
answered Nov 22 at 14:57
EvR
1,2342313
add a comment |
up vote
0
down vote
Here a possible translation to VBA for your first formula, the others you can do yourself now
Function MinRabiai(dist As Range, Q1V1 As Range, Q1V2 As Range, Q1V3 As Range, Villes1 As Range, Villes2 As Range) As Long
With Application.WorksheetFunction
MinRabiai = .Min(.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V2, Villes1, 0)), _
.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V3, Villes1, 0)), .Index(dist, _
.Match(Q1V2, Villes2, 0), .Match(Q1V3, Villes1, 0)))
End With
End Function
Sub tst()
MsgBox MinRabiai(Range("Distances"), Range("Q1_Ville1"), Range("Q1_Ville2"), Range("Q1_Ville3"), Range("Villes1"), Range("Villes2"))
End Sub
answered Nov 22 at 14:57
EvR
1,2342313
add a comment |
up vote
0
down vote
up vote
0
down vote
Here a possible translation to VBA for your first formula, the others you can do yourself now
Function MinRabiai(dist As Range, Q1V1 As Range, Q1V2 As Range, Q1V3 As Range, Villes1 As Range, Villes2 As Range) As Long
With Application.WorksheetFunction
MinRabiai = .Min(.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V2, Villes1, 0)), _
.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V3, Villes1, 0)), .Index(dist, _
.Match(Q1V2, Villes2, 0), .Match(Q1V3, Villes1, 0)))
End With
End Function
Sub tst()
MsgBox MinRabiai(Range("Distances"), Range("Q1_Ville1"), Range("Q1_Ville2"), Range("Q1_Ville3"), Range("Villes1"), Range("Villes2"))
End Sub
answered Nov 22 at 14:57
EvR
1,2342313
Here a possible translation to VBA for your first formula, the others you can do yourself now
Function MinRabiai(dist As Range, Q1V1 As Range, Q1V2 As Range, Q1V3 As Range, Villes1 As Range, Villes2 As Range) As Long
With Application.WorksheetFunction
MinRabiai = .Min(.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V2, Villes1, 0)), _
.Index(dist, .Match(Q1V1, Villes2, 0), .Match(Q1V3, Villes1, 0)), .Index(dist, _
.Match(Q1V2, Villes2, 0), .Match(Q1V3, Villes1, 0)))
End With
End Function
Sub tst()
MsgBox MinRabiai(Range("Distances"), Range("Q1_Ville1"), Range("Q1_Ville2"), Range("Q1_Ville3"), Range("Villes1"), Range("Villes2"))
End Sub
answered Nov 22 at 14:57
EvR
1,2342313
answered Nov 22 at 14:57
EvR
1,2342313
answered Nov 22 at 14:57
EvR
1,2342313
answered Nov 22 at 14:57
EvR
1,2342313
1,2342313
add a comment |
add a comment |
Do you wanna place this formula back to a cell? (if yes keep in mind it's restricted to 255 characters). Or do you wanna translate this formula to a VBA function?
– EvR
Nov 22 at 13:38
i want to convert them in vba and clear the formulas from the cells
– Rabiai Tarek
Nov 22 at 14:09