-
Feb 18th, 2015, 12:51 PM
#1
Thread Starter
New Member
[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!
-
Feb 18th, 2015, 03:23 PM
#2
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
-
Feb 18th, 2015, 05:21 PM
#3
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|