CODEWITHSUNDEEP
vb.netvbaexcel
cluelessCoder
cluelessCoder

Reputation: 21

Code is unresponsive

Hi i'm a novice at vb (aka learned it today) and i wrote a code that's supposed to count how many identical serial numbers are in a list (listed in a column) and post each serial number with the number of times it's listed on another sheet. When i run my code as is, nothing happens. No errors, nothing new on the sheet. I limited my list to 1-10 in my current code because when it was 1-10000 excel crashed. Can anyone give me pointers on what's happening?? Thank you!

Public Sub count()

Dim count As Long
Dim i, j, a As Integer
Dim skuNames() As Double

a = 2
count = 0
ReDim skuNames(1)
skuNames(0) = Worksheets("RawBarcodeData").Cells(1, 1).Value

'this checks if an sku matches an existing sku in the array and adds if it does not'

For i = 1 To 10

For j = 0 To UBound(skuNames)
    If Worksheets("RawBarcodeData").Cells(i, 1).Value <> skuNames(j) And j <> UBound(skuNames) Then

    ElseIf Worksheets("RawBarcodeData").Cells(i, 1).Value <> skuNames(j) And j = UBound(skuNames) Then
        ReDim Preserve skuNames(0 To UBound(skuNames) + 1)
        skuNames(UBound(skuNames)) = Worksheets("RawBarcodeData").Cells(i, 1).Value

    Else
        End If
    Next j
Next i

'this will count how many of each element of the array is listed and post it'
For j = 0 To UBound(skuNames)
For i = 1 To 10
  If skuNames(j) = Worksheets("RawBarcodeData").Cells(i, 1).Value And i <> 10000 Then
    count = count + 1

  ElseIf skuNames(j) = Worksheets("RawBarcodeData").Cells(i, 1).Value And i = 10000 Then
    count = count + 1
    Worksheets("InventoryReport").Cells(a, 1).Value = skuNames(j)
    Worksheets("InventoryReport").Cells(a, 3).Value = count
    a = a + 1
    count = 0

  ElseIf skuNames(j) <> Worksheets("RawBarcodeData").Cells(i, 1).Value And i <> 10000 Then

  ElseIf skuNames(j) <> Worksheets("RawBarcodeData").Cells(i, 1).Value And i = 10000 Then
    Worksheets("InventoryReport").Cells(a, 1).Value = skuNames(j)
    Worksheets("InventoryReport").Cells(a, 3).Value = count
    a = a + 1
    count = 0

  End If

  Next i
Next j



End Sub

Upvotes: 2

Views: 100

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

maybe your code didn't crash but just entered in a long loop.

the reasons are because you didn't use 

 application.screenupdating= false
 application.enableevents=false
 application.calculation=xlManual

at the begin of the code, and then set it to true, and xlautomatic at the end.

An other reason is you ask your code too often to read again the same cell value (Worksheets("RawBarcodeData").Cells(i, 1).Value)

Why not tell your code to memorize it at the begin of loop ? 

CellI1= Worksheets("RawBarcodeData").Cells(i, 1).Value ' for example

Also when you use i and j as integer, it's slower then as Long :

 dim i as long, j as long.

An other thing , in your code i is a variant, not an integer as you might think

 dim i , j as integer

translates as :

 dim i  'vba by default sets it as variant
 dim j as integer

You can make the code simpler with asking less long "if" lines, but use more if's. use first the if wich will occur more often (if i<>1000)

so something like:

 if i<1000 then
      if CellI1=Skunames(j) then
           'somethinf
      else 'no need to ask if <> because it's already not =
           'something
      end if
 else 'here i=1000, no need to test if 
      if CellI1=Skunames(j) then
           'somethinf
      else 'no need to ask if <> because it's already not =
           'something
      end if
 end if

Other thing , use variables for worksheet:

 dim Sh as Worksheet
 set sh=Worksheets("RawBarcodeData")

and then sh.cells(i,1).value is as better way to write it.

Later on, you can even use the "With" statement.

with sh
     a= .cells(i,j).value
     if skullname(j) <> .cells(i,1).value

end with

this way excel doesn't need to recalculate/reread the sheet (or variable) at each pass.

And finally, an other way to find matches of same value in a range is to use the "match" function. (i don't recommend the "find" function wich is slower)

for very big ranges of data, you can use arrays, instead of looping through cells.values :

dim MyArray() as variant 'works only with variant
dim Max as Long
dim Sh as Worksheet
set Sh=thisworkbook.sheets("Test")
with sh
    max = .cells( .rows.count,1).end(xlup).row   'return the last row in first column
        MyArray = .range ( .cells(1,1) , .cells ( max,1) ).value 'fast way to memorize the whole range
        'can also be written  = . range ( "A1:A" & max).value   ' but is slower
end with

and later on, instead of working with cell(i,1).value , you have the same value in MyArray (i,1) 'and no ".value" at the end this time Don't forget to free some memory at the end with this code:

 erase MyArray
 set sh=nothing

have fun with VBA

Upvotes: 3

sagar
sagar

Reputation: 26

line of code which transfers data to other sheet ,i.e. below lines: 

    Worksheets("InventoryReport").Cells(a, 1).Value = skuNames(j)
    Worksheets("InventoryReport").Cells(a, 3).Value = count

will only execute when "i=10000", this criteria is not met when you run code, to debug how ur program is doing, run it in debug mode, step by step (press "F8" key to execute one line at a time)

Upvotes: 0

Related Questions