Results 1 to 3 of 3

Thread: [Excel] Attempting to automate the keeping of stock and ordering, run-time error 1004

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    1

    [Excel] Attempting to automate the keeping of stock and ordering, run-time error 1004

    Hi, so I'm very new to coding in VB, and I'm sure there's an easy fix, but I just don't know enough yet. Basically, what I'm trying to get set up is a system of worksheets, each with an identical table, with one being blank to put in current stock, then one with the established minimum of each item that there can be in each supply room, an established maximum, and then another blank table that will show the number of each item that is needed to reach the maximum amount.

    I know I could code it as one long, repetitive block of text wherein I just go through and compare each cell individually, but I'd prefer to do by way of nested loops, if possible, so that it compares a particular cell in the selected range for each worksheet, then if current stock is at or less than the minimum, it spits the difference needed into the correct cell of the order worksheet.

    This is the code I've cobbled together so far, from the tutorials and help forums I've read on similar situations:

    Code:
    Public Sub Compare()
    Sheets("Current Stock").Range("C3:J35").Select
    
    Dim CompareRange1 As Variant, CompareRange2 As Variant, x As Variant, y As Variant, z As Variant
    Set CompareRange1 = Worksheets("Set Min").Range("C3:J35")
    Set CompareRange2 = Worksheets("Set Max").Range("C3:J35")
    
    For Each x In Selection
        For Each y In CompareRange1
            For Each z In CompareRange2
                If x <= y Then
                    Dim currentadd As Variant
                    currentadd = ActiveCell.Address
                    Worksheets("Order").Range("currentadd").Value = z.Value - x.Value
                End If
             Next z
        Next y
    Next x
    
    End Sub
    Do I need to add offsets for the loop variables or will it move along the range automatically? Did I correctly reference the cell address for whichever cell it's on in the loop so I can alter the value of the same in "Order"? Or should I just nest another loop for that sheet as well to keep it in sync with the others?

    Thanks for any help you guys can give me!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [Excel] Attempting to automate the keeping of stock and ordering, run-time error

    currentadd = ActiveCell.Address
    this is probably incorrect, but i have not figured which cell you are trying to get the address of, the code does not show what you are trying to achieve, but as stock items have a code you should be able to use lookups rather than so many loops, if possible avoid the use of selection, or selecting ranges

    which line gives the error?

    i am not sure why you have max and min worksheets, when you could just have a column for each and stock on hand, in a master stocklist sheet
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Excel] Attempting to automate the keeping of stock and ordering, run-time error

    You set the variable currentadd to the ActiveCell Address (a string), but when you attempt to use the string variable in the Range reference, you have enclosed currentadd in quotes. Remove the quotes.
    Code:
    currentadd = ActiveCell.Address
    Worksheets("Order").Range("currentadd").Value = z.Value - x.Value

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width