VBA, extract charecters from string, invalid use of null error

[jcauteru]

Hi All, <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Here is the situation, in sheet1 of a workbook, column A contains text values, for example "EXFRMR123456" or "EXFRMR143678". I am looking to extract the numeric sequence at the end of each cell into column B of the same worksheet. I wrote the following: <o></o>
<o></o>

[COLOR=black][FONT=Verdana]Sub find_char()<o:p></o:p>[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]Dim model_code As String[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Dim model_code_ex As String<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]model_code = Sheet1.Range("A:A").Text  'This line errors out[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]model_code_ex = Mid(model_code, 7, 6)<o:p></o:p>[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Sheet1.Range("B:B") = model_code_ex<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub<o:p></o:p>[/FONT][/COLOR]

<o></o>
<o></o>
The code returns an "invalid use of null" error and, according to the debugger, the line I commented next to is the culprit.<o></o>
<o></o>
I think the problem lies in the "Sheet1.Range("A:A").Text". When I change the range to one cell only the code works fine, only with the full column does it return the error.
<o></o>
<o>Also, it is worth noting that I have to do this in VBA, I cannot use the =mid function in sheet.</o>
<o></o>
<o></o>
Any help would be appreciated<o></o>
<o></o>
<o></o>
-Joe <o></o>

 

[VoG]

You will need to loop through the range.

 

[jcauteru]

Ya, thanks. I figured that after thinking about it a bit more. I solved it as follows:

Sub find_char()
Dim model_code As String
Dim model_code_ex As String
Dim rownum As Integer
 
rownum = 1
 
Do While Sheet1.Range("E" & rownum).Value <> ""
model_code = Sheet1.Range("E" & rownum).Text
model_code_ex = Mid(model_code, 7, 6)
Sheet1.Range("F" & rownum) = model_code_ex
rownum = rownum + 1
Loop

End Sub

 

[hiker95]

jcauteru,


Sample data before the macro:

Excel Workbook
	A	B
1	EXFRMR123456
2	EXFRMR143678
3	EXFRMR043678
4
Sheet1

After the macro:

Excel Workbook
	A	B
1	EXFRMR123456	123456
2	EXFRMR143678	143678
3	EXFRMR043678	043678
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractNumbers()
' hiker95, 03/16/2011
' http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2649101
Dim c As Range
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  With c.Offset(, 1)
    .NumberFormat = "@"
    .Value = TextNum(c, 0)
  End With
Next c
End Sub

Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' =TextNum(A1,1)
' 1 for Text only, 0 for Numbers only
With CreateObject("VBScript.RegExp")
  .Pattern = IIf(ref = True, "\d+", "\D+")
  .Global = True
  TextNum = .Replace(txt, "")
End With
End Function

Then run the ExtractNumbers macro.

 

[jcauteru]

Hiker - great solution! I'll have to think about which to use...unfortunatly the powers at be prefer less code to more elegant solutions.

final code:

Sub find_char()
Dim model_code As String
Dim model_code_ex As String
Dim rownum As Integer
rownum = 1
Do While Sheet1.Range("E" & rownum).Value <> ""
model_code = Sheet1.Range("E" & rownum).Text
model_code_ex = Mid(model_code, 9, 2)
Sheet1.Range("F" & rownum) = model_code_ex
Sheet1.Range("F" & rownum).NumberFormat = "000000"
rownum = rownum + 1
Loop

End Sub

 

[hiker95]

jcauteru,

Excel Workbook
	A	B
1	EXFRMR123456	123456
2	EXFRMR143678	143678
3	EXFRMR043678	043678
4
Sheet1

The formula in cell B1 copied down:

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)

 

[jcauteru]

I would, except:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o>....Also, it is worth noting that I have to do this in VBA,...</o><o></o>
<o></o>

<o></o>
<o></o>
-Joe <o></o>