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.
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.
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"
ZeroCnt = Right(strNum, 1)
Num = Left(strNum, Len(strNum) - 1)
Num = Num & WorksheetFunction.Rept("0", ZeroCnt) & ".0"
Cel.Offset(0, 1).NumberFormat = "0.0"
Cel.Offset(0, 1).Value = Num
Application.ScreenUpdating = True
Function getNumber(ByVal str As String) As String
Dim strNum As String
Dim Num As Long
Dim Matches As Object
.Global = False
.Pattern = "R?(\d+)[UV]"
If .test(str) Then
Set Matches = .Execute(str)
getNumber = Matches(0)
To implement this code to your original workbook, follow these steps...
Open your file and press Alt+F11 to open the VB Editor.
On VB Editor Ribbon--> Insert Tab --> Choose Module and then copy the above code and paste it into the opened code window.
Close the VB Editor window.
Save your Excel Workbook as Macro-Enabled Workbook,
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.
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.