Automating Excel - code doesn't execute on 2nd loop iteration

Hot Topic

RegEx Question: Match whole word from substring
Date format VB 2005 beta
Detect and STOP shutdown in a VB.net 2003 Process
Web App no longer runs after Windows Update
How do I edit properties of a dataset from XML Schema (xsd file)?
How do you get the "Solution Configurations list box" back? HELP!!!!!!!!!!!!!!!
Operator Overloading and Enumerations?
compiler and unexpected error (cqqueryeditquerysaveinterfaces.cpp)
Graphics Persistence
Command windows

Visual Basic

question about installation of source safe 6, can it work with visual basic.net 2003?
Where should file <Addin name>.addin be copied to when deploying VS 2005 Addin?
Convert each record in an ArrayList to an array of strings
Sending an Email containing a Hyperlink
Can't Run Inside IDE
Adding Value and text to a Listbox or a combobox
passing a dimensioned array values as argument to a procedure
VB 2005 Compiler Optimization Discussion
Delete old data sources
Get information from database

Automating Excel - code doesn't execute on 2nd loop iteration

Hello all.

I have written code in VB.NET to automate Excel. This portion will copy a range on 1 sheet, find the last cell in a particular column on a 2nd sheet, paste the range and then sort the data so to remove any empty cells. The problem is that the code executes the first time in the loop (index = 0) but not the 2nd time although while stepping through it, it appears that it should work fine. It works for the 3rd iteration on to 15 or above, just not on the 2nd time it loops index = 1). I put break points in the code and all of the values appear to be set correctly and there is no error. What happens is that the code trys to activate a particular cell (the row is the correct value i.e. 411) but it activates a different cell (i.e. 3) and overwrites data. It seems that it just ignores the 2nd iteration of the loop all together even though I can step into it during the 2nd loop. Any help is appreciated!



Answer this question

Automating Excel - code doesn't execute on 2nd loop iteration

  • JeffRusso

    In order to help you solve this issue it is necessary to see the actuall code you are using...specifically the procedure with the loop you mention...

  • safra911

    I am mistaken - Unfortunately correcting the erroneous value in my matrix did not fix the problem. I'm back to square one. Any ideas

  • Sumedh Barde - MSFT

    That is exactly what it is . . . Thanks!!!! Now I just have to figure out why there is a zero in that cell rather than the true value (ugh). I appreciate your help!

  • miss_platina

    This is the code:

    Private Sub PasteHallPace(ByVal colend As Integer, ByVal rngst As Integer, ByVal rnged As Integer)

    Dim colindx As Integer, strng As Integer, endrng As Integer

    Dim s_strng As String, s_endrng As String

    Dim Hally1 As String, Hallz1 As String, Halla1 As String

    Dim sortrngmtrx As Microsoft.Office.Interop.Excel.Range

    Dim paceHallrange1 As Microsoft.Office.Interop.Excel.Range

    Dim Hallws As Microsoft.Office.Interop.Excel._Worksheet

    Dim Hallrowend1 As Integer, Hallend_sortrng As Integer

    Dim Hallrng1len As Integer, Hallrng2len As Integer, rngHallstrt As String, rngHallend As String, Hall_keyrng As String

    strng = rngst

    endrng = EventMatrix(0, 0) - 1

    For colindx = 0 To colend

    s_strng = "G" & CStr(strng)

    s_endrng = "G" & CStr(endrng)

    exlmetric_ppwb.Worksheets(1).activate()

    exlmetric_ppwb.Worksheets(1).range(s_strng & ":" & s_endrng).copy()

    exlmetric_ppwb.Worksheets(3).activate()

    paceHallrange1 = exlmetric_ppwb.Worksheets(3).range("E1")

    Hally1 = paceHallrange1.End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).Offset(1, 0).AddressLocal

    Hallz1 = Len(Hally1)

    Halla1 = Microsoft.VisualBasic.Right(Hally1, Hallz1 - 3)

    Hallrowend1 = CInt(Halla1)

    exlmetric_ppwb.Worksheets(3).cells(Hallrowend1, 5).activate()

    Hallws = exlmetric_ppwb.Worksheets(3)

    Hallws.Paste()

    Hallrng1len = Len(s_strng)

    Hallrng2len = Len(s_endrng)

    rngHallstrt = Microsoft.VisualBasic.Right(s_strng, Hallrng1len - 1)

    rngHallend = Microsoft.VisualBasic.Right(s_endrng, Hallrng2len - 1)

    Hallend_sortrng = Hallrowend1 + (CInt(rngHallend) - CInt(rngHallstrt))

    sortrngmtrx = exlmetric_ppwb.Worksheets(3).range("E3:E" & CStr(Hallend_sortrng))

    Hall_keyrng = "E3:E" & CStr(Hallend_sortrng)

    sortrngmtrx.Sort(key1:=exlmetric_ppwb.Worksheets(3).Range("E3"), order1:=Microsoft.Office.Interop.Excel.XlSortOrder.xlAscending, Header:=Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, Orientation:=Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns)

    strng = EventMatrix(1, colindx) + 2

    If EventMatrix(0, colindx + 1) = 0 Then

    endrng = rnged

    Else

    endrng = EventMatrix(0, colindx + 1) - 2

    End If

    Next

    End Sub


  • tarwin

    I'm not sure what EventMatrix Does but I'll bet your problem resides within it when colindx = 1

    EventMatrix(0, 2) = 0



  • Automating Excel - code doesn't execute on 2nd loop iteration
Answer this question