Test when value of an array is filled

S

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
Hello guys,

now I would need some helping hands with following UDF.

Code: 
Function ReNrAendern(ByVal strText As String) As String
    Dim strOld(0 To 11) As Variant
    Dim strNew As String
    
    Dim i As Integer
    
    strOld(0) = "RE "
    strOld(1) = "RE. "
    strOld(2) = "RE: "
    strOld(3) = "Re "
    strOld(4) = "Re. "
    strOld(5) = "Re: "
    strOld(6) = "Rg "
    strOld(7) = "Rg. "
    strOld(8) = "Rg: "
    strOld(9) = "RG "
    strOld(10) = "RG. "
    strOld(11) = "RG: "

    strNew = "RNR. "
    
    For i = LBound(strOld) To UBound(strOld)
        strText = Replace(strText, strOld(i), strNew)
    Next i
    
    ReNrAendern = strText
End Function

So how can I test this code, what strOld array is used when I step through the code using F8

The reason behind this is that mostly the function replace how it intend to but with one case it replace the strText not the way I want it.
So I was trying to step through the code but not really find what array is used if the strText contains one of the strOld within the strText.

Hope I did explain this good enough and someone can give me a hint on that.

I guess it should be a watch added or? But with the watch I still was not sure how to find when it was driggert.
Or how to look for it correctly.

Many thanks
 
Last edited:
Sort by date Sort by votes
silentwolf said:
Now I think it is working ..) but just one last thing..
About R2012 that can not be fixed with the array or?
Click to expand...
Will the R2012 (I presume the 2012 part can change) always appear at the end of the text like your examples show?
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sorry guys I was not at the computer today so I will need to look into that and give you proper answers hopefully tomorrow.
Just wanted to let you know.
Many thanks
 
