Macro fill cell based on value from another sheet - columns change

[tcy0330]

Hi all,

I could use a bit of help here.

I have a file with a master list of data, titled "Values." In this sheet, I have specific identifying columns - Building #, Floor # and Unit. (Columns B, C and D, respectively). This sheet contains information for all units on all floors in 5 different buildings.

I then have separate sheets for each separate building, with all the same column headings.

What I want to do is have the column for "sale price" sheets for the individual building autofill when a price is entered in to the "Values" sheet [column F is sale price].

I know I could use a VLOOKUP, but I would like a macro for this, unless someone can give me a good solution. My problem is that I (and others) will be sorting the data in the sheets based on multiple factors. For example, I may want to sort by the units in a building (i.e. price for each "A" unit) or by floors (all units on 5th floor) or, on the master sheet, an advance search by first building, then floor, etc... I'm also concerned about someone using the sheet and deleting the vlookup formula in the columns!

Is there a way to do this? Does my description make sense?

Thanks for all the help!

 

[Legacy 221039]

Hi

You might want to adapt this code to your needs:

<code>

Sub MyVlookup()


Dim MyRange As Range
Set MyRange = Range("E2:E4")


For Each cell In MyRange


y = cell.Value


x = Application.WorksheetFunction.vlookup(y, Range("H2:i4"), 2, False)


cell.Offset(0, 1) = x


Next cell


End Sub



</code>

To see this macro running, enter the data below and then run macro as normal:

Values[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]PRICE[/TD]
[TD]Sale Price[/TD]
[TD][/TD]
[TD]Table for vlookup[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010




Also, on the topic of someone deleting the formula, have you considered protecting selected cells?

M

 

[tcy0330]

Hey Mark,

Thanks for the help. First, I had not considered protecting any cells, but am not sure if that will work for me - I want my boss to be able to go into the sheet and enter anything he sees fit!

Second, I am not sure if I was clear in my question, but maybe I am missing something with your macro.

What I am looking for is to be able to enter a value for sales price (column F) in sheet 1 "Values" and have that automatically fill in the sale price in a separate sheet (which corresponds to the building I am working with). For example, F7 is building 400, Floor 1, Unit C. If someone enters a value in F7, can I get that to automatically fill in on the sheet titled "400" in the row with floor 1, unit 7? I'm imagining the macro has to identify 3 values (building, floor, unit) and then find the book and row. Is this possible?

 

[Legacy 221039]

Hi

My example below shows an example using Building 400, Floor 1, Unit C. The user has entered a sales price of 1 on the Values worksheet, and the formula on the 400 worksheet (for Building 400) is returning the result of 1. Have a look at this and see what you think - if this is along the right lines then I'll have a go at writing a macro.
M PS Worksheet name given below the relevant cells.

Excel 2007

	A	B	C	D	E	F
Building	Floor	Unit	Concatenation	Enter value for sales price
C	Building400Floor1UnitC

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

</tbody>

Values​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]E7[/TH]
[TD="align: left"]="Building"&B7&"Floor"&C7&"Unit"&D7[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2007

	B	C	D	E	F
Building	Floor	Unit	Concatenate	Sales Price
C	Building400Floor1UnitC

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

</tbody>

400​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]E2[/TH]
[TD="align: left"]="Building"&B2&"Floor"&C2&"Unit"&D2[/TD]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]F2[/TH]
[TD="align: left"]=VLOOKUP(E2,Values!E:F,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[tcy0330]

hey Mark,

Thanks again. That is definitely along the lines of what I'm looking for. Any help with a macro would be great. One question: is there a way to create a macro for this that will run automatically anytime a value is entered in the main sheet? (as opposed to having to run it every time?)

I can't thank you enough.

Tom

 

[tcy0330]

Hey Mark:

I have the following Macro:
Sub updatesheets()
Dim i As Long, ii As Long, j As Long, jj As Long, findkey As String
Dim lastrow As Long, lastrowi As Long
Application.ScreenUpdating = False
Set shvalues = Sheets("Values")
lastrow = shvalues.Cells(shvalues.Rows.Count, "B").End(xlUp).Row
For j = 4 To lastrow
If shvalues.Cells(j, 6) <> "" Then
findkey = shvalues.Cells(j, 2).Text & shvalues.Cells(j, 3).Text & shvalues.Cells(j, 4).Text
For i = 2 To Worksheets.Count
With Worksheets(i)
lastrowi = .Cells(.Rows.Count, "B").End(xlUp).Row
For ii = 1 To lastrowi
keyii = .Cells(ii, 2).Text & .Cells(ii, 3).Text & .Cells(ii, 4).Text
If keyii = findkey Then
.Cells(ii, 6) = shvalues.Cells(j, 6)
End If
Next ii
End With
Next i
End If
Next j
Application.ScreenUpdating = True

End Sub


This works well when I run it, but was wondering if there is any way that it will run automatically when any vlaue is entered into sales price?

thanks

tom

 

[Legacy 221039]

Hi Tom

This should be possible - I'll have a think about this and try to post something this weekend. Meanwhile, you might want to search using the term "event driven macro". I understand that this link is good:

http://www.cpearson.com/excel/events.htm

Regards M

 

[Legacy 221039]

Hi Tom

The name for a macro which runs when a user makes a change is called an event driven macro (as far as I know!). Try the following example out and you'll see what I mean:
1. Open a blank workbook
2. Right click on the sheet 1 tab at the bottom of the screen and select "View Code"
3. Paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.Font.Color = RGB(0, 255, 0)
End Sub

Now go back to Excel and try entering "1" in cells A1 to A10. You should find that from cells A2 onwards the font appears as green. So, in other words the macro is running each time the user makes a change. (By the way, cell A1 remain in black for some reason - I'm not quite sure why this happens).
Try experimenting with this by substituting the second line of code with some other code. I hope this helps in some way
Regards
M

 

[Legacy 221039]

Hi Tom

how about the following. This example is very like my example above, except that I've written a macro this time. In this example, the user has entered two sales prices on the Values worksheet, sheet 400 has been selected, and then the macro has been run (so that column F has been completed). The code is given below, followed by what the worksheets look like. I've assumed in this example that the spreadsheet designer has added the "concatenation" column on the Values worksheet, and on the 400 worksheet has added the concatenation column and the building column. Regards M

Sub MyVlookup2()
Dim MyRange As Range
Set MyRange = Range("e2:e3")
Dim MyRange2 As Range
Set MyRange2 = Application.Worksheets(1).Range("E2:F3")




For Each cell In MyRange


y = cell.Value
x = Application.WorksheetFunction.VLookup(y, MyRange2, 2, False)
cell.Offset(0, 1) = x


Next cell
End Sub


Values[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Building [/TD]
[TD]Floor[/TD]
[TD]Unit [/TD]
[TD]Concatenation[/TD]
[TD]Sales Price[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD]C[/TD]
[TD]Building400Floor1UnitC[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]400[/TD]
[TD="align: right"]2[/TD]
[TD]D[/TD]
[TD]Building400Floor2UnitD[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
E2	="Building"&B2&"Floor"&C2&"Unit"&D2
E3	="Building"&B3&"Floor"&C3&"Unit"&D3

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]




400[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Building [/TD]
[TD]Floor[/TD]
[TD]Unit [/TD]
[TD]Concatenation[/TD]
[TD]Sales Price macro runs here[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD]C[/TD]
[TD]Building400Floor1UnitC[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]400[/TD]
[TD="align: right"]2[/TD]
[TD]D[/TD]
[TD]Building400Floor2UnitD[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Cell	Formula
E2	="Building"&B2&"Floor"&C2&"Unit"&D2
E3	="Building"&B3&"Floor"&C3&"Unit"&D3

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<thead>[TR]

</thead><tbody>
</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[tcy0330]

Thanks for everything Mark. I got the following, but do have one question:

Dim lastrow As Long, lastrowi As Long
Application.ScreenUpdating = False
Set shvalues = Sheets("Values")
lastrow = shvalues.Cells(shvalues.Rows.Count, "B").End(xlUp).Row
For j = 4 To lastrow
If shvalues.Cells(j, 6) <> "" Then
findkey = shvalues.Cells(j, 2).Text & shvalues.Cells(j, 3).Text & shvalues.Cells(j, 4).Text
For i = 2 To Worksheets.Count
With Worksheets(i)
lastrowi = .Cells(.Rows.Count, "B").End(xlUp).Row
For ii = 1 To lastrowi
keyii = .Cells(ii, 2).Text & .Cells(ii, 3).Text & .Cells(ii, 4).Text
If keyii = findkey Then
.Cells(ii, 6) = shvalues.Cells(j, 6)
End If
Next ii
End With
Next i
End If
Next j
Application.ScreenUpdating = True


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F5:F500")) Is Nothing Then UpdatePrice
End Sub


My question is if I want to expand the number of columns that will update, how do I do that. For example, if I want the macro to update based on changes to column s F G H, what changes do I make in the code?