none
VBA excel Inventory report RRS feed

  • Question

  •  I am trying to build a program in VBA for excel to solve the following problem: I am given 10000 numbers from A1 to A10000. I need to write a macro that will count loop through them, and create a list that will tell me how many of each number is present in the list. (The 10000 have many repeating numbers). I'm having a lot of trouble with this...
    The solution must use at least one declared variable, at least one decision structure and one loop (you may actually need more than one of each).


    What code would be best for this??
    Monday, March 12, 2012 6:56 AM

Answers

  • This line is not working?

    If Worksheets("Sheet 2").Range("A1") <> " Then

    Replace it with a double quote for an empty string

    If Worksheets("Sheet 2").Range("A1") <> "" Then

    You might find it easier to write this if you add a variable to reference the worksheet, like this

    Dim wks As Worksheet
    Set wks = Worksheets("Sheet 2")
    If wks.Range("A1") <> "" Then
    Then you can replace Worksheets("Sheet 2") with wks in your code and intellisense will help you by showing the properties and methods of the worksheet object...

    Ed Ferrero
    www.edferrero.com

    Tuesday, March 13, 2012 7:11 AM

All replies

  • Hello Starda,

    This forum is for VB in VB Net.

    For VBA is the VBA forum

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


    Success
    Cor

    Monday, March 12, 2012 6:59 AM
  •  I am trying to build a program in VBA for excel to solve the following problem: I am given 10000 numbers from A1 to A10000. I need to write a macro that will count loop through them, and create a list that will tell me how many of each number is present in the list. (The 10000 have many repeating numbers). I'm having a lot of trouble with this...
    The solution must use at least one declared variable, at least one decision structure and one loop (you may actually need more than one of each).


    What code would be best for this??
    Monday, March 12, 2012 7:02 AM
  • Homework is best done with your own mind...

    Ed Ferrero
    www.edferrero.com

    Monday, March 12, 2012 9:26 AM
  • Look in the index of your textbook for: Loops, Variables and Decision Structures...

    Monday, March 12, 2012 3:24 PM
  • So I made the code but there is a segment that is not working

    Public Sub CreateInventoryReport()
    Dim bcode As Long
    Dim ubcode As Long

    'This is the part that isnt working

    If Worksheets("Sheet 2").Range("A1")<>" Then
        'First Value Is unique
     Worksheets("Sheet2").Range("D5") = Worksheets("Sheet2").Range("A1")
        Worksheets("Sheet2").Range("E5") = 1
    Else
        r = MsgBox("no data", , "no data")
    Exit Sub
    End If
    bcode = 2


    Do While Worksheets("Sheet 2").Cell(bcode, 1) <> ""
    ubcode = 5
    IsMatch = False
    Do While Worksheets("Sheet 2").Cell(ubcode, 4) <> ""
    If Worksheets("Sheet 2").Cell(bcode, 1) = Worksheets("Sheets2").Cell(ubcode, 4) Then
        Worksheets("Sheet 2").Cell(ubcode, 5) = Worksheets("Sheet2").Cell(ubcode, 5) + 1
        IsMatch = True
        Exit Do
        End If
        ubcode = ubcode + 1
        Loop
        If IsMatch = False Then
            Worksheets("Sheet2").Cell(ubcode, 4) = Worksheets("Sheet2").Cell(bcode, 1)
            Worksheets("Sheet 2").Cell(ubcode, 5) = 1
                    End If
            bcode = bcode + 1
    Loop
    End Sub

    Tuesday, March 13, 2012 6:52 AM
  • This line is not working?

    If Worksheets("Sheet 2").Range("A1") <> " Then

    Replace it with a double quote for an empty string

    If Worksheets("Sheet 2").Range("A1") <> "" Then

    You might find it easier to write this if you add a variable to reference the worksheet, like this

    Dim wks As Worksheet
    Set wks = Worksheets("Sheet 2")
    If wks.Range("A1") <> "" Then
    Then you can replace Worksheets("Sheet 2") with wks in your code and intellisense will help you by showing the properties and methods of the worksheet object...

    Ed Ferrero
    www.edferrero.com

    Tuesday, March 13, 2012 7:11 AM
  • That still didnt work. I keeps saying: Run time error '9'   Subscript out of range 
    Tuesday, March 13, 2012 7:31 PM
  • Nevermind I got it!
    Tuesday, March 13, 2012 8:04 PM
  • Hey Im doing this assignment now. Do you have the correct code? It still giving me the errors. Ive been playing with my mac all day!!! 
    Saturday, March 16, 2013 12:58 AM