Home

How can I generate a number patterns from a source value

%3CLINGO-SUB%20id%3D%22lingo-sub-846565%22%20slang%3D%22en-US%22%3EHow%20can%20I%20generate%20a%20number%20patterns%20from%20a%20source%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846565%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EI'am%20filling%20some%20electricty%20products%20on%20my%20exceel%20%2C%20the%20problem%20is%20that%20i%20have%2035K%20products%20for%20the%20momment%20and%20i'am%20filling%20one%20by%20one%20%2C%20is%20there%20any%20formula%20to%20fill%20faster.%3CBR%20%2F%3EI'am%20asking%20to%20fill%20faster%20Column%20%3CSTRONG%3EB%3C%2FSTRONG%3E.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EIf%20someone%20know%20how%20to%20do%20it%20%2C%20can%20he%20send%20me%20a%20video%20how%20to%20do.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20title%3D%22Problem%22%20href%3D%22https%3A%2F%2Fi.imgur.com%2Fe6pWrsM.jpg%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EPhoto%20of%20Problem%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-846565%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847195%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20generate%20a%20number%20patterns%20from%20a%20source%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406165%22%20target%3D%22_blank%22%3E%40Edo123451%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20open%20to%20a%20VBA%20solution%2C%20the%20following%20macro%20will%20fill%20the%20column%20B%20as%20desired.%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20click%20the%20button%20on%20Sheet1%20to%20run%20the%20code.%3C%2FP%3E%3CP%3EThe%20code%20is%20placed%20on%20Module1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20FillPattern()%0ADim%20Rng%20%20%20%20%20%20%20%20%20As%20Range%0ADim%20Cel%20%20%20%20%20%20%20%20%20As%20Range%0ADim%20LR%20%20%20%20%20%20%20%20%20%20As%20Long%0ADim%20strNum%20%20%20%20%20%20As%20String%0ADim%20Num%20%20%20%20%20%20%20%20%20As%20Variant%0ADim%20ZeroCnt%20%20%20%20%20As%20Long%0A%0AApplication.ScreenUpdating%20%3D%20False%0A%0ALR%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%0ASet%20Rng%20%3D%20Range(%22A1%3AA%22%20%26amp%3B%20LR)%0A%0AFor%20Each%20Cel%20In%20Rng%0A%20%20%20%20strNum%20%3D%20getNumber(Cel.Value)%0A%20%20%20%20strNum%20%3D%20Left(strNum%2C%20Len(strNum)%20-%201)%0A%20%20%20%20%0A%20%20%20%20If%20Left(strNum%2C%201)%20%3D%20%22R%22%20Then%0A%20%20%20%20%20%20%20%20Num%20%3D%20Replace(strNum%2C%20%22R%22%2C%20%22%22)%0A%20%20%20%20%20%20%20%20Num%20%3D%20%220.%22%20%26amp%3B%20Num%0A%20%20%20%20%20%20%20%20Cel.Offset(0%2C%201).NumberFormat%20%3D%20%220.00%22%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20ZeroCnt%20%3D%20Right(strNum%2C%201)%0A%20%20%20%20%20%20%20%20Num%20%3D%20Left(strNum%2C%20Len(strNum)%20-%201)%0A%20%20%20%20%20%20%20%20Num%20%3D%20Num%20%26amp%3B%20WorksheetFunction.Rept(%220%22%2C%20ZeroCnt)%20%26amp%3B%20%22.0%22%0A%20%20%20%20%20%20%20%20Cel.Offset(0%2C%201).NumberFormat%20%3D%20%220.0%22%0A%20%20%20%20End%20If%0A%20%20%20%20%0A%20%20%20%20Cel.Offset(0%2C%201).Value%20%3D%20Num%0ANext%20Cel%0A%0AApplication.ScreenUpdating%20%3D%20True%0AEnd%20Sub%0A%0AFunction%20getNumber(ByVal%20str%20As%20String)%20As%20String%0ADim%20strNum%20%20%20%20%20%20As%20String%0ADim%20Num%20%20%20%20%20%20%20%20%20As%20Long%0ADim%20Matches%20%20%20%20%20As%20Object%0A%0AWith%20CreateObject(%22VBScript.RegExp%22)%0A%20%20%20%20.Global%20%3D%20False%0A%20%20%20%20.Pattern%20%3D%20%22R%3F(%5Cd%2B)%5BUV%5D%22%0A%20%20%20%20If%20.test(str)%20Then%0A%20%20%20%20%20%20%20%20Set%20Matches%20%3D%20.Execute(str)%0A%20%20%20%20%20%20%20%20getNumber%20%3D%20Matches(0)%0A%20%20%20%20End%20If%0AEnd%20With%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20implement%20this%20code%20to%20your%20original%20workbook%2C%20follow%20these%20steps...%3C%2FP%3E%3COL%3E%3CLI%3EOpen%20your%20file%20and%20press%20Alt%2BF11%20to%20open%20the%20VB%20Editor.%3C%2FLI%3E%3CLI%3EOn%20VB%20Editor%20Ribbon--%26gt%3B%20Insert%20Tab%20--%26gt%3B%20Choose%20Module%20and%20then%20copy%20the%20above%20code%20and%20paste%20it%20into%20the%20opened%20code%20window.%3C%2FLI%3E%3CLI%3EClose%20the%20VB%20Editor%20window.%3C%2FLI%3E%3CLI%3ESave%20your%20Excel%20Workbook%20as%20Macro-Enabled%20Workbook%2C%3C%2FLI%3E%3CLI%3ETo%20insert%20a%20button%2C%20you%20may%20insert%20any%20Shape%20of%20your%20choice%2C%20edit%20it's%20text%20and%20right%20click%20on%20the%20inserted%20Shape%20and%20choose%20Assign%20Macro%20and%20select%20the%20macro%20%22FillPattern%22%20in%20the%20next%20Macro%20Window%20which%20would%20appear%20with%20the%20available%20macros%20in%20the%20workbook%20and%20then%20click%20ok%20to%20finish.%20So%20now%20you%20may%20click%20the%20inserted%20Shape%20to%20run%20your%20code.%3C%2FLI%3E%3CLI%3EYou%20may%20also%20run%20the%20code%20after%20pressing%20the%20Alf%2BF8%20to%20open%20the%20Macro%20Window%2C%20selecting%20the%20macro%20from%20the%20list%20and%20clicking%20on%20the%20Run%20button.%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859478%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20generate%20a%20number%20patterns%20from%20a%20source%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859478%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bsir%20i%20don't%20know%20how%20to%20thank%20u%20u%20did%20the%20right%20job%20for%20me%20thanks%20%3CLI-EMOJI%20id%3D%22lia_red-heart%22%20title%3D%22%3Ared_heart%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859528%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20generate%20a%20number%20patterns%20from%20a%20source%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859528%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F406165%22%20target%3D%22_blank%22%3E%40Edo123451%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20I%20could%20help.%3C%2FP%3E%3CP%3EPlease%20take%20a%20minute%20to%20accept%20the%20post%20with%20the%20proposed%20solution%20as%20a%20Best%20Answer%2FResponse%20which%20will%20mark%20your%20question%20as%20Solved.%3C%2FP%3E%3CP%3EYou%20may%20hit%20the%20Like%20button%20under%20the%20post%20which%20resolved%20your%20question%2C%20that's%20another%20way%20to%20say%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Edo123451
New Contributor

