問題描述
使用 VBA 根據 B 列的值重置 A 列中的值 (Reset values in column A based on the value of column B using VBA)
I have a sheet that looks like this, but way more complex:
Product count real count
Rake Complete -1 n/a
Rake Head 2 =B3+B2
Rake Handle 2 =B4+B2
Bike Complete -5 n/a
Bike Handle 10 =B6+B5
Bike Wheel 25 =B7+B5+B5
Bike Frame 90 =B8+B5+B6
With the formulas working, it looks like this:
Product count real count
Rake Complete -1 n/a
Rake Head 2 1
Rake Handle 2 1
Bike Complete -5 n/a
Bike Handle 10 5
Bike Wheel 25 15
Bike Frame 90 85
I would appreciate help with VBA code that would reset the column B's value to the to be Column C's value like this:
Product count real count
Rake Complete 0 n/a
Rake Head 1 1
Rake Handle 1 1
Bike Complete 0 n/a
Bike Handle 5 5
Bike Wheel 15 15
Bike Frame 85 85
I have other VBA code that I put together on other sheets that add and subtract from the values in the count column. To keep this working inventory as accurate as I can and not based on a variable, I would like to reset column B everyday. That way, if a count is off slightly, I don't have to reset everything.
I have been working on this for awhile and I know there is a better way to do this than the five I have tried and failed at. It needs to be column based though, my sheet has 465 rows that this needs to apply to and as businesses grow, so will the rows on this sheet.
The code that got me the closest is this:
Sub CommandButton1_Click()
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim r As Long
For r = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
If Cells(r, 2) = "n/a" Then Cells(r, 2).value = 0
Next r
End Sub
The major problem I have is every time I run the code it ups the count on some of my other products. If you look at the bike frame, you can sell it separately but you also get a bike handle when you do. When this code resets it always adds the bike handles. If I run it 10 times without changes my inventory grows higher then excel is willing to show. Any idea how I can fix this or any ideas of a different way all together?
參考解法
方法 1:
Copy and paste values - with a paste special - from column C to column B.
But then column C's values will obviously change - because Eistein and cause and effect or something like that.
方法 2:
It looks like you should use column D and call it Display Count, or something like that. Use this formula in column D:
=IF(C18="n/a", 0, C18)
You can then hide columns B and C if they confuse your boss.
方法 3:
Try this macro in the workbook open event. I hope it may solve your problem:
Dim realcount() As Variant
Dim x As Single
Private Sub Workbook_Open()
On Error Resume Next:
x = Sheet1.Cells(2, 3).End(xlDown).Rows
ReDim realcount(x)
For i = 2 To x
realcount(i) = Sheet1.Cells(i, 3).Value
Sheet1.Cells(i, 2).Value = realcount(i)
If Sheet1.Cells(i, 3).Value = "n/a" Then
Sheet1.Cells(i, 2).Value = 0
End If
Next
End Sub
(by Stephan Daudt、Stepan1010、Jon Crowell、ranganatha channarayapatna)