I am seeking help designing function or macros that will perform the following task:

Allow a user to select a specific base model non living quarter trailer and have a function input the price in the blank cell under list price. I would like this to be as simple for the user as possible. I would like the user to click on the corresponding cell to whatever trailer that they desire, and have the function or macro input directly into the blue cells near the bottom of the page. If anybody that can come up with something that would work to complete this task, that would be great. If you need more information, just let me know.

Thanks

On Wed, 31 Jul 2013 14:34:39 +0100, smoborny wrote:


I am seeking help designing function or macros that will perform the
following task:

Allow a user to select a specific base model non living quarter trailer
and have a function input the price in the blank cell under list price.
I would like this to be as simple for the user as possible. I would like
the user to click on the corresponding cell to whatever trailer that
they desire, and have the function or macro input directly into the blue
cells near the bottom of the page. If anybody that can come up with
something that would work to complete this task, that would be great. If
you need more information, just let me know.

Thanks


+-------------------------------------------------------------------+
|Filename: excelbanter.jpg |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=893|
+-------------------------------------------------------------------+

Suggest you post a workbook, with the data entered, rather than a picture. For those of us who want to respond, it would sure save us some time as we would not have to generate the workbook.

Here you go, thanks for your suggestion

On Wed, 31 Jul 2013 19:25:19 +0100, smoborny wrote:


Here you go, thanks for your suggestion


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=895|
+-------------------------------------------------------------------+

If your real data layout is pretty close to what you've posted, the following Event Macro should do what you want.

To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-click drop-down menu.
Then paste the code below into the window that opens.


=====================================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim rTS As Range
Dim rPrice As Range
Dim s As String, cost As Double

Set r = Range("a1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=10)
If Not Intersect(Target, r) Is Nothing Then
Set rTS = Cells.Find(what:="TRAILER SHELL (NO LQ)", after:=[a1], _
LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext, _
MatchCase:=True)
Set rPrice = Cells.Find(what:="List", after:=rTS, searchorder:=xlByRows)
If rTS Is Nothing Or rPrice Is Nothing Then
MsgBox ("No place for results")
Exit Sub
End If
Set rTS = rTS.Offset(rowoffset:=1)
Set rPrice = rPrice.Offset(rowoffset:=1)
If IsNumeric(Target) And Len(Target) 0 Then
cost = Target.Value
s = Cells(Target.Row, "A").Text & ", " & _
Target.End(xlUp).Text & ", " & _
Cells(Target.End(xlUp).Row - 1, "A").Text
End If
rTS = s
rPrice = cost
rPrice.NumberFormat = "$#,##0"
End If
End Sub
===========================================

On Wed, 31 Jul 2013 19:25:19 +0100, smoborny wrote:


Here you go, thanks for your suggestion


+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=895|
+-------------------------------------------------------------------+

Looking also at your "picture" where you indicate more clearly how you want the Trailer Shell data entered, I made some changes to the VBA Code in my previous post:

==================================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim rTS As Range
Dim rPrice As Range
Dim s As String, cost As Double

Application.EnableEvents = False
Set r = Range("a1", Cells(Rows.Count, "A").End(xlUp)).Resize(columnsize:=10)
Set r = r.SpecialCells(xlCellTypeConstants, xlNumbers)

If Not Intersect(Target, r) Is Nothing Then
Set rTS = Cells.Find(what:="TRAILER SHELL (NO LQ)", after:=[a1], _
LookIn:=xlValues, lookat:=xlPart, searchdirection:=xlNext, _
MatchCase:=True)
Set rPrice = Cells.Find(what:="List", after:=rTS, searchorder:=xlByRows)
If rTS Is Nothing Or rPrice Is Nothing Then
MsgBox ("No place for results")
Exit Sub
End If
Set rTS = rTS.Offset(rowoffset:=1)
Set rPrice = rPrice.Offset(rowoffset:=1)
cost = Target.Value
s = Replace(Cells(Target.End(xlUp).Row - 1, "A").Text, "/", " x ") & _
" / " & Cells(Target.Row, "A").Text & " / " & _
Target.End(xlUp).Text & " Short Wall"
rTS = s
rPrice = cost
rPrice.NumberFormat = "$#,##0"
End If
Application.EnableEvents = True
End Sub
===========================================

I am impressed, thanks for such a quick response! Now I am in the process of allowing the code to work in my complete project. I have been attempting this for a couple hours and haven't got anywhere. If the prices you seen on the zip file that I uploaded were referenced back to a different workbook would I have to change the code?

Just curious if you could post a description of what is being accomplished next to each line of code, so I can understand it better. I am going to have to use this code in other projects and I would like to be able to do it myself. Thanks in advance!

On Thu, 1 Aug 2013 16:30:06 +0100, smoborny wrote:


I am impressed, thanks for such a quick response! Now I am in the
process of allowing the code to work in my complete project. I have been
attempting this for a couple hours and haven't got anywhere. If the
prices you seen on the zip file that I uploaded were referenced back to
a different workbook would I have to change the code?


I don't know what you mean by "referenced back to a different workbook"
But if the cells showing the prices really contain a formula, rather than a number as shown in the worksheet you sent, then the code would need to be changed.