I'am filling some electricty products on my exceel , the problem is that i have 35K products for the momment and i'am filling one by one , is there any formula to fill faster.
I'am asking to fill faster Column B.

If someone know how to do it , can he send me a video how to do.

Photo of Problem

3 Replies
Highlighted

@Edo123451 

If you are open to a VBA solution, the following macro will fill the column B as desired.

In the attached, click the button on Sheet1 to run the code.

The code is placed on Module1.

 

Sub FillPattern()
Dim Rng         As Range
Dim Cel         As Range
Dim LR          As Long
Dim strNum      As String
Dim Num         As Variant
Dim ZeroCnt     As Long

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range("A1:A" & LR)

For Each Cel In Rng
    strNum = getNumber(Cel.Value)
    strNum = Left(strNum, Len(strNum) - 1)
    
    If Left(strNum, 1) = "R" Then
        Num = Replace(strNum, "R", "")
        Num = "0." & Num
        Cel.Offset(0, 1).NumberFormat = "0.00"
    Else
        ZeroCnt = Right(strNum, 1)
        Num = Left(strNum, Len(strNum) - 1)
        Num = Num & WorksheetFunction.Rept("0", ZeroCnt) & ".0"
        Cel.Offset(0, 1).NumberFormat = "0.0"
    End If
    
    Cel.Offset(0, 1).Value = Num
Next Cel

Application.ScreenUpdating = True
End Sub

Function getNumber(ByVal str As String) As String
Dim strNum      As String
Dim Num         As Long
Dim Matches     As Object

With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "R?(\d+)[UV]"
    If .test(str) Then
        Set Matches = .Execute(str)
        getNumber = Matches(0)
    End If
End With
End Function

 

To implement this code to your original workbook, follow these steps...

  1. Open your file and press Alt+F11 to open the VB Editor.
  2. On VB Editor Ribbon--> Insert Tab --> Choose Module and then copy the above code and paste it into the opened code window.
  3. Close the VB Editor window.
  4. Save your Excel Workbook as Macro-Enabled Workbook,
  5. To insert a button, you may insert any Shape of your choice, edit it's text and right click on the inserted Shape and choose Assign Macro and select the macro "FillPattern" in the next Macro Window which would appear with the available macros in the workbook and then click ok to finish. So now you may click the inserted Shape to run your code.
  6. You may also run the code after pressing the Alf+F8 to open the Macro Window, selecting the macro from the list and clicking on the Run button.

 

@Subodh_Tiwari_sktneer sir i don't know how to thank u u did the right job for me thanks

@Edo123451 

You're welcome! Glad I could help.

Please take a minute to accept the post with the proposed solution as a Best Answer/Response which will mark your question as Solved.

You may hit the Like button under the post which resolved your question, that's another way to say thanks.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies