PDA

View Full Version : VBA problem from complete amateur



bull699
10-30-2015, 07:04 PM
Completely new to excel VBA, sorry if this seems pretty standard, no idea what I'm talking about

Want to be able automate the look up of a piece of data in a spreadsheet, then subtract/add a value from the cell adjacent to it.

E.g. I want to change the price of an item in a stock list by $0.25

I enter the name of the item in Worksheet 1, A1 and the price change of $0.25 in A2

I then want to identify the item name in Worksheet 2, Column A, and subtract A2 from the adjacent cell in column B

Thanks

jolivanes
10-31-2015, 10:44 AM
Use negative values in Sheet1, Cell A2 for subtraction.



Sub Maybe()
With Sheets("Sheet2").Columns(1).Find(Range("A1").Value, , , 1).Offset(0, 1)
.Value = .Value + Sheets("Sheet1").Range("A2").Value
End With
End Sub

Note: going this route could be problematic with spelling mistakes.
You might want to have a drop down in Cell A1 so you have a choice and this will eliminate spelling differences.

SamT
11-01-2015, 09:48 AM
In VBA:

On the Menu Tools >> Options >>Editor Tab, check all the boxes in the "Code Settings" Pane.

Press Ctrl+R and F4 to insure that both the Project Explorer and the Properties Window are open.

In the Project Explorer Pane, Right Click on "ThisWorkbook" and insert a UserForm.

Click the ToolBox's ListBox control and drag it onto the UserForm. Press F1 while the ListBox Control is selected to see the Help for ListBoxes.

While the ListBox Control is selected, in the Properties Window Pane, Click on "Row Source" and press F1 to see the Help for that Property.

Label Controls let you place a Label with Caption next to your other Controls.

Insert a TextBox Control on the UserForm. Add a Label Control above it with the Caption "Enter Amount to Change Value of Selected Item"

Add a CommandButton Control.
Click the UserForm to select it. Right-Click the UserForm and Select "View Code." At the top of the VBA Editor Pane, in the Left Hand Dropdown, where it says "UserForm," select the CommandButton Name. In the Right-hand Dropdown, select "Click."

In the new CommandButton_Click Sub, write the Code


'This code is not complete and has errors. Use F1 a lot
Set Rng = Sheet2.Cells(ListBox.ListIndex, "B")
Rng = Rng + CLng(TextBox.Value
Set Rng = nothing
With ListBox
.Topindex = -1
.SetFocus
In the VBA Menu Debug >> "Compile VBAProject" often. If it Compiles, click the UserForm and press F5 to Run it.

You can place the cursor in any VBA or Excel Key Word in the VBA Editor and press F1 to get the Help on that Key word.

When adding a CommandButton to a sheet in Excel, I recommend always using the "Control ToolBox" Controls and not the "Form" Controls