counting html checkbox values (Excel 2010)

[toshimarise]

I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:

1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.

I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).

Based on that, I recorded a macro to extract the htmlName of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.

There must be a more elegant way to do this.

Any help is much appreciated!

 

[toshimarise]

If anyone is interested, I found a much simpler way to solve the problem. It is extremely slow over large data ranges; but pasting html checkboxes into Excel is extremely slow to start with, so I have resigned myself to the wait.

Sub tickybox_values()
    '
' pulls values from tickyboxes, puts them in the underlying cell, and 
' deletes tickyboxes
    '
Dim o

    For Each o In ActiveSheet.OLEObjects
        If o.progID = "Forms.HTML:Checkbox.1" Then
            o.TopLeftCell.MergeArea.Value = o.Object.Checked
            o.Delete
        End If
    Next o
    '
'replaces true with x and false with a blank
    '
    For Each mycell In Range("c3:k1000")
    mycell.Replace What:="true", Replacement:="X"
    mycell.Replace What:="false", Replacement:=""
    Next

End sub

 

[jollyllp]

I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of ​

html

checkboxes. I need to do two things with the checkboxes and would like to do a third:

1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell......
​

________
jasicajhon

 

[jollyllp]

I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of ​

html

checkboxes. I need to do two things with the checkboxes and would like to do a third:

1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell......
​

________
jasicajhon

emirates check in online