VBA adding a formula in vba code

[Leske]

Hi,

Atm i have this code

.to = sh.Range("b2").Value

and would like to add the excel fomula -> =proper(b2)

I don't know how to intigrate this together, so any help would be great and some explenation would be appriciated as well.

Kind regards,

Les

 

[Norie]

Les

Do you mean you want to convert the value from B2 to proper case in your code?

If you do there are 2 options really:

1 Use Application.Proper, which basically replicates the PROPER worksheet function.

.to = Application.Proper(sh.Range("b2").Value)

2 Use VBA's StrConv function with the vbProperCase option.

.to = StrConv(sh.Range("b2").Value, vbProperCase

Not sure if there's much difference, if any, between the two so it's your choice really.

 

[Leske]

Hello Norie,
Indeed that is what i want and it works well
many thx for you quick respons

 

[Norie]

Oops, typo in the 2nd code I posted.

Should be this.

.to = StrConv(sh.Range("b2").Value, vbProperCase)