CONCATENATE & IF statement in VBA

[bloodmilksky]

Hi Guys,

I am currently working on a holiday request form and I am at the very early stages of it. I was just wondering if anyone could help me with integrating the below formula into VBA please as I am a complete newbie.

=IF(C21 ="Jan","01",IF(C21 ="Feb","02", IF(C21 ="Mar","03" ,IF(C21 ="Apr","04""Month Required")))

=IF(E21 ="Jan","01",IF(E21 ="Feb","02", IF(E21 ="Mar","03" ,IF(E21 ="Apr","04""Month Required")))

then Concatenate the results from C21 & D21 into H21
E21 & F21 into I21
And Change into Date format.

Any help would be greatly appreciated.

Bloodmilksky

 

[mole999]

if you just use something different consider a date of the event

use =MONTH(C21), no conversion necessary, not mucking around with written months and you have the real result

 

[steve the fish]

Whats in D21?

 

[bloodmilksky]

Hi Mole999,

what would the formula look like utilizing that method?

many thanks

Bloodmilksky

 

[mole999]

say Jon wanted to start leave on 21/6/2016 or 21 jun 2016 and that entered in a location example A1 then where you want you month number to appear you use =MONTH(A1) and it will return 6, no IF statements, just converting a date number to a month number

 

[bloodmilksky]

thats awesome thank you Mole999 ^_^ has given me a new take on the Form. if I wanted to set this up in VB without a formula, would you know the Syntax for that?

 

[mole999]

honestly i hit macro record and then just record me changing that cell which might give you
ActiveCell.FormulaR1C1 = "=MONTH(R[-1]C[-6])"
that works but it dosen't reference A1, it references 1 above and six left, also if the cell you are looking at is empty you will get a 1 so it might be mistaken for January,
and you need to change the cell to =IF(A1="","",MONTH(A1)) to test for a value,
that can be taken further to ensure you are getting a number there, it is seemingly endless