VBA Help

[Skybluekid]

I have a small query that I believe I will need <ACRONYM title="visual basic for applications">VBA</ACRONYM> to do the job.

I have a range of 3 cells, B38-B40. In each of the cells is a drop box which lists Breakdown, Mileage, Recovery and Sublet. If the user selects an item then I would like to see the following:

Item Selected in B38 In Cell F38 In Cell I38 In Cell J38
Breakdown "Breakdown Fee" 1 25
Mileage "Mileage" Blank = I38*0.5
Recovery "Recovery Fee" 1 Blank
Sublet Blank Blank Blank.

Naturally, if they choose anyone of those items in B39, then it would put the relevant information in F39, I39 and so on.

I know that this could be done with formulas, but I thinks it would cofuse a lot of them, having to retype over the formula.

Hope you can help.

 

[Snakehips]

Skybluekid,

Try this in the worksheet's code module....

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B38:B40")) Is Nothing Then
Application.EnableEvents = False
With Target
Select Case .Value
Case "Breakdown"
.Offset(0, 4) = "Breakdown Fee"
.Offset(0, 7) = 1
.Offset(0, 8) = 25
Case "Mileage"
.Offset(0, 4) = "Mileage"
.Offset(0, 7).ClearContents
.Offset(0, 8).FormulaR1C1 = "=RC[-1]*0.5"
Case "Recovery"
ColF = "Recovery Fee"
.Offset(0, 4) = "Recovery Fee"
.Offset(0, 7) = 1
.Offset(0, 8).ClearContents


Case "Sublet"
.Offset(0, 4) = "Sublet"
.Offset(0, 7).Resize(1, 2).ClearContents
Case Else
.Offset(0, 4).ClearContents
.Offset(0, 7).Resize(1, 2).ClearContents


End Select
End With
End If


Application.EnableEvents = True
End Sub

Hope that helps.

 

[Skybluekid]

Thanks for this. However, this doesn't seem to work. Would it make a difference that the cell references that I have given are merged cells?

 

[Snakehips]

Let's put it this way, it worked perfectly well when I tested it on cells that weren't merged.

Which cells are merged with which?

 

[Skybluekid]

The cells in column F, so F38 - F40 and cells in column I, so I38 - I40.

Would it help if I send you the file?

 

[Snakehips]

File would help.
Can you upload to Dropbox? Or PM me if you need an email address.