• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Problem with VBA Script

Hi Experts

I am having such a fit trying to figure out why this VBA script isn't working.  I've used this scripts every year for the last 4 years without any issue.  I only use it 1 time per year so I have to reacquaint myself with it every time.  

I've attached the test workbook with a before tab and after tab.  I am really scratching my head on this one.  

Here is the code (which was originally written by an EE Expert)

Sub x()
 
 
 
 
 
End Sub
Sub insert()
Dim rng As Range, i As Long, rng1 As Range
i = 9
Do Until i > Cells(65536, "ab").End(xlUp).Row
If (Cells(i, 2).Value <> Cells(i, 28).Value) Then
Set rng = Range("AB9:AB" & Cells(65536, "A").End(xlUp).Row)
If (Application.WorksheetFunction.CountIf(rng, Cells(i, 2).Value) >= 1) Then
Do Until Cells(i, 2).Value = Cells(i, 28).Value
Range("b" & i & ":x" & i).insert shift:=xlDown
Rows(i).Interior.ColorIndex = 16
i = i + 1
Loop
Else
Set rng1 = Range("b9:b" & Cells(65536, "A").End(xlUp).Row)
If (Application.WorksheetFunction.CountIf(rng1, Cells(i, 28).Value) > 0) Then
Range("ab" & i & ":ay" & i).insert shift:=xlDown
Rows(i).Interior.ColorIndex = 36
i = i + 1
Else
If (Cells(i, 2).Value > Cells(i, 28).Value) Then
Range("b" & i & ":x" & i).insert shift:=xlDown
Rows(i).Interior.ColorIndex = 16
i = i + 1
Else
Range("ab" & i & ":ay" & i).insert shift:=xlDown
Rows(i).Interior.ColorIndex = 36
i = i + 1
 
End If
End If
End If
Else
i = i + 1
End If
If (i > Cells(65536, "b").End(xlUp).Row) Then Exit Sub
Loop
End Sub
example2.xlsx
0
spudmcc
Asked:
spudmcc
  • 2
1 Solution
 
NorieCommented:
What, in words, is the code supposed to do?

Also, how does it not work?

Are you getting errors or does it seem to take a long time to run?
0
 
spudmccAuthor Commented:
1--If the zip code is in the 2012 data (AB) then add a line in the 2011(B) data area only and color entire row in yellow.  

2--If the zip code in 2011(B) but not 2012(AB) then add a line to the 2012(AB) data area only and color the entire row gray.  

It gets down to row 1600 and just kept going with the gray color.  I just can't see what the issue is with the script or the data.
0
 
Ken ButtersCommented:
The macro code assumes that your zip codes are sorted in ascending order.

The first zip code that causes a problem is 13224.  It is out of order in column B.

If you sort them.... it should work ok.
0
 
spudmccAuthor Commented:
Thank you so much!  That was the issue.  I was looking at it so hard that I was looking right past the obvious.  

spudmcc (Andy)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now