Help with Vba in Excel

[213emi]

I'm am very new to using vba in excel and i'm trying to write a code that splits the names from one cell into 2 cells and leaves out the "," between the names and the middle initial

this is what i have so far but whenever i try to compile it highlight the first number Row = 2 and says "Compile error: Invalid outside procedure"

Does this mean i'm missing something?
I took a java class but a lot of the key words in vba are different

All my code

Dim Firstname As String
Dim Lastname As String

Dim x As Integer
Dim Row As Integer
Dim Col As Integer

Row = 2
Col = 1

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False
    
    x = InStr(2, Cells(Row, Col).Value, ",")
    Lastname = Left(Cells(Row, Col).Value, x - 1)
    Firstname = Mid(Cells(Row, Col).Value, Len(Cells(Row, Col).Value) - x - 1)
    
    Cells(Row, Col + 1).Value = Firstname
    Cells(Row, Col + 2).Value = Lastname
    Row = Row + 1

Loop

 

[=ODIN=]

i would not use a macro for something like this. worksheet formulas will work better.

if you have a name like this in cell A2 Doe,John
put this formula in b2 =LEFT(A2,FIND(",",A2)-1)
put this formula in c2 =MID(A2,FIND(",",A2)+1,100)

drag formulas down as far as necessary.

as to your macro, did you encapsulate it with the form

Sub  subNameOfYourChoice ()
your code
End sub

Also, your loop makes no sensse as far as I can tell.

Do

    Row = Row + 1
Loop Until Row > 65536 Or Cells(Row, Col).Value = ""

this loop is better.

 

[213emi]

i would not use a macro for something like this. worksheet formulas will work better.

if you have a name like this in cell A2 Doe,John
put this formula in b2 =LEFT(A2,FIND(",",A2)-1)
put this formula in c2 =MID(A2,FIND(",",A2)+1,100)

drag formulas down as far as necessary.

as to your macro, did you encapsulate it with the form

Sub  subNameOfYourChoice ()
your code
End sub

Also, your loop makes no sensse as far as I can tell.

Do

    Row = Row + 1
Loop Until Row > 65536 Or Cells(Row, Col).Value = ""

this loop is better.

Well the plan was for the

Do While IsEmpty(ActiveCell.Offset(0, 1)) = False

to stop the loop once there wasn't anymore data
is that not going to work?

and i realized after i posted in need it in a sub so that is fixed but now i need help with the loop

This is was extra credit in my database class but the deadline has already passed.
I just want to figure it out still

 

[=ODIN=]

the problem with your loop is you are saying basically that you want the loop to run while the cell next to the active cell is not empty. The problem is that your code never moves the activecell so it will only run the loop once or run it for infinite loops depending on weather or not the cell next to the active cell when the macro was run is empty or not.

My loop is saying run the loop as while cells in col 1 are not empty, or that the row number is less than or - to 65536 (the number of rows in version 2003 and prior).

 

[213emi]

when i try to compile that i get a "loop without Do" error

 

[=ODIN=]

when i try to compile that i get a "loop without Do" error

you've got to post the code you tried to run.