Formula or Function Needed
- Thread starter markhuges
- Start date
Mark
Firstly, Welcome to the Chandoo.org Forums
I will assume two things, 1 is that you know a little VBA and secondly that you want to reduce the Stock Qty on the Stock Sheet by the selected Items Qty?
Changing the CommandButton1_Click event should do what your after:
[pre]
[/pre]
Firstly, Welcome to the Chandoo.org Forums
I will assume two things, 1 is that you know a little VBA and secondly that you want to reduce the Stock Qty on the Stock Sheet by the selected Items Qty?
Changing the CommandButton1_Click event should do what your after:
[pre]
Code:
Private Sub CommandButton1_Click()
Sheets("Receipt").Select
Selection.Hide
' This is all new
Dim c As Range
For Each c In Worksheets("Stock").Range("B5:B100")
If c.Text = Worksheets("Selection").Range("B6").Text Then _
c.Offset(0, 2).Value = c.Offset(0, 2).Value - Worksheets("Selection").Range("C6").Value
Next
End SubHello Thank You for your replay, I am a begginer in VBA sorry,
Yes i would like : ou want to reduce the Stock Qty on the Stock Sheet by the selected Items Qty?
Do i create a new Button and paste in the code?
What do you mean by : Changing the CommandButton1_Click event should do what your after:
Thank You
Yes i would like : ou want to reduce the Stock Qty on the Stock Sheet by the selected Items Qty?
Do i create a new Button and paste in the code?
What do you mean by : Changing the CommandButton1_Click event should do what your after:
Thank You
Mark
Open the file and close the pop up menu
Alt F11
In the left hand tree view window find your project and select the Forms and then the Selection window
Double click on the Selection icon
Then double click on the Finish Button in the Right hand pane
Replace the code below the Private Sub CommandButton1_Click()
with the code above
Alt F11 to go back to Excel and save your file
Open the file and close the pop up menu
Alt F11
In the left hand tree view window find your project and select the Forms and then the Selection window
Double click on the Selection icon
Then double click on the Finish Button in the Right hand pane
Replace the code below the Private Sub CommandButton1_Click()
with the code above
Alt F11 to go back to Excel and save your file
@Hui - Did you check your email
Thank You for that it has worked. Thank You very very much!
Can you explain the code for me please.
' This is all new
Dim c As Range - What does the C mean ?
For Each c In Worksheets("Stock").Range("B5:B100") - ( the item names)
If c.Text = Worksheets("Selection").Range("B6").Text Then _
(if the selection is in range B6 then )
c.Offset(0, 2).Value = c.Offset(0, 2).Value - (not sure here) Worksheets("Selection").Range("C6").Value ( then the selection worksheet range c6 -quantity)
Next
End Sub
I dont understand the code, if possible can you break it down and explain it please.
Thank You very much. I greatly appreciate your help. Thank You once again
Thank You for that it has worked. Thank You very very much!
Can you explain the code for me please.
' This is all new
Dim c As Range - What does the C mean ?
For Each c In Worksheets("Stock").Range("B5:B100") - ( the item names)
If c.Text = Worksheets("Selection").Range("B6").Text Then _
(if the selection is in range B6 then )
c.Offset(0, 2).Value = c.Offset(0, 2).Value - (not sure here) Worksheets("Selection").Range("C6").Value ( then the selection worksheet range c6 -quantity)
Next
End Sub
I dont understand the code, if possible can you break it down and explain it please.
Thank You very much. I greatly appreciate your help. Thank You once again
Thank You very much for that!! Thank You
There is simply one last part left. I will attempt to create this my self but as the attached sheet was a sample i will test it with 2 or 3 other selection items
Here is the Link
http://www.mediafire.com/?hn8tcfaoc2hbqw5
Dim c As Range
For Each c In Worksheets("Stock").Range("B5:B100,G5:G100")
If c.Text = Worksheets("Selection").Range("B6,F6").Text Then _
c.Offset(0, 2).Value = c.Offset(0, 2).Value - Worksheets("Selection").Range("C6,G7").Value
Next
Thank You
End Sub
There is simply one last part left. I will attempt to create this my self but as the attached sheet was a sample i will test it with 2 or 3 other selection items
Here is the Link
http://www.mediafire.com/?hn8tcfaoc2hbqw5
Dim c As Range
For Each c In Worksheets("Stock").Range("B5:B100,G5:G100")
If c.Text = Worksheets("Selection").Range("B6,F6").Text Then _
c.Offset(0, 2).Value = c.Offset(0, 2).Value - Worksheets("Selection").Range("C6,G7").Value
Next
Thank You
End Sub
This code however does not work
Dim c As Range
For Each c In Worksheets("Stock").Range("B5:B100,G5:G100")
If c.Text = Worksheets("Selection").Range("B6,F6").Text Then _
c.Offset(0, 2).Value = c.Offset(0, 2).Value - Worksheets("Selection").Range("C6,G7").Value
Next
End Sub
Thank You
Dim c As Range
For Each c In Worksheets("Stock").Range("B5:B100,G5:G100")
If c.Text = Worksheets("Selection").Range("B6,F6").Text Then _
c.Offset(0, 2).Value = c.Offset(0, 2).Value - Worksheets("Selection").Range("C6,G7").Value
Next
End Sub
Thank You
SirJB7
Excel Rōnin
Hi, markhughes!
I downloaded your file and I notice this:
a) In userform Selection, CommandButton1 click event (the above code), "c" is a one-cell range that varies from B5 thru B100 and G5 thru G100, that's to say the loop is performed 192 times, but:
- you're checking its value against a two-cell range value (B6,F6), for which -even if they have individual values in B6 and F6 and Excel doesn't raise an error- the text property is null: just debug it and check for the value
- despite of that, the value to be subtracted when the condition is true (what it won't happen accordingly to previous paragraph) is related to a two-cell range (C6,G7) in my humble opinion should be (C6,G6)
b) To reduce search loops thru empty cells I would change the loop structure to something like this, if there are unique entries in range 5:100:
-----
[pre]
[/pre]
-----
c) If what you intended to do is checking "c" value against B column and then after G column, you should use two nested If structures, as a two-cells range text property returns null, even both individual cells text property are not empty
d) I would add an empty column before E column in sheet Selection, so as to maintain the same structure and offsets that in sheet Stock: in this way, you would compare/use columns B against columns B and columns G against columns G, which I think is clearer than B vs. B and F vs. G.
Summary: you're right, the above code doesn't work, let us know what was the intended action and we'll try to help you fixing it.
Regards!
I downloaded your file and I notice this:
a) In userform Selection, CommandButton1 click event (the above code), "c" is a one-cell range that varies from B5 thru B100 and G5 thru G100, that's to say the loop is performed 192 times, but:
- you're checking its value against a two-cell range value (B6,F6), for which -even if they have individual values in B6 and F6 and Excel doesn't raise an error- the text property is null: just debug it and check for the value
- despite of that, the value to be subtracted when the condition is true (what it won't happen accordingly to previous paragraph) is related to a two-cell range (C6,G7) in my humble opinion should be (C6,G6)
b) To reduce search loops thru empty cells I would change the loop structure to something like this, if there are unique entries in range 5:100:
-----
[pre]
Code:
If Len(c.Text) = 0 Then Exit For
If Condition Then
Action
Exit For
End If
-----
or, if there are duplicate entries in range 5:100:
-----
If Len(c.Text) = 0 Then Exit For
If Condition Then
Action
End If-----
c) If what you intended to do is checking "c" value against B column and then after G column, you should use two nested If structures, as a two-cells range text property returns null, even both individual cells text property are not empty
d) I would add an empty column before E column in sheet Selection, so as to maintain the same structure and offsets that in sheet Stock: in this way, you would compare/use columns B against columns B and columns G against columns G, which I think is clearer than B vs. B and F vs. G.
Summary: you're right, the above code doesn't work, let us know what was the intended action and we'll try to help you fixing it.
Regards!