title: Using a UDF in a VBA copy paste macro
body: <p>In a workbook of mine there are 2 worksheets (Sheet1 &amp; Sheet2) and Sheet1 has some data like</p>
  
  <p><a href="https://i.stack.imgur.com/z93Et.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z93Et.png" alt="s1"></a></p>
  
  <p>I'm trying to copy the data from columns SERIAL NO., HS CODE and PALLET MMT to Sheet2's columns PROD. ID, HS CODE &amp; NET WT. respectively. Now the first two copies are pretty straight forward but the problem I'm having is generating NET WT. (it is the product of two numbers inside the brackets &amp; divided by 1000)</p>
  
  <p>Default Sheet2 looks like:</p>
  
  <p><a href="https://i.stack.imgur.com/xnPKp.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xnPKp.png" alt="s2"></a></p>
  
  <p>Result Sheet2 data should look like:</p>
  
  <p><a href="https://i.stack.imgur.com/DnQxy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DnQxy.png" alt="s2Result"></a></p>
  
  <p>I've done:</p>
  
  <pre><code>Sub Macro1()
  '
  ' Macro1 Macro
  '
  
  '
      Cells.Find(What:="SERIAL", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
      ActiveCell.Offset(1, 0).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Sheets("Sheet2").Select
      Cells.Find(What:="PROD", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
      Range("B4").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Application.CutCopyMode = False
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
      Sheets("Sheet1").Select
      Range("A1").Select
      Cells.Find(What:="CODE", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
          :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
          False, SearchFormat:=False).Activate
      ActiveCell.Offset(1, 0).Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Sheets("Sheet2").Select
      Range("C4").Select
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
      Application.CutCopyMode = False
      With Selection
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlBottom
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
  ' @@@@@@@@@@ NET WT. ?????? @@@@@@@@@@@@@
  End Sub
  </code></pre>
  
  <p>I've also made a function to calculate NET WT. &amp; but struggling to figure out how to use it in my code without making it too complicated</p>
  
  <pre><code>Function netWT(CellRef As String)
      Dim i As Long, Result As String, ch As String
      For i = 1 To Len(CellRef)
          ch = Mid(CellRef, i, 1)
          Result = Result &amp; IIf(ch Like "[0-9]", ch, " ")
      Next i
      Result = Application.Trim(Result)
      netWT = (Split(Result, " ")(1) * Split(Result, " ")(2)) / 1000
  End Function
  </code></pre>
  
  <p>Help please. Different approach than mine are also welcome...It just has to do the job efficiently.</p>