how to convert column contents to number?

[antm79]

Hello All,

Im trying to convert the data in certain columns to number. I need to select the rows in those columns based on the rows counted in another column with a different heading, this is because there can be breaks in the data half way down the columns.

I'm obviously very new to VBA and would like to learn from any help. I have some sample code, If anybody could correct it and tell me where I'm going wrong It would be greatly appreciated.

The Code I have so Far is:

Sub ConvertTonumber()


'Convert Certain Columns to numbers
'Use the "x"column to Calculate how many rows are required to fill the columns.
Dim ColX As Range
Set ColX = Rows(1).Find(What:="ColX", _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
'Count the Rows in the "ColX" column
Dim J As Integer
J = ColX.Rows.Count


'Declare Headings of columns Where Text needs to be converted to 0.
Dim ConvertColTonumber As Variant, I As Long
ConvertColTonumber = Array("Y", "P", "B", "Z")
For I = LBound(ConvertColTonumber) To UBound(ConvertColTonumber)
 
'Set the location for the column headings to change
Dim FindHeadings As Range
Set FindHeadings = Rows(1).Find(ConvertColTonumber(I))


If FindHeadings Is Nothing Then Exit Sub
For Each Element In FindHeadings
Rows(J).Select
Selection.NumberFormat = "0"
Next
Next
End Sub

Many Thanks in advance,
Antm79

 

[JLGWhiz]

Not sure I completely uhnderstand what you are doing here:

Dim ColX As Range
Set ColX = Rows(1).Find(What:="ColX", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchDirection:=xlNext, _
MatchCase:=False)
'Count the Rows in the "ColX" column
Dim J As Integer
J = ColX.Rows.Count

But this would get you the last row with data in column x:

Dim lastRw As Long
lastRw = ActiveSheet.Range("X" & Rows.Count).End(xlUp).Row

lastRw here would be equivalent to what I think you want J to be in your code.

 

[antm79]

Hello,

Thanks for your reply. What I'm trying to achieve is declare an array of the column headings containing the data that I want to ensure is converted to number. With the array I want to write a loop to work through the column headings highlighting all the rows in those columns and then convert the selection to number. The columns containing the data to be converted have blanks in them, so to calculate the rows I need to format, I was planning on using a column that has a continuous data set in it and counting the rows in that one to the bottom of the data set("Colx"). Holding that information in a variable and using it in the loop("j" or "lastRow").

As your can see from the code I'm not too sure what I'm doing I was hoping someone could write it for me then maybe show me where I was going wrong

Thanks Antm79.

 

[Rick Rothstein]

If I understand what you are attempting to do correctly, this is the code I would use to do it...

Sub ConvertTonumber()
  Dim LastRow As Long, Col As Long, Header As Variant
  LastRow = Cells(Rows.count, "X").End(xlUp).Row
  For Each Header In Array("Y", "P", "B", "Z")
    On Error Resume Next
    Col = Rows(1).Find(Header, LookAt:=xlWhole, MatchCase:=False).Column
    On Error GoTo 0
    If Col > 0 Then Range(Cells(2, Col), Cells(LastRow, Col)).NumberFormat = "0"
  Next
End Sub

 

[antm79]

ADVERTISEMENT

Hello Rick,

Thanks for your help. I just tried your code but I keep getting a 'type mismatch' Error when I enter the actual column heading into this line of the code:

LastRow = Cells(Rows.Count, "X").End(xlUp).Row

I know its not your code because I tried an ordinary column heading like "F" and the error didn't occur. I have tried copying the column heading from the excel spreadsheet directly into the code in the VBA editor, this didn't work and I know the spelling is correct.

Do you have any idea what might be going wrong?

Many thanks
Antm79

 

[Rick Rothstein]

Hello Rick,

Thanks for your help. I just tried your code but I keep getting a 'type mismatch' Error when I enter the actual column heading into this line of the code:

LastRow = Cells(Rows.Count, "X").End(xlUp).Row

I know its not your code because I tried an ordinary column heading like "F" and the error didn't occur. I have tried copying the column heading from the excel spreadsheet directly into the code in the VBA editor, this didn't work and I know the spelling is correct.

Do you have any idea what might be going wrong?

Sorry, my fault... I misremembered your "ColX" heading as being the actual Column X and used that by mistake. Here is revised code that should work (I am now calculating the last row with data in it by a different method)...

Sub ConvertTonumber()
  Dim LastRow As Long, Col As Long, Header As Variant
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  For Each Header In Array("Y", "P", "B", "Z")
    On Error Resume Next
    Col = Rows(1).Find(Header, LookAt:=xlWhole, MatchCase:=False).Column
    On Error GoTo 0
    If Col > 0 Then Range(Cells(2, Col), Cells(LastRow, Col)).NumberFormat = "0"
  Next
End Sub

 

[antm79]

ADVERTISEMENT

Hi Rick,

Yes this works, thank you very much. I did have a couple of questions though if you don't mind?

In this part of the code:

If Col > 0 Then Range(Cells(2, Col), Cells(LastRow, Col)).NumberFormat = "0"

I'm assuming this statement selects the second cell and the last cell of the column once found, then applies the number format accordingly?

For this spreadsheet I don't think 'Col' will ever be less than '0' as I'm fairly certain its always in Column A in the export, but if that wasn't the case what changes could be made to the code to counteract that?

Thanks for your time,

Antm79

 

[Rick Rothstein]

In this part of the code:

 If Col > 0 Then Range(Cells(2, Col), Cells(LastRow, Col)).NumberFormat = "0"

I'm assuming this statement selects the second cell and the last cell of the column once found, then applies the number format accordingly?

One form of the Range command is this

Range(Cell1, Cell2)

and it forms a reference that includes Cell1, Cell2 and all the cells between them. That is the form of Range that I used in my code.

For this spreadsheet I don't think 'Col' will ever be less than '0' as I'm fairly certain its always in Column A in the export, but if that wasn't the case what changes could be made to the code to counteract that?

The test for Col>0 is for error handling purposes... if any one (or more) of the heading "Y", "P", "B", "Z" are missing, Col would equal 0 and my code would crash... testing for Col greater than 0 makes sure my code always works without raising any errors.

 

[antm79]

Hello Rick,

Thanks for the clarification,you have been a great help.

Regards,

Antm79