Convert text to number in VBA

[lionginass]

Hello,


I have 4 month: January, February, March and April in range A2:D2

January actually means 1; February - 2; march - 3 and April 4

I need a code, that converts month name to number and return number

Lets say row 3:
February, february, April, April = 12 (2+2+4+4)
row 4:
January, April, January, March = 9 (1+4+1+3

replace all doesnt fit here, because i need those names in my worksheet

something with vlookop would work fine, but i dont need additional sheets or cells.. in other words i need that only month names and answer would be seen.

thanks for help

 

[steve the fish]

See if this works:

=SUMPRODUCT(--(MONTH(1&A1:D1)))

 

[lionginass]

Thanks for answer,

forgot to mention that I need it in VBA.. it is a part of a bigger code


Thank you

 

[steve the fish]

VBA is specific. What exactly does need it in VBA mean?

 

[lionginass]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]February[/TD]
[TD]April[/TD]
[TD]February[/TD]
[TD]April[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]March[/TD]
[TD]January[/TD]
[TD]January[/TD]
[TD]April[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]



I need to create vba code (function) to return value 12 in cell E1 and 9 in cell E2
(January =1, February =2, march = 3, april = 4)
Table must show month names so replace function does not fit here

thanks

 

[Phuoc]

Public Function SumMonth(Rng As Range) As Long
Dim Cel As Range, monthArr()
Dim v As Variant
monthArr = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
For Each Cel In Rng
         v = Application.Match(Cel.Value, monthArr, 0)
         If IsNumeric(v) Then SumMonth = SumMonth + v
Next
End Function

 

[steve the fish]

Why a VBA function when the native formula (function) does exactly that?

 

[Fluff]

Maybe

Function AddMonths(Rng As Range)
AddMonths = Evaluate("SUMPRODUCT(--(MONTH(1& " & Rng.Address & ")))")
End Function

Used like

Excel 2013/2016
	A	B	C	D	E
25	February	April	February	April	12
26	March	January	January	April	9
Quote

Cell Formulas
Range	Formula
E25	=AddMonths(A25:D25)