Problem with Range.Formula in VBA

[gandreso]

Below is my code that is a part of a bigger code. The problem here is that in column O there is a date in the following format yyyyww, for example 201645.
In my formula I would like to take the previous week so 201645 will be 201644 which is working as intended. The problem is that when a date is for example 201701 then I want the formula to give me 201652 but right now it gives me 201700 because IF(Right(O2,2)=01 is not true since it reads 01 as only 1. How can I fix this? Its giving me some headache.

With wbmodule.Sheets(ws1)
    With .Range("AB2:AB" & .Cells(.Rows.Count, 1).End(xlUp).row)
         .Formula = "=IF(Right(O2,2)=01,CONCATENATE(LEFT(O2,4)-1,52),O2-1)"
         .Value = .Value
    End With
End With

[LeoTaxi]

Try

With wbmodule.Sheets(ws1)
    For Each cl In .Range("O2:O" & .Cells(.Rows.Count, 1).End(xlUp).Row)
         If Right(cl.Value, 2) = 1 Then
            .Range("AB" & cl.Row).Value = Val(Left(cl, 4)) - 1 & "52"
         Else
            .Range("AB" & cl.Row).Value = cl.Value - 1
         End If
    Next
End With

Kind regards
Leo

[bradl822]

Hi Gandreso,

Small change to your formula using quotations will give you the desired outcome.

With wbmodule.Sheets(ws1)
    With .Range("AB2:AB" & .Cells(.Rows.Count, 1).End(xlUp).row)
         .Formula = "=IF(Right(O2,2)="01",CONCATENATE(LEFT(O2,4)-1,52),O2-1)"
         .Value = .Value
    End With
End With

[Richard Buttrey]

Personally I think you'd be better advised to use proper dates in your workbook rather than hybrid dates which appear to refer to a year and a week number. Can't you use say week ending dates, or week commencing dates and use a formula that deducted 7 days from the previous date? That is going to be far more flexible.

[gandreso]

First of all thanks for the responses.

@bradl822 - I was thinking about the same but the formula row gives me a syntax error. I think it has to be that you cant use quotations inside a quotations.
.Formula = "=IF(Right(O2,2)="01",CONCATENATE(LEFT(O2,4)-1,52),O2-1)"

[gandreso]

@Richard Buttrey - I totally agree with you Richard. The problem is that the data comes from an datasystem that has just that dateformat. But I agree, proper dates would be much more flexible.

[Richard Buttrey]

@Richard Buttrey - I totally agree with you Richard. The problem is that the data comes from an datasystem that has just that dateformat. But I agree, proper dates would be much more flexible.

Hi,

OK, understood, but notwithstanding I'd be inclined to have a pre-processing macro that creates a proper Excel date number from the datasystem 'dates in another column, and then use the proper date numbers in your macro so that you future proof and can use the dates for other analysis stuff should that ever be necessary.

So for instance with the system 'date' 201645 in A1 and assuming week 1 2016 is Monday 4 Jan

=DATE(LEFT(A1,4),1,4)+RIGHT(A1,2)*7

will return the date November 14 2016. Adjust the number 4 in the formula as appropriate for a different January 2016 w/c date.

[LeoTaxi]

Other 1

With wbmodule.Sheets(ws1)
    With .Range("AB2:AB" & .Cells(.Rows.Count, 1).End(xlUp).Row)
         .FormulaR1C1 = "=IF(VALUE(RIGHT(RC[-13],2))=1,LEFT(RC[-13],4)-1&""52"",RC[-13]-1)"
         .Value = .Value
    End With
End With

Cheers
Leo

[gandreso]

@LeoTaxi - Your code is working as I hoped for. The thing is that my datafile is about 300000 rows atleast, which meant that I wanted to avoid looping through the data if possible to avoid slow code. But I have run the code and your loop is not as slow as I thought it would be. Im gonna do some more tests, I come back later. Anyway thank you all.

[bradl822]

Sorry, my bad. Untested.

Try with double quotations, I believe I have done this before.
.Formula = "=IF(Right(O2,2)=""01" ",CONCATENATE(LEFT(O2,4)-1,52),O2-1)"

[LeoTaxi]

See #7


Cheers
Leo

[gandreso]

Oh I forgot about that I have to use double quotations. Both of the below is working so thanks! But which of the two should be run the fastest?

With wbmodule.Sheets(ws1)
    With .Range("AB2:AB" & .Cells(.Rows.Count, 1).End(xlUp).Row)
         .FormulaR1C1 = "=IF(VALUE(RIGHT(RC[-13],2))=1,LEFT(RC[-13],4)-1&""52"",RC[-13]-1)"
         .Value = .Value
    End With
End With

With wbmodule.Sheets(ws1)
    With .Range("AB2:AB" & .Cells(.Rows.Count, 1).End(xlUp).row)
         .Formula = "=IF(Right(O2,2)=""01"",CONCATENATE(LEFT(O2,4)-1,52),O2-1)"
         .Value = .Value
    End With
End With