Excel VBa to add equal sign

[Biz]

In column C2 and Down I have list of value with + sign but I can't get sum

<TABLE style="WIDTH: 83pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=110><COLGROUP><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 83pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=110>10 +2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>100+20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>60 +12 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>10 + 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>5+1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>5 + 1</TD></TR></TBODY></TABLE>

Is it possible use vba and add = sign so that I can get sum?

Biz

 

[Rick Rothstein]

@Biz,

I was so caught up in solving the problems at hand that I completely forgot about the "tbc" entries. I do have one simplification to the code... we can eliminate one Replace function call by making use of the function's Compare argument (which let's it ignore the casing for the letter "x"). I am also expanding the "tbc" test to make it able to handle upper case letters in case that should ever happen in the future and filling out the rest of the Len test (I think it is a bad idea to mix a Boolean comparison with a constant in a Boolean expression). So, the final code...

Sub DoTheMath()
    Dim Cell As Range
    For Each Cell In Range("C2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        If LCase(Cell.Value) <> "tbc" And Len(Cell.Value) > 0 Then
            Cell.Formula = "=--(" & Replace(Replace(Cell.Value, Chr(160), " "), "x", "*", , , vbTextCompare) & ")"
        End If
    Next Cell
End Sub

 

[mirabeau]

Hey Biz,

I really don't want to intrude on your fine thread, but you seemed to have in Post#3 a problem using Evaluate.

The Evaluate command is a useful one and inter alia (among other things) can often be used to avoid looping, which some contributors (not me, I like loops) seem to sometimes view with disfavor.

For your data as given in Post#1, consider the following code:

Sub sumsumz()
Dim zod
Set zod = Range("C2:C8")
zod.Value = Evaluate("""=""" & "&" & zod.Address)
End Sub

You can easily make this into a one-liner if you like.

 

[GTO]

Hi All,

Similar to mirabeau, I do not wish to intrude; you are getting great help from Rick. I would humbly submit that it appears that there are some unknowns, such as wacky non-printing characters, and in case, strings that are not formulas.

Whilst removing spaces is certainly an optional clean-up, I would still think validating the formula as readable to be a good idea.

Hope that is of help,,

Mark

 

[Weaver]

I'm not one for intruding either, but I was wondering why the need to add --() to the eventual formula - it seems to work fine if you just add an equals sign, but I might not have a very representative set of test data.

 

[GTO]

I'm not one for intruding either, but I was wondering why the need to add --() to the eventual formula - it seems to work fine if you just add an equals sign, but I might not have a very representative set of test data.

Bravo and thank-you Weaver. I was a little busy and didn't ask that excellent question.

@Rick Rothstein:

By my understanding, the first first unary would coerce a TRUE to -1 and the second would coerce to ABS. If you do not mind, how is it affecting the formula?

Thank you so much,

Mark

 

[Biz]

@Biz,

I was so caught up in solving the problems at hand that I completely forgot about the "tbc" entries. I do have one simplification to the code... we can eliminate one Replace function call by making use of the function's Compare argument (which let's it ignore the casing for the letter "x"). I am also expanding the "tbc" test to make it able to handle upper case letters in case that should ever happen in the future and filling out the rest of the Len test (I think it is a bad idea to mix a Boolean comparison with a constant in a Boolean expression). So, the final code...

Sub DoTheMath()
    Dim Cell As Range
    For Each Cell In Range("C2:F" & Cells(Rows.Count, "F").End(xlUp).Row)
        If LCase(Cell.Value) <> "tbc" And Len(Cell.Value) > 0 Then
            Cell.Formula = "=--(" & Replace(Replace(Cell.Value, Chr(160), " "), "x", "*", , , vbTextCompare) & ")"
        End If
    Next Cell
End Sub

Hi Rick,

Thanks for help again mate.

Biz

 

[Biz]

Hey Biz,

I really don't want to intrude on your fine thread, but you seemed to have in Post#3 a problem using Evaluate.

The Evaluate command is a useful one and inter alia (among other things) can often be used to avoid looping, which some contributors (not me, I like loops) seem to sometimes view with disfavor.

For your data as given in Post#1, consider the following code:

Sub sumsumz()
Dim zod
Set zod = Range("C2:C8")
zod.Value = Evaluate("""=""" & "&" & zod.Address)
End Sub

You can easily make this into a one-liner if you like.

Hi,

Tried your code but it crashed my file.

Biz

 

[mirabeau]

Hi,

Tried your code but it crashed my file.

Biz

Biz,

As indicated, that code snippet was only given to assist with your problems you outlined in Posts#1 and #3.

Specifically, if you copy your data from Post#1 and paste onto a blank sheet from C2 down, then run the code ... It doesn't crash anything, it evaluates the range just as you requested.

For your general file, I didn't want any involvement with that (haven't actually looked at it) since Rick seemed to be handling it very well.

 

[Biz]

Biz,

As indicated, that code snippet was only given to assist with your problems you outlined in Posts#1 and #3.

Specifically, if you copy your data from Post#1 and paste onto a blank sheet from C2 down, then run the code ... It doesn't crash anything, it evaluates the range just as you requested.

For your general file, I didn't want any involvement with that (haven't actually looked at it) since Rick seemed to be handling it very well.

Mate you are right Rick code is doing job just fine.

Biz

 

[BMAK1]

Hi
I tried the above script it works fine adds the "£" in the cells headed amounts however it pastes the "£" symbol in the whole column all the way down but I need it to work only in the active cells which I am importing in using an Macro. So therefore the amount of rows populated every week will vary on the number of payments per week. Any idea how I can make this work. I am new to creating and working with Macros.