Upvote 0
Try
Code: 
For i = LBound(strOld) to Ubound(StrOld)-1
    strText = Replace(strText, strOld(i), strOld(Ubound(strOld))
Next i

StrText = Replace(strText, strOld(UBound(strOld), strNew)
 
Last edited:
Upvote 0
Hi, @mikerickson thank you also for your reply.

For i = LBound(strOld) to Ubound(StrOld)-1
strText = Replace(strText, strOld(i), strOld(Ubound(strOld))
Next i

StrText = Replace(strText, strOld(UBound(strOld), strNew)
Click to expand...

with this code it gives me a syntax error on both lines so I changed it to

Code: 
    For i = LBound(strOld) To UBound(strOld) - 1
    strText = Replace(strText, strOld(i), strOld(UBound(strOld))[COLOR=#ff0000])[/COLOR]
    Next i
that part should be right like that

but on this line

Code: 
strText = Replace(strText, [COLOR=#ff0000]strOld[/COLOR](UBound(strOld), strNew))

wront number of arguments or something like that..

so I did try to change it but no luck getting it right.

How would that need to be changed ?
@Rick Rothstein
Will the R2012 (I presume the 2012 part can change) always appear at the end of the text like your examples show?
Click to expand...

Yes those numbers are Invoice Numbers and I like to get those Invoice Numbers out of the text so I can track which Invoice has been paid.

And customers always write those number in different format which makes things a little difficult.
That's why I like to change all those Invoce prefixes to a standart one so I can then extract those numbers easily to a different column.

Many thanks to all who are helping me to achive this :)
 
Upvote 0
silentwolf said:
Code: 
Function ReNrAendern(ByVal strText As String) As String
    Dim strOld(0 To 82) As Variant
    Dim strNew As String
    
    Dim i As Integer
    
    strOld(1) = "Rechnungs- Nr "
    strOld(2) = "Rechnungs- Nr. "
    strOld(3) = "Rechnungs- Nr: "
    strOld(4) = "Rechnungs Nr "
    strOld(5) = "Rechnungs Nr. "
    strOld(6) = "Rechnungs Nr: "
    strOld(7) = "Rechnungs-Nr "
    strOld(8) = "Rechnungs-Nr. "
    strOld(9) = "Rechnungs-Nr.: "
    strOld(10) = "RechnungsNr "
    strOld(11) = "RechnungsNr."
    strOld(12) = "RechnungsNr:"
    strOld(13) = "Rechnungsnr "
    strOld(14) = "Rechnungsnr. "
    strOld(15) = "Rechnungsnr: "
    strOld(16) = "Belegnummer: "
    strOld(17) = "Belegnummer."
    strOld(18) = "Belegnummer "
    strOld(19) = "Rechnung "
    strOld(20) = "Rechnung. "
    strOld(21) = "Rechnung: "
    strOld(22) = "Beleg Nr: "
    strOld(23) = "Beleg Nr. "
    strOld(24) = "Beleg Nr "
    strOld(25) = "Beleg. Nr: "
    strOld(26) = "Beleg. Nr. "
    strOld(27) = "Beleg. Nr "
    strOld(28) = "Beleg nr: "
    strOld(29) = "Beleg nr. "
    strOld(30) = "Beleg nr "
    strOld(31) = "Beleg.Nr.: "
    strOld(32) = "Belegnr. "
    strOld(33) = "belegnr. "
    strOld(34) = "Rechnr: "
    strOld(35) = "Rechnr. "
    strOld(36) = "Rechnr "
    strOld(37) = "RE NR: "
    strOld(38) = "RE NR. "
    strOld(39) = "RE NR "
    strOld(40) = "Re.Nr. : "
    strOld(41) = "Re.Nr. "
    strOld(42) = "RE Nr: "
    strOld(43) = "RE Nr. "
    strOld(44) = "RE Nr "
    strOld(45) = "Re Nr "
    strOld(46) = "Re Nr. "
    strOld(47) = "Re Nr: "
    strOld(48) = "R.-NR: "
    strOld(49) = "R.-NR. "
    strOld(50) = "R.-NR "
    strOld(51) = "Rg. Nr: "
    strOld(52) = "Rg. Nr. "
    strOld(53) = "Rg. Nr "
    strOld(54) = "Rg Nr: "
    strOld(55) = "Rg Nr. "
    strOld(56) = "Rg Nr "
    strOld(57) = "RENR "
    strOld(58) = "RE####"
    strOld(59) = "Re####"
    strOld(60) = "RgNr: "
    strOld(61) = "RgNr "
    strOld(62) = "RgNr. "
    strOld(63) = "A1072/"
    strOld(64) = "RNR. "
    strOld(65) = "RNR: "
    strOld(66) = "RE "
    strOld(67) = "RE. "
    strOld(68) = "RE: "
    strOld(69) = "RN: "
    strOld(70) = "RN. "
    strOld(71) = "RN "
    strOld(72) = "RG: "
    strOld(73) = "RG. "
    strOld(74) = "RG "
    strOld(75) = "Rg: "
    strOld(76) = "Rg. "
    strOld(77) = "Rg "
    strOld(78) = "Re: "
    strOld(79) = "Re. "
    strOld(80) = "Re "
    strOld(81) = "RNR "
    strOld(82) = "R###"

    strNew = "RNR. "
    
    For i = LBound(strOld) To UBound(strOld)
        If InStr(1, strText, strOld(i)) > 0 Then
            strText = Replace(strText, strOld(i), strNew)
            Exit For
        End If
    Next i
    
    ReNrAendern = strText
End Function
Click to expand...
If I am not mistaken (and I do not believe I am), the above function can be replaced by this one (which also handles your R1234 problem as well)...
Code: 
[table="width: 500"]
[tr]
	[td]Function ReNrAendern(ByVal strText As String) As String
  Dim X As Long, Original As Variant, Replacement As Variant, Parts() As String
  Original = Array(".", ":", "/", "###", " : ", "RE NR")
  Replacement = Array(". ", ": ", "/ ", "### ", ": ", "RENR")
  For X = LBound(Original) To UBound(Original)
    strText = Application.Trim(Replace(strText, Original(X), Replacement(X), , , vbTextCompare))
  Next
  Parts = Split(strText)
  If Parts(UBound(Parts)) Like "*[A-Za-z]*#" Then
    For X = Len(Parts(UBound(Parts))) To 1 Step -1
      If Mid(Parts(UBound(Parts)), X, 1) Like "[!0-9]" Then Parts(UBound(Parts)) = "RNR " & Mid(Parts(UBound(Parts)), X + 1)
    Next
  Else
    If LCase(Parts(UBound(Parts) - 2)) & LCase(Parts(UBound(Parts) - 1)) Like "rechnungs*nr*" Then
      Parts(UBound(Parts) - 2) = ""
    End If
    Parts(UBound(Parts) - 1) = "RNR."
  End If
  ReNrAendern = Application.Trim(Join(Parts))
End Function[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
Hi Rick,
many thanks for your reply..

Unless I did not make a mistake with the code there is a bug in this line
Code: 
    If LCase(Parts(UBound(Parts) - 2)) & LCase(Parts(UBound(Parts) - 1)) Like "rechnungs*nr*" Then

Index out of range or something like that

Thanks
 
Upvote 0
silentwolf said:
Unless I did not make a mistake with the code there is a bug in this line
Code: 
    If LCase(Parts(UBound(Parts) - 2)) & LCase(Parts(UBound(Parts) - 1)) Like "rechnungs*nr*" Then

Index out of range or something like that
Click to expand...
Show me the text that was passed into the function when that error occurred.
 
Upvote 0
Hi Rick,

"Privatentnahme"

So basically when there is no ReNrAendern..

I guess it will be with all text in that column if there is no ReNr
 
Upvote 0
silentwolf said:
Hi Rick,

"Privatentnahme"

So basically when there is no ReNrAendern..

I guess it will be with all text in that column if there is no ReNr
Click to expand...
"Privatentnahme" is the entire text in the cell? If yes, does that mean you have cells without a RNR number in them (at the end of the text)?
 
Upvote 0
Hi Rick,
"Privatentnahme" is the entire text in the cell? If yes, does that mean you have cells without a RNR number in them (at the end of the text)?
Click to expand...
yes that is right.

Not even at the end only the Number can be in between the text too.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Extract rows based upon unique data in first column of an array
Replies
2
Views
203
RJC40
R
K
  • Question
VBA Code using Table References instead of column numbers
Replies
1
Views
460
CraigTriesHisBest
C
S
  • Question
User verification-VBA code
Replies
2
Views
395
Sunnygreet
S
B
  • Question
Excel VBA Userform: Filtering a Listbox and Showing a "Live" Version of the Filtered Data Based on a Texbox Value
Replies
1
Views
3K
bauer32
B
franklin_m
  • Solved
Learning arrays ... error when trying to read from range using cells property
Replies
4
Views
60
Fluff
Fluff

Forum statistics

Threads
1,214,798
Messages
6,121,636
Members
449,043
Latest member
farhansadik

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top