CODEWITHSUNDEEP
vbaexcel
Riet
Riet

Reputation: 1290

sub declaration visual Basic

I am really new with , so please forgive my slaughtering of the code. I'm trying to write a macro for excel (my first one), and I get "statement invalid outside of type block" (pointing at the first line). Here is my code:

Sub MakeHTMLTable()    
   Worksheets("Sheet1").Activate
   endRow As Integer

For Count = 1 To 200
    For CountY = 1 To 200
      If (!ActiveSheet.Cells(Count, CountY).Value.IsEmpty) Then
       ActiveSheet.Cells(Count, CountY).Value = "<td>" + ActiveSheet.Cells(Count, CountY).Value + "</td>"
      End If
    Exit For
Exit For

For i = 1 To 200
    If (!ActiveSheet.Cells(i, 1).Value.IsEmpty()) Then
        ActiveSheet.Cells(i, 1).Value = "<tr>" + ActiveSheet.Cells(i, 1)
    End If
Exit For

For x = 1 To 200
    If (!ActiveSheet.Cells(x, 1).Value.IsEmpty()) Then
        endRow = x
    End If
Exit For

For countAgain = 1 To 200
    If (!ActiveSheet.Cells(x, countAgain).Value.IsEmpty()) Then
        ActiveSheet.Cells(x, countAgain).Value = ActiveSheet.Cells(x, countAgain).Value + "</tr>"
    End If
Exit For
End Sub

I really don't understand, as the debugger fails on the line of computer generated code without even making it to mine. Have I missed ending an If" or For block?

I also realize that I'm probably reinventing the wheel. Any help on more appropriate built in functions would be appreciated.

Upvotes: 0

Views: 430

Answers (1)

qxn
qxn

Reputation: 17584

It looks like you've got quite a few syntax errors in your code.

In VBA, local variables are declared with the Dim keyword. So, the endRow declaration should look like this:

Dim endRow As Integer

For loops should end with the Next statement. So, your For loops should look like this:

For x = 1 To 200
    If (!ActiveSheet.Cells(x, 1).Value.IsEmpty()) Then
        endRow = x
    End If
Next

VBA uses the keyword Not instead of !, so, your conditionals should look like this:

If (Not (ActiveSheet.Cells(i, 1).Value.IsEmpty())) ...

Try removing most of the code and adding it back in line-by-line until all of it works. The VBA syntax can be cumbersome for those who aren't used to it.

Upvotes: 3

Related Questions