Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-21-2019, 07:52 AM
Bumba Bumba is offline How do I implement my custom function in my VBA code? Windows 7 32bit How do I implement my custom function in my VBA code? Office 2007
Novice
How do I implement my custom function in my VBA code?
 
Join Date: Jan 2019
Posts: 23
Bumba is on a distinguished road
Default How do I implement my custom function in my VBA code?

In a workbook of mine there are 2 worksheets (Sheet1 & Sheet2) and Sheet1 has some data like (first image)



I'm trying to copy the data from columns SERIAL NO., HS CODE and PALLET MMT to Sheet2's columns PROD. ID, HS CODE & 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 & divided by 1000)

Default Sheet2 looks like: (to the right of the first image)

Result Sheet2 data should look like: (last image)

Note: I want to get the range to copy to & the range to paste dynamically and not hard code the ranges.

I've done:
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
I've also made a function to calculate NET WT. & but struggling to figure out how to use it in my code without making it too complicated
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 & IIf(ch Like "[0-9]", ch, " ")
    Next i
    Result = Application.Trim(Result)
    netWT = (Split(Result, " ")(1) * Split(Result, " ")(2)) / 1000
End Function
Help please. Different approach than mine are also welcome...It just has to do the job efficiently.
Attached Images
File Type: png 1.png (20.3 KB, 16 views)
File Type: png 2.png (12.0 KB, 16 views)
File Type: png 3.png (12.1 KB, 16 views)
Reply With Quote
  #2  
Old 12-21-2019, 05:26 PM
p45cal p45cal is offline How do I implement my custom function in my VBA code? Windows 10 How do I implement my custom function in my VBA code? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 342
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

many ways, among them:
Code:
xx = netWT("3x(20x20 ml)")
Range("A1").Value = netWT("3x(20x20 ml)")
 Range("A1").Value = netWT(Range("E4").Value)

Attach a workbook so we can experiment.
Reply With Quote
  #3  
Old 12-21-2019, 05:56 PM
Bumba Bumba is offline How do I implement my custom function in my VBA code? Windows 7 32bit How do I implement my custom function in my VBA code? Office 2007
Novice
How do I implement my custom function in my VBA code?
 
Join Date: Jan 2019
Posts: 23
Bumba is on a distinguished road
Default

Attached a sample workbook.

Note: I do not want to hard code the ranges like
Code:
Range("A1").Value = netWT(Range("E4").Value)
but make them dynamic, like the range to copy data from and paste data to should be done dynamically in a loop or something.
Attached Files
File Type: xlsm sample.xlsm (19.6 KB, 2 views)
Reply With Quote
  #4  
Old 12-22-2019, 08:24 AM
p45cal p45cal is offline How do I implement my custom function in my VBA code? Windows 10 How do I implement my custom function in my VBA code? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 342
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

A few options in the attached.
You can enable lines ending in 'debug line if you want to follow what's happening as you step through the code with F8 on the keyboard.
You can move the source data anywhere on sheet1, you can move the headers any on Sheet2, in any order, separated by blank columns if you want, but they should be on the same row.


Edit:
I see that you've asked this question elsewhere (excel - Using a UDF in a VBA copy paste macro - Stack Overflow), you're really obliged to inform people of that.
Have a read of Excelguru Help Site - A message to forum cross posters
Get known as a cross-poster without supplying links and you'll find your responses drying up.


ps. It would be nice if you acknowledge it when people try to help - nothing from you the last two times I tried here.
Attached Files
File Type: xlsm msofficeforums44085sample.xlsm (21.9 KB, 3 views)
Reply With Quote
  #5  
Old 12-22-2019, 06:04 PM
Bumba Bumba is offline How do I implement my custom function in my VBA code? Windows 7 32bit How do I implement my custom function in my VBA code? Office 2007
Novice
How do I implement my custom function in my VBA code?
 
Join Date: Jan 2019
Posts: 23
Bumba is on a distinguished road
Default

Quote:
I see that you've asked this question elsewhere (excel - Using a UDF in a VBA copy paste macro - Stack Overflow), you're really obliged to inform people of that.
Have a read of Excelguru Help Site - A message to forum cross posters
Get known as a cross-poster without supplying links and you'll find your responses drying up.
I didn't knew to reference links but I'll keep that in mind next time for sure.

Thanks for your reply.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I implement my custom function in my VBA code? VBA code to save to individual files and skipif function beefcake2000 Mail Merge 2 12-05-2017 03:19 AM
How do I implement my custom function in my VBA code? Custom ribbon button or shortcut for a specific function Nicobisgaard Word 6 04-22-2015 04:39 AM
How do I implement my custom function in my VBA code? Custom fields used in the Compare Function OTPM Project 3 03-02-2015 06:43 AM
Custom formatting code - rounding problem venkys4u Excel 1 08-14-2012 07:45 PM
Change format of date when using Now function in VB code Bondai Excel Programming 2 03-02-2012 05:09 PM


All times are GMT -7. The time now is 02:29 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft