Code to extract only number from a cell that contains Text and Number separated by Colon

[ragav_in]

Dear members,

I am currently working on multiple files which are in different format. However the last row of the Column A has a text "Record Count: XXXXX"; the number after the Colon is Dynamic and varies with file to file. I would like to have a macro the get the "digits" after the "colon" in each file and populate it in another sheet along with the workbook name. I have the below code which can do the activity, however the step of getting the number from that particular cell is not known to me. I am providing the code that I have written below.

sub getnumber()
Dim i As Integer, rngData As Range, load As Integer, mybook As String
'Setting the range
Set rngData = Range("A1").CurrentRegion
On Error Resume Next
'Removing Autofilter if applied
If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
End If
'Selecting the last cell in Column A which has the text "Record Count: "
Range("A1").Select
Selection.End(xlDown).Select
If ActiveCell.Value Like "*Record Count: *" Then
load = "code here" --> this is the missing piece
'get workbook name
mybook = ActiveWorkbook.Name
'pasting the data in another book "DefectLog.xlsx"
Workbooks("DefectLog.xlsx").Activate
Sheets ("Sheet1").Select
Range("A1").End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = mybook
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = load
End sub

I would request this Forum to help me in identifying the only step that is missing, so that I can start combining data from multiple files. I thank each and everyone in this forum for their time and effort to read this post and provide the solution.

Thanks
ragav_in

 

[Peter_SSs]

Is this it?

If ActiveCell.Value Like "Record Count: *" Then
  load = Split(ActiveCell.Value, ":")(1)
End If

 

[ragav_in]

Is this it?

If ActiveCell.Value Like "Record Count: *" Then
  load = Split(ActiveCell.Value, ":")(1)
End If

Hi Peter,

Thanks much for the assistance. However this code works when the Active Cell value is "Record Count: 18", the value of "load" is 18. However, if the Active Cell value is "Record Count: 456726", the value of "load" is returning 0 and not 456726. I just tested the code with different values. If the number after "colon" is 4 digits, the answer is returned correctly; however if the digits is increased to 5, it returns a 0 and not the 5 digits.

Note that in above code, "load" is declared of type "Integer". Is this correct declaration, and why is the code not returning for different text? Can you please provide some light on this?

Thanks again for your support and help in this regard.
ragav_in

 

[johnnyL]

Note that in above code, "load" is declared of type "Integer". Is this correct declaration, and why is the code not returning for different text? Can you please provide some light on this?

An Integer in VBA has maximum value of 32767
Use Long instead of Integer.

 

[Peter_SSs]

Note that in above code, "load" is declared of type "Integer". Is this correct declaration, ...

I never use Integer declarations. I believe that vba converts all Integer values to Long to work with them so you might as well save it the trouble by declaring them Long to start with.
The added advantage is to avoid the issue discussed above (plus it is shorter to type anyway )

 

[Rick Rothstein]

I'm with Peter and johnnyL... I never use Integer data types either. Besides the problem you ran into, you might have trouble determining what the problem is in the following. You run the following calculation on your calculator to see what the answer should be. Punch in 12344, press the multiplication key and punch in 8, press the division key and punch in 4, then press the equal key. Answer = 24688. Now run this macro...

Sub Test()
  Dim N As Integer
  N = 12344 * 8 / 4
  Debug.Print N
End Sub

Whoops! It raises an error. But all the number (12344, 8, 4 and 24688 are values that will fit in an Integer variable). True, but the first intermediary operation (12344*8) does not fit in an Integer variable and so the entire calculation fails. You could reverse the last two operations...

N = 12344 / 4 * 8

and then the macro would work fine. To avoid all the possible hassles using an Integer variable can cause, the simple solution is to just use a Long.

 

[johnnyL]

NLN

 

[Rick Rothstein]

The both of you were not with me that suggested to Dim as Long like over 9 hrs ago? OK, I see how it is.

Oversight corrected in Message #6.

 

[ragav_in]

Dear Peter_SSs, johnnyL, Rick Rothstein,

Thanks a lot for assisting me on the solution. Also it was very kind of you to have explained about the declaration of Integer vs Long, which was very helpful to know why the code was not returning the expected value. I thank you once again for all the support you have provided to me in this forum.

Thanks,
ragav_in

 

[Peter_SSs]

You're welcome. Glad we could help.