Function with FOR loop

[sindrefm]

Hi,

I have the following code:

Module1-----------------------------------------------------------------------------


Function FindColumn(x As String) As Long
FindColumn = Worksheets("Sheet1").Rows(1).Find(what:=x, LookAt:=xlWhole).Column
End Function

Function ConvertToNumber(x As Long) As Long
For i = 2 To .Cells(.Rows.Count, x).End(xlUp).Row
.Cells(i, x).Value = CDec(.Cells(i, x).Value)
Next
End Function


Module2------------------------------------------------------------------


Sub ConvertColumn()
Dim strVariable As String
Dim i As Long, ColVariable As Long

strVariable = "Variable"

With Worksheets("Sheet1")

ColVariable = FindColumn(strVariable)

ConvertToNumber (ColVariable)
End With
End Sub


What I want:
I want to find the "Variable" column at first row and convert all the cells in the column.
I have problems with the code marked as red, see attached picture for error message.

12976

Can someone help me on this?

[pike]

sindrefm (http://www.vbaexpress.com/forum/member.php?55220-sindrefm)



can you please add code tags to your post
Function return a value
so conevert the Function to a Sub routine
also the with statment will not work in the way you expect .. you wound need to pass the sheet name to the sub

Sub ConvertToNumber(x As Long)
For i = 2 To Cells(Rows.Count, x).End(xlUp).Row
Cells(i, x).Value = CDec(Cells(i, x).Value)
Next
End Sub

[Bob Phillips]

I would merge them into a single function


Function ConvertToNumber (ws As Worksheet, x As String) As Boolean
Dim colnum As Long

With ws

colnum = .Rows(1).Find(what:=x, LookAt:=xlWhole).Column

For i = 2 To .Cells(.Rows.Count, colnum).End(xlUp).Row

.Cells(i, x).Value = CDec(.Cells(i, colnum).Value)
Next
End Function

Sub ConvertColumn()
Dim strVariable As String

strVariable = "Variable"

Call ConvertToNumber (Worksheets("Sheet1"), strVariable)
End Sub

[Paul_Hossler]

I want to find the "Variable" column at first row and convert all the cells in the column.
I have problems with the code marked as red, see attached picture for error message.

To answer the SPECIFIC question, the error message is because it is just .Rows

The 'dot' is sort of just hanging there unqualified

It should be something like Worksheets("Sheet1").Rows.Count ....

Same for the unqualified .Cells later on

As the others have said, you might want to restructure your approach to simplify