change the code to be a formula
i have the following code added to the s/s tab which does what i want but is
there a way of adding this to cell like a formula thing like vlookup?? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 2 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Station").Range("A1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0) Application.EnableEvents = True End If End Sub thanks pete |
change the code to be a formula
First, your code actually changes the value of the cell after you type in your
data. You won't be able to do that using a formula (well, unless you type the formula in that cell). You might be able to use a formula in an adjacent cell to return that same value, though. Something like this in C7: =OFFSET(Station!A1,MATCH(B7,StationList,0),0) If that doesn't work, you may want to post the address of StationList. It'll make testing a bit easier. Little pete wrote: i have the following code added to the s/s tab which does what i want but is there a way of adding this to cell like a formula thing like vlookup?? Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 2 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False Target.Value = Worksheets("Station").Range("A1") _ .Offset(Application.WorksheetFunction _ .Match(Target.Value, Worksheets("Station").Range("StationList"), 0), 0) Application.EnableEvents = True End If End Sub thanks pete -- Dave Peterson |
All times are GMT +1. The time now is 01:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com