issue with excel macro

[kerm007]

hello in our compagny we have an excel that [roce a word document plus entry in the outlook
some one came to me cause they enter line 250 it steems to not filling all line
here the macro : not sure why it's not working

Sub Main()

  StartForm.Show
 
  Dim Name As String
  Dim OwordDocument As WordDocument
  Dim OoutlookEvents As OutlookEvents
  Dim Item As String
  Dim Items() As String
  Dim element As Variant
 
  Sheet1.Activate
 
  If ActiveSheet.Cells(StartForm.LineTextBox, 1).Text <> "" Then
 
      Name = ActiveSheet.Cells(StartForm.LineTextBox, 1).Text
     
      Set OwordDocument = New WordDocument
      Set OoutlookEvents = New OutlookEvents

      OwordDocument.InsertTitle (Name)
     
      For i = 2 To 255
     
        If ActiveSheet.Cells(StartForm.LineTextBox, i).Text <> "" And i <> 30 Then
       
          Item = ActiveSheet.Cells(3, i).Text + " " + _
            ActiveSheet.Cells(2, i).Text + " " + ActiveSheet.Cells(4, i).Text + " " + _
            ActiveSheet.Cells(StartForm.LineTextBox, i).Text + ActiveSheet.Cells(5, i).Text + " " + _
            ActiveSheet.Cells(6, i).Text + ActiveSheet.Cells(7, i).Text + _
             " ,all tests"
             
          OwordDocument.InsertItem (Item)
          OoutlookEvents.SetEvent (Item)
       
        End If
     
      Next
     
      If ActiveSheet.Cells(StartForm.LineTextBox, 30).Text <> "" Then
       
        Items = Split(ActiveSheet.Cells(StartForm.LineTextBox, 30).Text, Chr(10))
       
        OwordDocument.InsertRetests




           
        For Each element In Items

            OwordDocument.InsertItem (element)
            OoutlookEvents.SetEvent ("Retest: " + element)
       
        Next element
       
      End If
 
  End If
 
End Sub

 

[kerm007]

she enter line 273 and it seems not to product the result wanted

 

[kerm007]

hello
any one know why it seems to stop when they enter line 273 ?
is it something in the macro ?
it was created long time ago by some one that is no longer with us
Thanks

 

[kerm007]

ok after discussion with the department
its an exel they fill in and it create a word document when they run the macro the problem is not the line but a column evrything they enter after the Column IV is not moved to the word document
is it possible there a limitation of the number of column they can fill in ?
Thanks

 

[PureBluff]

Is it possible for you to upload the workbook in question?

 

[kerm007]

yes i can
how to send a file ?
thanks

 

[kerm007]

yes i can
how to send a file ?
thanks

here the link to get the file

Stability Program Schedule.xlsm

Microsoft Excel Workbook

1drv.ms

Thanks
passing colum IV there is no data on word not sur why

 

[PureBluff]

Increase the 255 in this line

For i = 2 To 255

255 is Column IV, currently you're using to Col. 265 (JE), but if this is growing or changing, you can always calculate it at runtime.

This snippet will calculate the last used column from the right, of row 2

Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    End With

Which you can then reference:

For i = 2 To LastCol

 

[kerm007]

ok and i add it to the current code ?
Thanks

 

[PureBluff]

The snippet should be added fairly early on, after sheet1.activate, before the if statement.

Then replace
For i = 2 To 255
With
For i = 2 To LastCol