Visual Basic Forum for Visual Basic Programmers VB

[loureiro]

I really need help with the following error.

here is what I have for my script:

'returns the number of columns in the worksheet
Private Function findColumnNum() As Integer
Dim alphabet As String
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim count As Integer
count = 1

Do While Sheet1.Cells(1, Mid(alphabet, count, 1)) <> ""
count = count + 1
Loop

findColumnNum = count
End Function

Private Function findDataNum(column As String) As Integer
Dim count As Integer
count = 2

Do While Sheet1.Cells(count, column) <> ""
count = count + 1
Loop

findDataNum = count - 1
End Function

'returns a string array of the column names
Private Function parseRow(rowLength As Integer, columnNum As Integer) As String()
ReDim colNames(rowLength - 1) As String

For i = 0 To (rowLength - 1)
colNames(i) = Sheet1.Cells(columnNum, i + 1)
Next

parseColumnNames = colNames
End Function

Private Sub btnSave_Click()
If (txtLocation <> "") Then
Dim colNum As Integer
colNum = findColumnNum() - 1
Dim sColNames As Variant
sColNames = parseRow(colNum, 1)
Dim alphabet As String
alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim counter As Integer
counter = 0

Dim i As Variant
For Each i In sColNames
'counter = counter + 1
'Dim message As String
'message = "INSERT INTO " & m
'message = " VALUES("
'Dim dataNum As Integer
'dataNum = findDataNum(Mid(alphabet, counter, 1))
'Dim rowData As Variant
'
'Dim k As Integer
'For k = 2 To dataNum
' rowData = parseRow(colNum, k)
'
' Dim j As Integer
' For j = 1 To (colNum - 1)
' message = message & rowData(j) & ","
' Next
'
' message = message & rowData(colNum) & ");"
'Next
'
MsgBox ("Column: " & i)
Next i
Else
MsgBox ("You have not entered a location to save the SQL file!")
End If
End Sub

I keep getting an error at the following place:
Dim i As Variant
For Each i In sColNames

it is very annoying. I have done this already and have not had any problems. Can anyone please help!