[RESOLVED] For loop problem

[Resource Dragon]

This code works up to the start of the second iteration where SelectedArray(i) = 1 (this array can only take the values 0 or 1), then it crashes. How do I fix it?

For i = 1 To big_deep
If SelectedArray(i) = 1 Then
 
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("C:\My Documents\Geek Stuff\Document for Office Holder.doc")
    wrdDoc.Activate
    With wrdDoc
 
'    code that defines position_name & other stuff
 
ActiveDocument.Bookmarks("offr_name").Range.InsertBefore position_name

'  more code to populate document here
 
If Dir(ActiveWorkbook.Path & "\" & position_name & ".doc") <> "" Then
        Kill ActiveWorkbook.Path & "\" & position_name & ".doc"
        End If
        .SaveAs (ActiveWorkbook.Path & "\" & Left(position_name, 20) & ".doc")
        .Close ' close the document
    End With
 
wrdApp.Documents.Open ("C:\My Documents\Geek Stuff\Document for Office Holder.doc")
   For Each wrdDoc In Documents
    wrdDoc.Close SaveChanges:=wdSaveChanges
    Next wrdDoc
 
'    wrdApp.Close
    wrdApp.Quit
 
oXLSheet.Cells(i + 2, big_wide + 2).Select
oXLSheet.Cells(i + 2, big_wide + 2).Value = Format(Date, "d-mmm-yy")
MsgBox ("Document for " & position_name & " created")
 
 ' _________________________________________ end of creation of new doc
    
End If
ActiveWorkbook.Save
 
Next i
 
End Sub

The problem is that this all works fine for the first value of i for which SelectedArray(i) = 1 [members of SelectedArray can only take the values 0 or 1, this links up with the checkboxes the user has selected in the userform] but if the user selects two (or more) checkboxes, the routine crashes on its second pass.

The code that the routine doesn't like is the line:

ActiveDocument.Bookmarks("offr_name").Range.InsertBefore position_name

and the problem it claims it has is that there is no word document open, although in fact there is. (I inserted the message box down towards the end and also the save command to let the routine catch up with itself, but that doesn't fix the problem.)

(This code is being run from an excel file, hence the need to open an instance of Word.)

Thanks

[westconn1]

as you have already set a document object, you should avoid using activedocument

i would avoid opening and closing word within a loop, once opened, leave open until loop finished

you can try doevents after each word operation, to allow other processes to complete

[Resource Dragon]

OOPS! PLEASE IGNORE THIS. I POSTED IT IN THE WRONG PLACE. IT BELONGS WITH http://www.vbforums.com/showthread.p...2&goto=newpost. (I blame my mouse, think I'm going to have to get one that works reliably.) My apologies.

Strange. The code picks up the first cell as an error, despite the fact that the entry in this cell looks like "[email protected]" (This code is part of a larger routine that checks for data integrity, and similar coding works fine for the other columns.)

[Siddharth Rout]

Did you incorporate the suggestions Pete gave?

[westconn1]

you need to test the value of the cell as excel may have automatically converted an email address to a hyperlink

msgbox cells(x, y)
msgbox cells(x, y).text
msgbox cells(x, y).hyperlinks(1).texttodisplay

[Resource Dragon]

Unfortunately I haven't had time to look at Pete's suggestions yet but I will do so, I ended up busy with some other stuff at work today. I can't search VB Forums at work & I can't log into VB Forums there, either. I can however open up a link to see a particular discussion thread, so I e-mail the links to myself for reading at work when the problem is work-related.

[Resource Dragon]

I am going to mark this thread as resolved, as I suspect that Pete's suggestions (post no 2 in this thread) will solve the problem. (The project fell by the wayside - one of those ones where someon higher up the foodchain said that further instructions in regard to what the documents are meant to do would be coming out at some stage.)