Need help changing the cell.

[aragon123321]

I am importing Data from somewhere and when it imports in it comes in like this.

[TABLE="width: 116"]
<colgroup><col></colgroup><tbody>[TR]
[TD]285 + 2

Is there any way I can change this to 287?


There are tons of Cells like these so anything manual will not work.


[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[V_Malkoti]

Do you have multiple calculations in the imported cell values? Different operators?

Try this. This code changes data in first column of Sheet2. You can change that according to your data.

Sub evalTest()
    Dim i As Long, k As Variant
    For i = 2 To Sheets([COLOR=#0000ff]"[B]Sheet2[/B]"[/COLOR]).Cells(Rows.Count, [B][COLOR=#0000ff]1[/COLOR][/B]).End(xlUp).Row
        k = Evaluate(Sheets("[B][COLOR=#0000ff]Sheet2[/COLOR][/B]").Cells(i, [B][COLOR=#0000ff]1[/COLOR][/B]).Value)
        If Not IsError(k) Then Sheets("[B][COLOR=#0000ff]Sheet2[/COLOR][/B]").Cells(i, [B][COLOR=#0000ff]1[/COLOR][/B]).Value = k
    Next
End Sub

Make sure you test it on a copy of the workbook first.

 

[aragon123321]

Ya there are multiple calculations but your code works! Thanks V_Malkoti Appreciate it brah!

 

[aragon123321]

only problem is that it fills in some cells that are black with the my 287

 

[RickXL]

Hi,

You could try this. The code below needs to be pasted in to a new Macro Module.

Sub Eval()

    Dim r As Range
    Dim first As String
    Dim temp As Variant
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        Set r = .Cells(1, 1)
        
        Do While True
            Set r = .Cells.Find(what:=" + ", after:=r)
            If r Is Nothing Or r.Address = first Then Exit Do
            If r.HasFormula Then
                If first = "" Then first = r.Address
            Else
                temp = Evaluate(r.Value)
                If IsError(temp) Then
                    If first = "" Then first = r.Address
                Else
                    r.Value = Evaluate(r.Value)
                End If
            End If
        Loop
        
    End With

End Sub

It uses the Find method to locate every " + " (i.e. space, plus, space). If it finds one, it evaluates that cell then looks for the next one.

Please try this on a copy of the real data first, just in case!

You can change the sheet name from Sheet1 to whatever your sheet is called. You could use ActiveSheet instead but that makes it even more dangerous

Plus signs in formulas should not be changed and neither should plus signs inside strings but there may be some I have not thought of. Hence the request to try it on a copy first.

Regards,

 

[V_Malkoti]

My sample data with blanks worked fine so I must have created my data differently. Can you post a block of your data cells so that I can test with that and make corrections in the code?

 

[RickXL]

Apologies.

I didn't check to see if anyone else had answered before I posted.

 

[aragon123321]

[TABLE="width: 116"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]OHT Active Count[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]285+2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
[TR]
[TD]285 + 2[/TD]
[/TR]
</tbody>[/TABLE]


here is a example of the code

 

[V_Malkoti]

No worries Rick. The more the merrier.

Aragon, I ran the code on this data and got correct results. On which lines did you get the incorrect result?

[TABLE="class: grid, width: 180"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]OHT Active Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]285+2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]285 + 2[/TD]
[TD="align: right"]287[/TD]
[/TR]
</tbody>[/TABLE]

 

[aragon123321]

Oh i had something wrong, but I got it now....thanks for your help your a excel god thank you.