VBA Code transform numbers in specific columns into other numbers

[LNG2013]

I need some VBA code that will look at only specific columns, eg: ValueA11, ValueB11, ValueC11, etc... and transform its data from a single number into a number sequence.

I have about 50 columns I need to do this for, and there are other columns that have similar data that I don't want affected by this.

Eg. the first value in the column ValueA11 = 7, I want the value to be changed into 1 2 3 4 5 6 7. The second value is 4, so it should translate to 1 2 3 4. This sequencing would not go higher then 7. More examples below:


<STYLE type=text/css>
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</STYLE>
<TABLE style="WIDTH: 278px; HEIGHT: 234px" class=tableizer-table>
<TBODY><TR class=tableizer-firstrow><TH>ValueA11</TH><TH>ValueA11</TH></TR><TR><TD>7</TD><TD>1 2 3 4 5 6 7</TD></TR><TR><TD>4</TD><TD>1 2 3 4</TD></TR><TR><TD>2</TD><TD>1 2</TD></TR><TR><TD>1</TD><TD>1</TD></TR><TR><TD>3</TD><TD>1 2 3</TD></TR><TR><TD>6</TD><TD>1 2 3 4 5 6</TD></TR><TR><TD>5</TD><TD>1 2 3 4 5</TD></TR>


</TABLE>

 

[LNG2013]

Hey Jasmith,

I just emailed the sample over to you.

In the zipfile is the macro, sample csv and Output folder.

The CSV currently onlt goes down to row 6, but it has the potential to go past 1000. It would be awesome if the macro was only applied to the available data some how...

OK, I sent it. Can you give me an idea of the CSV's dimensions? That is, when you open it manually and do Ctrl-End, where do you end up?

 

[jasmith4]

Just one change in RavelNums. I had no idea that IsNumeric returns True on an empty cell!

            ' Assume data begin right below the header and are contiguous
            For Each ColCell In Hdr.EntireColumn.Cells
                [COLOR=red]If Not IsEmpty(ColCell.Value) Then[/COLOR] If IsNumeric(ColCell.Value) Then ColCell.Value = RavelNumber$(ColCell.Value)
            Next

 

[LNG2013]

Jas Brilliant!

That worked! Thanks for all of your help!