Macro the uses data list to compute property costs

May 1, 2013 at 05:27:32
Specs: Windows 10
Macro help -

I want to create a macro to automate certain processes:

1. Customer chooses a property name form a drop down list and clicks 'run macro' button.
2. Macro takes property name, searches in 'Sheet 3' for the property's ID number (ID Number in Column A, Property Name in Column B).
3. ID number is copied and pasted into 'Sheet 2' Column A - (where a vlookup pulls all other relevant data)

Could somene provide the basic VBAs - or advice? I'm trying to learn VBA skills, would be good to get some pointers.

See More: Macro the uses data list to compute property costs

Report •

May 2, 2013 at 12:17:49
Here is some "basic VBA".

Some assumptions had to made since you didn't supply a lot of detail.


The Drop Down is in Sheet1!A1
The destination for the ID number pulled by the macro is Sheet2!A2

This code will monitor changes on Sheet1 and if a change is made to the Drop Down (A1) it will fire. (There's no need for a "run macro" button")

It will look in Sheet3!B:B for the value chosen in the drop down. When it finds the value, it will place the ID number from the corresponding row on Sheet3!A:A into Sheet2!A2.

Right-click the Sheet tab for Sheet1, chose View Code and paste the macro into the pane that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to drop down cell
 If Target.Address = "$A$1" Then
'Find drop down value in Sheet3!B:B
   With Sheets(3).Columns("B:B")
    Set p = .Find(Sheets(1).Range("A1"))
'Return corresponding value from Sheet3!A:A to Sheet2!A2
     Sheets(2).Range("A2") = Sheets(3).Range("A" & p.Row)
   End With
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question