VBA text functions

[theta]

Hi...I have a named ranged on my sheet that is called "UserTrafficPeriod"

It is a number cell but formatted "0000" - so if I enter 311, it is displayed as 0311 (meaning MARCH 2011).

This is a traffic period, and must be entered in this format.

Now in VBA I need to compare this traffic period to another cell.

To compare them directly I need to flip the year / date to ensure the <= compare works. But not sure how to do it in VBA. In formula I would do it like this :

=RIGHT(TEXT(UserTrafficPeriod,"0000"),2)&LEFT(TEXT(UserTrafficPeriod,"0000"),2)

Would give me the (logically correct) result of 1103 (without this I would be looking at 311 which is less than 1210, although this would not produce the desired result as they represent period)

But not sure on the VBA equivalent. Then could do a simple range("a1").value <= [VBA text bit goes here]

Any help appreciated!

 

[njimack]

It should be a simple case of replace TEXT with FORMAT

 

[theta]

Could you give me a VBA example...just so that i'm crystal clear.

The data set I work on is quite large so would take 5 minutes before the error even appeared (each time I run the code)

If sPMN = ThisWorkbook.Sheets("PMN").Cells(r, 1) AND ThisWorkbook.Sheets("PMN").Cells(r, 4) <= [format logic] Then

 

[njimack]

Something like this:
ThisWorkbook.Sheets("PMN").Cells(r, 4) <= Right(Format(Range("UserTrafficPeriod"),"0000")+0,2)

 

[Jon von der Heyden]

Personally I would compare date values as date values, and such I would convert both values to a date. E.g:

datevalue(format$("01" & range("a1").Text,"00\/00\/00"))

 

[theta]

Hi Jon...so what would be the best way to convert [m]myy into a true date

Inputs would be like :

910
1010
1110
1210
111
211
311

etc

 

[Jon von der Heyden]

Well I assumed that the numbers were formatted as "0000", rather than as date (which is why I was using the Text property).

datevalue(format$("01" & range("a1").Text,"00\/00\/00"))</pre>

However if those are actually dates, formatted as [m]myy then you can compare the date values directly by only using the Value property.

 

[theta]

Thanks. Solution works. But bit long winded, any way of shortening? Didn't see the need to define the named ranges as variables as I only use them once in the whole macro

If sPMN = ThisWorkbook.Sheets("PMN").Cells(r, 1) And ThisWorkbook.Sheets("PMN").Cells(r, 3) = ThisWorkbook.Sheets("INDEX").Range("UserHub") And ThisWorkbook.Sheets("PMN").Cells(r, 4) <= DateValue(Format$("01" & ThisWorkbook.Sheets("INDEX").Range("UserTrafficPeriod").Text, "00\/00\/00")) Then

Works perfectly though