Sep 10 2019 06:15 AM
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
Sep 10 2019 10:25 AM
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...
Sep 17 2019 11:00 AM
@Subodh_Tiwari_sktneer sir i don't know how to thank u u did the right job for me thanks <3
Sep 17 2019 11:15 AM
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.