variable using formula

[Ingemar]

Hello Excel users

The following two rows works fine. They check if value in cell C4 is correct (202102) and return the value in a modified shape to the cell A1 (2021-02).

Range("A1").Select
ActiveCell.FormulaR1C1 = "=IF(AND(ISNUMBER(R[3]C[2]),LEN(R[3]C[2])=6),LEFT(R[3]C[2],4)&""-""&RIGHT(R[3]C[2],2),"""")"


I have tried to create a variable, using the formula below, which is a minor modification from the formula above, but I only get the result "FALSE" in the Message box.

Yearandmonth = FormulaR1C1 = "=IF(AND(ISNUMBER(R[3]C[2]),LEN(R[3]C[2])=6),LEFT(R[3]C[2],4)&""-""&RIGHT(R[3]C[2],2),"""")"
MsgBox Yearandmonth

I am grateful for any help.

Kind regards

Ingemar

 

[StephenCrump]

Perhaps:

Dim Yearandmonth As String

With Range("A1")
    .Formula = "=IF(AND(ISNUMBER(C4),LEN(C4)=6),LEFT(C4,4) & ""-"" & RIGHT(C4,2),"""")"
    Yearandmonth = .Value
End With

'or

With Range("C4")
    If (IsNumeric(.Value) And Len(.Value) = 6) Then Yearandmonth = Left(.Value, 4) & "-" & Right(.Value, 2)
End With

 

[Ingemar]

Dear Stephen,

I have tested the first alternative and it works great. T H A N K Y O U very much for the help!


Kind regards

Ingemar

 

[StephenCrump]

You're welcome, I'm glad it works for you.