VBA combining cell values into text string

[winstela]

Hi All,

I am trying to add a formula in VBA that works in an excel sheet but not when I use the same formula in VBA

I am trying to combine cell value AC2 and AC1 separated with a dash and copy this to the last column

But I keep getting Runtime error 13 Type mismatch

Range("AC3:BB3").Formula = "=AC2&" - "&AC1"

Excel formula I am using

=AC2&"-"&AC1

Any ideas where I am going wrong?

Thank you

 

[rlv01]

Try this.

Range("AC3:BB3").Formula = "=AC2&" & """ - """ & "&AC1"

 

[winstela]

Thanks for your suggestion

When I add it to my codeI am receiving Runtime Error

my full code is

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:BB3")) Is Nothing Then ' watch all the cells in this range
    'For Each cel In Target ' do the next steps for each cell that was changed
Range("A1:BB3").UnMerge


End If
Range("AC3:BB3").Formula = "=AC2&" & """ - """ & "&AC1"
End Sub

If I debug below code line is highlighted

Range("AC3:BB3").Formula = "=AC2&" & """ - """ & "&AC1"

If I end the code and review the excel sheet the formula is there, any ideas?
Thanks

 

[veyselemre]

Range("AC3:BB3").Formula = "=AC2-AC1"

 

[RoryA]

You need to double any quotes that are actually part of the formula, so:

Range("AC3:BB3").Formula = "=AC2&"" - ""&AC1"

 

[winstela]

I am still getting the error message but formula is being added to the sheet correctly.

Any ideas why?

Thanks

 

[RoryA]

Which error message, and what is your full code?

 

[winstela]

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("A1:BB3")) Is Nothing Then ' watch all the cells in this range
    'For Each cel In Target ' do the next steps for each cell that was changed
Range("A1:BB3").UnMerge

End If


If Not Intersect(Target, Range("A1:BB3")) Is Nothing Then ' watch all the cells in this range
    'For Each cel In Target ' do the next steps for each cell that was changed
Range("AC3:BB3").Formula = "=AC2&""-""&AC1"

End If
End Sub

The error message is

which when I select Debug the following line is highlighted

Range("AC3:BB3").Formula = "=AC2&""-""&AC1"

I am copying a full sheet from another excel file and pasting this into the excel file .

Thank you

 

[RoryA]

If you change cells in a change event, you should disable events while you do:

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("A1:BB3")) Is Nothing Then ' watch all the cells in this range
application.enableevents = false
    'For Each cel In Target ' do the next steps for each cell that was changed
Range("A1:BB3").UnMerge
    'For Each cel In Target ' do the next steps for each cell that was changed
Range("AC3:BB3").Formula = "=AC2&""-""&AC1"
application.enableevents = true
End If
End Sub

 

[winstela]

Fixed

That is great thank you .