Creating default value with VBA

[jalewis]

I'm using Excel 2010. I have two rows, H and I, that both have data validation enabled using a list. What I want to do is when a value is entered in one of the cells in column H have it be the default value in column I (same row) which can still be overwritten. I have named the two rows H = "Stock_UOM" and I = "Cost_UOM". This is what I have in VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColH As Range, ColI As Range
Set ColH = Range("Stock_UOM")
Set ColI = Range("Cost_UOM")
If Intersect(Target, ColH) Is Nothing Then Exit Sub
Application.EnableEvents = False
ColI = ColH
Application.EnableEvents = True
End Sub

I can get this to work if I change the named ranges to specific cells (i.e. H3 and I3) but of course it only works for that cell. I've also tried it without named ranges (H:H, I:I or H1:H65536, I1:I65536) to see if that worked but it doesn't.

Thanks.

 

[Jeffrey Smith]

Hello jalewis,

ColH is a range and ColI is a range also. you need to reference the one cell that was changed. Please try this:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ColH As Range, ColI As Range
  '
  Set ColH = Range("Stock_UOM")
  Set ColI = Range("Cost_UOM")
  If Intersect(Target, ColH) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  Target.Offset(0, 1) = Target.Value
  Application.EnableEvents = True
End Sub

 

[Jeffrey Smith]

Of course, with that, then you don't need to use ColI range at all.

 

[jalewis]

That works. Thanks. I would have liked to use the named range for the destination. That way if I move the column I don't have to worry about remembering to change the script. At least this works for now. Thanks again.

 

[mikerickson]

You could avoid the .Offset with code like this

Set ColH = Range("Stock_UOM")
Set ColI = Range("Cost_UOM")

With ColH
    If Not Application.Intersect(Target, .Cells) Is Nothing And .Cells.Count = 1 Then
        Application.EnableEvents = False
        Application.Intersect(Target.EntireRow, ColI).Value = Target.Value
        Application.EnableEvents = True
    End If
End With

 

[jalewis]

I must be doing something wrong because that isn't working. What does the .Cells.Count = 1 do? Is it looking at the first row because I could be entering information in column H in any row.

 

[mikerickson]

The .Cells.Count = 1 condition tests if more than one cell has been changed (as if you pasted a range of cells onto column H)

 

[jalewis]

Ok I understand that. So the example using the named range isn't working. When I enter a value in column H nothing is placed in column I.

 

[Jeffrey Smith]

@mike

I like this. Clean!

Application.Intersect(Target.EntireRow, ColI).Value = Target.Value

 

[jalewis]

That's it! That works perfectly substituting the Target.Offset(0,1) suggestion that Jeffery originally gave me. Now I can define multiple named ranges and default the entry in column H to all of them. Thanks everyone for your help.