Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.
OPthorsager
514 Views, 11 Replies

Find next empty row in excel sheet

Hi, 

i am in the midst of creating an external rule which i'll be using to "manage" all of my projects for my business. since the amount of clients i have accumulated is increasing i have started to lose track of where i save my files and such. 

 

Earlier this year i made a macro enabled workbook for a client which is used to automate sales quotes. essentially my excel macro is what i want, but less complex, as i dont really need alot of the extra cross checking which i had implemented in my Excel macro, where it would first check if the quote number existed, if not, then it would write the number to the next empty row on the reference excel file. however if the number existed it would look at the revision number afterwards to check if the revision number on the quote existed. if it didnt exist, but the client mentioned in a third column was different than the client mentioned in the existing quote number, then it would show a dialog box saying something was off, and have the user change either the client or the quote number. the entire code itself is around 400lines or so, so i won't go into the details about it.

 

what i want to do similarly in the rule im working on in iLogic is basically have inventor read through all rows (only column A) and check if there is any values stored there. if there is a value it will go to the next line and check if that one has a value. this process should repeat itself until it finds a row in which column A  is empty. All the solutions i have found would use a "for x to x" to look up an array. however i dont want to have a specified "to", since the list could technically go on forever.

below is a snippet of the Excel macro mentioned a little further up in my post. There is alot more going on between my definitions and the actual code posted below, however this is not relevant to what im trying to achieve, so i have left it out. 

    Dim ws1 As Worksheet
    Dim wb1 As Workbook

    Dim cellC6 As Range
    Dim cellD6 As Range
    Dim cellB13 As Range
    Dim cellH2 As Range
    Dim lastRow As Long
    Dim newValue As Long
    Dim foundRow As Range
    Dim foundValue As Boolean
 
    Set wb1 = ThisWorkbook
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set cellC6 = ws1.Range("C6")
    
    ' get data from cell D6 in Workbook 1
    Set cellD6 = ws1.Range("D6")
    ' get data from cell B13 in Workbook 1
    Set cellB13 = ws1.Range("B13")
    ' get data from cell H2 in Workbook 1
    Set cellH2 = ws1.Range("H2")
UpdateValues_Restart:
    ' open workbook2 without updating links
    Application.AskToUpdateLinks = False
    On Error Resume Next
    Set wb2 = Workbooks.Open(SetRef, UpdateLinks:=0)

        ' Check if the value exists in column A of Workbook2
        Set foundRow = ws2.Columns("A").Find(what:=cellC6.Value, LookIn:=xlValues, lookat:=xlWhole)
        
        If Not foundRow Is Nothing Then
            ' Value exists in column A of Workbook2
            ' Check if the text in column B matches the text from cell D6 in Workbook1
            foundValue = False
                                If IsEmpty(ws1.Range("C6")) Then
                                lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
                                newValue = Application.WorksheetFunction.Max(ws2.Range("A1:A" & lastRow)) + 1
                                cellC6.Value = newValue
                                MsgBox ("No quote number set, getting next free number")
                                GoTo UpdateValues_Restart
                                End If
            
            
            For Each cell In ws2.Range("A" & foundRow.Row & ":A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row)
                If cell.Value = cellC6.Value And ws2.Cells(cell.Row, 2).Value = cellD6.Value Then
                If Not ws2.Cells(cell.Row, 3).Value = cellB13.Value Then
                    foundValue = True
                    ElseIf ws2.Cells(cell.Row, 3).Value = cellB13.Value Then
                    MsgBox ("Quotenumber: " & cellC6.Value & cellD6 & "    " & cellB13.Value & vbCr & "This quote combination already exists for this project, please change letter indicator or change the project to update quote number")
                    wb2.Close SaveChanges:=False
                    Exit Sub
                    End If
                    
                    Exit For
                End If

            Next cell
            
              If foundValue Then


            
            
                ' Both value and text match, find the highest value and update C6 in Workbook1
                lastRow = ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row
                newValue = Application.WorksheetFunction.Max(ws2.Range("A1:A" & lastRow)) + 1

                'MsgBox IsEmpty(ws1.Range("C6"))
                
                ' Ask for confirmation to add a new row
                response = MsgBox("The QUOTE NUMBER already exists in the reference list with different LETTER INDICATOR. Do you want to add a new row?", vbYesNo + vbQuestion, "Confirmation")
                
                If response = vbYes Then
                    ' Update C6 in Workbook1
                    cellC6.Value = newValue
                    ' Add the new row to Workbook2
                    ws2.Cells(lastRow + 1, 1).Value = cellC6.Value
                    ws2.Cells(lastRow + 1, 2).Value = cellD6.Value
                    ws2.Cells(lastRow + 1, 3).Value = cellB13.Value
                    ws2.Cells(lastRow + 1, 4).Value = cellH2.Value
                End If
            

  As mentioned this is not the entire code i used for my Excel macro, some items might be left out. the posted code is more or less just to give you guys an idea of what i'm trying to achieve. 

 

below is the spreadsheet i made for my project. i have already sorted out the actual values that inventor has to put into the spreadsheet. however i cannot get it to loop until it finds an empty row and then use that specific row.

the data which is put into the spreadsheet is filled out in a global form, which shows up whenever you create a new part or assembly file. 

OPthorsager_0-1727006820440.png

 

i hope my post wasnt too confusing, if you want more info dont hesitate to ask.

any ideas or help is VERY much appreciated, since i'm still somewhat a beginner to iLogic.

 

 

 

Labels (3)