Excel Macro - What am I missing?

[Hoopsah]

Hi,

Can you have a look at the macro behind the button "Edit" on Sheet 1.

It should take the value from cell F20 and copy it into the tab - Metering under column J

THe code I am using is:


Sub Amend_Record()' Amend_Record Macro




Dim pos As Long
Dim UTask As String


UTask = Range("UTask").Value
On Error Resume Next
pos = Application.Match(UTask, wsMetering.Columns(8), 0)
On Error GoTo 0

With wsMetering

If pos > 0 Then

.Cells(pos, "J").Value = Range("F20").Value


MsgBox "Record " & UTask & " amended"


Else

MsgBox "Not a Valid Reference", vbExclamation, "ERROR"
End If

End With


End Sub





All help Appreciated

[Paul_Hossler]

Marked 4 lines to look at

1. I always use Option Explicit
2. Since it wasn't there wsMetering was not flagged as a error
3. You used wsMetering, but didn't Set it
4. UTask is a String, but the .Match is a Long. You could probably just Dim UTask as a Long





Option Explicit '---------------------
Sub Amend_Record()
Dim wsMetering As Worksheet '-------------
Dim pos As Long
Dim UTask As String
Set wsMetering = Worksheets("Metering") '--------------
UTask = Range("UTask").Value

On Error Resume Next
pos = Application.Match(CLng(UTask), wsMetering.Columns(8), 0) '----------------
On Error GoTo 0

With wsMetering

If pos > 0 Then

.Cells(pos, "J").Value = Range("F20").Value


MsgBox "Record " & UTask & " amended"


Else

MsgBox "Not a Valid Reference", vbExclamation, "ERROR"
End If

End With


End Sub



Paul

[Hoopsah]

Your a star Paul.

It's been a wee while since I had to do anything like this and my brain got a bit frozen.

Your code works perfectly,

Thanks for your help

Gerry

[Paul_Hossler]

No problem - second set of eyes

Paul

PS -- You can mark it [Solved] using Thread Tools

10689