CODEWITHSUNDEEP
excelvbawhile-loopexistsgoto
Pierre_CM
Pierre_CM

Reputation: 55

vba next iteration if cell content already passed

So following my code that loop through a table, connect to sql server and send the datas of the table I have one more little constraint: I send something like 5400 rows and 13 columns of data row by row, cell by cell (which is not that slow actually!) but I need to set on some cell the condition: if I already sent the cell's content (ex: a client number already sent in database) I don't want to insert it twice so goto next iteration. Here is a simplified version of my loop:

nrow = 1

While My_range.Offset(nrow).Value <> ""  'continue if cell's not empty

'connect to the db and create a command that send the data

Set mobjCmd = New ADODB.Command
        With mobjCmd
        
            .ActiveConnection = mobjConn
            .CommandText = "INSERT_PRODUCT"
            .CommandType = adCmdStoredProc
            .CommandTimeout = 0

            'append the cell of the second row, first column in the db in the Client_id column  in my SQL table
 
            .Parameters.Append .CreateParameter("@Client_id", adChar, adParamInput, 255, Worksheets("My_Sheet").Range("A1").Offset(nrow, 0).Value)

            .Execute

Wend

Basically I thought about something like:

If ... then GoTo NextIteration

End if

NextIteration: my_next_iteration

But what to set up i/o "..." so that if it see a client_id that it already sent it just goes to the next iteration.

Thanks in advance for your help !

Upvotes: 0

Views: 37

Answers (2)

Pierre_CM
Pierre_CM

Reputation: 55

THanks @Spencer Barnes for helping. Countif function is a good idea. Another way too is amending directly the stored procedure into SQL. YOu can even uses the UPDATE statement (if exists ... UPDATE) if your datas are clean

Upvotes: 1

Spencer Barnes
Spencer Barnes

Reputation: 2877

I've had this issue a couple of times now and tend to use Worksheetfunction.countif on each loop to check if any instances of the same figure occur above/below it.

In your case this would look something like;

If WorksheetFunction.Countif(Range(Cells(1, MyRange.Column), Cells(nrow, MyRange.Column)), MyRange.Offset(nrow).Value) > 0 Then GoTo NextIteration

Upvotes: 0

Related Questions