Assigning Formula value to a variable

[esumm1]

I need to format this number
<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=103 border=0><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 77pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=103 height=17>030411</TD></TR></TBODY></TABLE>
to this one
<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=98 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=98 height=17>110304</TD></TR></TBODY></TABLE>
and assign it to the variable Reval

I am using the following code:

Dim Reval As String
Reval = ActiveCell.FormulaR1C1 = "=Right(RC[-9], 2)&Left(RC[-9], 4)"

Reval is returning FALSE with this code.

This formula outside of vba returns correctly =RIGHT(F2176,2)&LEFT(F2176,4)

Any help is appreciated.

 

[VoG]

Try

Dim Reval As String
ActiveCell.FormulaR1C1 = "=Right(RC[-9], 2)&Left(RC[-9], 4)"
Reval = ActiveCell.Value

 

[esumm1]

Thank you VOG.

I might need a different formula altogether because I am trying to test the active cell against the value assigned to Reval but using the activecell syntax replaces this with Reval here is my full code.

Dim Val As String

Val = ActiveCell.Offset(0, -9).Value
Do While ActiveCell.Value = Val
ActiveCell.Offset(1, 0).Select
Val = ActiveCell.Offset(0, -9).Value
Loop
If Val = "" Then Exit Sub Else Call CutOne

I want Val check for the number conversion I mentioned earlier 030411 to 110304. I thought I would use the Reval variable to store this value and test against Val. If val = the number stored in reval then my loop should run.

 

[VoG]

Possibly you could use (untested)

Dim Reval As String
Reval = Evaluate("=Right(RC[-9], 2)&Left(RC[-9], 4)")

 

[esumm1]

returned type mismatch error

 

[VoG]

Oops. Lose the = sign

Dim Reval As String
Reval = Evaluate("Right(RC[-9], 2)&Left(RC[-9], 4)")

 

[esumm1]

same error

 

[VoG]

Maybe explain exactly what you are trying to do in words not code.

 

[esumm1]

I have two columns with dates in two different formats. I need to match these with eachother. I was trying to convert one format so it can be matched to the original. The code I posted works fine but I had to manually convert the date and then run it. I want the converted number assigned to a variable that can then be checked against the original.

Thanks for your patience

 

[VoG]

Try

Dim Reval As String
With ActiveCell.Offset(, -9)
    Reval = Right(.Value, 2) & Left(.Value, 4)
End With