473,326 Members | 2,255 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to fix excel script that freezes in lloop?

Writing this code to help analyze and re-organize our e-mail promotions and every time I try to run the script, it just freezes up Excel (infinite loop somewhere?). It's pulling data from another sheet ("Promos") in the same workbook.

Expand|Select|Wrap|Line Numbers
  1. Sub Update_Campaign_Breakdown()
  2. Dim rownum As Integer
  3. Dim campaign As String
  4. Dim newrows As Integer
  5. Dim i As Integer
  6. rownum = 2
  7. Do Until IsEmpty(Sheets("Promos").Cells(rownum, 1))
  8.     campaign = Sheets("Promos").Cells(rownum, 6).Value
  9.     newrows = Application.CountA(Range("A:A")) + Application.CountA(Range("B:B"))
  10.     For i = 1 To newrows
  11.         If Cells(i, 1).Value = campaign Then
  12.         Cells(i).Offset(1).EntireRow.Insert
  13.         Cells(i + 1, 2).Value = Sheets("Promos").Cells(rownum, 1)
  14.         Cells(i + 1, 3).Value = Sheets("Promos").Cells(rownum, 7)
  15.         Cells(i + 1, 4).Value = Sheets("Promos").Cells(rownum, 8)
  16.         Cells(i + 1, 5).Value = Sheets("Promos").Cells(rownum, 9)
  17.         Cells(i + 1, 6).Value = Sheets("Promos").Cells(rownum, 10)
  18.         Cells(i + 1, 7).Value = Sheets("Promos").Cells(rownum, 11)
  19.         rownum = rownum + 1
  20.         Exit For
  21.         Else
  22.         If i = newrows Then
  23.         Cells(i).Offset(1).EntireRow.Insert
  24.         Cells(i).Offset(1).EntireRow.Insert
  25.         Cells(i + 1, 1).Value = campaign
  26.         Cells(i + 2, 2).Value = Sheets("Promos").Cells(rownum, 1)
  27.         Cells(i + 2, 3).Value = Sheets("Promos").Cells(rownum, 7)
  28.         Cells(i + 2, 4).Value = Sheets("Promos").Cells(rownum, 8)
  29.         Cells(i + 2, 5).Value = Sheets("Promos").Cells(rownum, 9)
  30.         Cells(i + 2, 6).Value = Sheets("Promos").Cells(rownum, 10)
  31.         Cells(i + 2, 7).Value = Sheets("Promos").Cells(rownum, 11)
  32.         rownum = rownum + 1
  33.         Exit For
  34.         End If
  35.         End If
  36.         Next i
  37. Loop
  38. End Sub
I'll try to summarize how it's SUPPOSED to work.

Basically it's going down the line and looking at each entry in the Promo's table. Then, it's looking at one of the columns (Campaign) and seeing if that Campaign already exists inside of the new sheet (I'm executing the script from a button on this sheet).

I want to have it organize the campaigns like so:

Campaign 1
> Email 1
> Email 2
Campaign 2
> Email 1
> Email 2

....and so on. So, the script is trying to see if there's already a slot where that campaign exists. If it finds the campaign, it should insert a row after the campaign, and then copy the data from the Promos sheet into the new sheet.

If it DOESN'T find that campaign in the break-down sheet, then it should add two rows: One for the new campaign label and one for the e-mail that falls under that Campaign.

Lastly, it should go back to the Promos table and move onto the next entry and repeat.

Any insight into why this just locks up Excel?
Feb 9 '11 #1
2 2283
ADezii
8,834 Expert 8TB
Without looking any further than Line #7, it appears that you may be using the wrong Function (IsEmpty()) at the start of your Do Until...Loop. The IsEmpty() Function is used to determine if individual Variables are Initialized, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim MyVar, MyCheck
  2. MyCheck = IsEmpty(MyVar)    ' Returns True, MyVar NOT Initialized.
  3.  
  4. MyVar = Null    ' Assign Null.
  5. MyCheck = IsEmpty(MyVar)    ' Returns False, is Initialized.
Feb 10 '11 #2
beacon
579 512MB
I think ADezii is right. You should probably be using IsNull if you want to check for a value.

If ADezii's suggestion doesn't work out, can you post your spreadsheet?
Feb 10 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: akunamatata | last post by:
Hello everyone, I contact this discussiongroup because I encountered a little problem with XSL. Let me explain it: I have following file "position.xml": <?xml version="1.0"?>...
0
by: /2ez|n | last post by:
I found the answer, but thanks for all the help! For my c++ class I have to make a small win32 console program that calculates body mass index. The user will enter in a their first and...
10
by: Sigmathaar | last post by:
Hi, I'm having some problems when using double linked lists, specially when it's up to the Blink an Flink pointers. Does anybody knows a good site or a book where they explain how to use them? ...
3
by: Shippy | last post by:
Please help, this is doing my head in!!!! I am sure it is something really simple and ovbious that I am missing but for the life of me I cant find where!!! I have this function... <script>...
2
by: cool17 | last post by:
Hi guys can help me to correct my mistake? For my program, i asked the user for the number of terms they want to enter, it will then loop to the required times for(j=0;j<10;j++) { ...
1
by: imranay | last post by:
hey is there some body to tell me what will be the next step after imorting website. i have importeb my website named cruel_digger to yahworld.com but i dont know the nect step please help this dull...
3
by: stressedstudent | last post by:
I dont know where I am going wrong so I dont know which part to post, this is what I have, can anyone help me figure out where I am going wrong? THanks for any and all help. // into to c++ //...
12
by: zalery | last post by:
so i'm trying to set up this exponents loop, keep in mind this is my first year in computer science so my knowledge of script is somewhat minimal. basically this assignment (or at least part of it)...
3
by: Zenoba | last post by:
Can someone tell me what does this FOR loop means in C programming? for (;a!=0;a=a>>1) what this for loop does?
2
by: Aung sang | last post by:
what this for loop means in java for(double a:accounts) where this bank program in which transactions are done in random between the accounts.give the syntax for this statement if anyone knows
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.