Solved: Clear Cell Content

[Hoopsah]

Hi

I have a page where the user can input a unique reference number.

If the number already exists then the details are brought up and the cells populated.

If it is a new number then it allows the user to input new details.

I am using this code in the sheet coding:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G11" '<== change to suit
Dim pos As Long

On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

On Error Resume Next
pos = Application.Match(.Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
If pos > 0 Then

Me.Range("D14").Value = Worksheets("Input Sheet").Cells(pos, "A").Value
Me.Range("D17").Value = Worksheets("Input Sheet").Cells(pos, "C").Value
Me.Range("G17").Value = Worksheets("Input Sheet").Cells(pos, "D").Value
Me.Range("J17").Value = Worksheets("Input Sheet").Cells(pos, "E").Value
Me.Range("D20").Value = Worksheets("Input Sheet").Cells(pos, "F").Value
Me.Range("G20").Value = Worksheets("Input Sheet").Cells(pos, "G").Value
Me.Range("D22").Value = Worksheets("Input Sheet").Cells(pos, "H").Value
Me.Range("D26").Value = Worksheets("Input Sheet").Cells(pos, "I").Value
Me.Range("H26").Value = Worksheets("Input Sheet").Cells(pos, "J").Value
Me.Range("D28").Value = Worksheets("Input Sheet").Cells(pos, "K").Value
Me.Range("H28").Value = Worksheets("Input Sheet").Cells(pos, "L").Value

End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



So that I can allow the user to browse files I need to have it so that if an EXISTING number is entered, the cells populate, but if the user then types in a NEW number the cells are cleared and allows new data to be entered.

Thanks for any help

[mdmackillop]

Rather than Match which can return an error, can you use Find?

Dim c As Range
Set c = Worksheets("Input Sheet").Columns(2).Find(target)
If Not c Is Nothing Then
pos = c.Row
'Set your values
Else
'Clear your values
End If

[Hoopsah]

Hi MD

When I try that coding and enter a reference number in cell G11 it just goes in to a loop and nothing happens :dunno

[Bob Phillips]

Is thgsi what you mean?



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "G11" '<== change to suit
Dim pos As Long

On Error GoTo ws_exit
Application.EnableEvents = False


If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

On Error Resume Next
pos = Application.Match(.Value, Worksheets("Input Sheet").Columns(2), 0)
On Error GoTo 0
If pos > 0 Then

Me.Range("D14").Value = Worksheets("Input Sheet").Cells(pos, "A").Value
Me.Range("D17").Value = Worksheets("Input Sheet").Cells(pos, "C").Value
Me.Range("G17").Value = Worksheets("Input Sheet").Cells(pos, "D").Value
Me.Range("J17").Value = Worksheets("Input Sheet").Cells(pos, "E").Value
Me.Range("D20").Value = Worksheets("Input Sheet").Cells(pos, "F").Value
Me.Range("G20").Value = Worksheets("Input Sheet").Cells(pos, "G").Value
Me.Range("D22").Value = Worksheets("Input Sheet").Cells(pos, "H").Value
Me.Range("D26").Value = Worksheets("Input Sheet").Cells(pos, "I").Value
Me.Range("H26").Value = Worksheets("Input Sheet").Cells(pos, "J").Value
Me.Range("D28").Value = Worksheets("Input Sheet").Cells(pos, "K").Value
Me.Range("H28").Value = Worksheets("Input Sheet").Cells(pos, "L").Value

Else
Me.Range("D14,D17,G17,J17,D20,G20,D22,D26,H26,D28,H28").Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[Hoopsah]

Perfect!!

You make this look easy Bob

Thanks for your help

Gerry