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:
So I guess exit sub is not working or am I wrong?
Click to expand...

You could alter the For loop like below to stop once you have made a replacement.

Code: 
    For i = LBound(strOld) To UBound(strOld)
        If InStr(1, strText, strOld(i)) > 0 Then
            strText = Replace(strText, strOld(i), strNew)
        Exit For
    Next i

You would still need to re-arrange the order of the replacement text strings.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi again,

I tried that now...
however there is still a problem :(

Code: 
    For i = LBound(strOld) To UBound(strOld)
'        If InStr(1, strText, strOld(i)) > 0 Then                    'Debug.Print strOld(i)
            strText = Replace(strText, strOld(i), strNew)
'        End If
'        Exit For
    Next i
When I leave End if out then there is an error next without an For statement.
Und with end if there is no data in the cell ..(
So I did use it as above so with the commands and ran it so it works again but still not with all so basically it is the same as befor.

Some return RNR. Nr. just furhter down the list in that column..

NOt a big issue but I would I would love to get it perfect :-)
 
Upvote 0
Sorry, it should look like:

Code: 
    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
 
Upvote 0
silentwolf said:
sorry that is not working I am affraid :( I get no updates at all .(
Click to expand...

Can you post your revised code in full? And an example of an input string and the expected result?
 
Last edited:
Upvote 0
Sure...

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

Code: 
Sub RechnungsNummerUpdate()
    Dim Buchung() As Variant
    Dim i As Integer

    Buchung = Range("J2", Range("J1").End(xlDown))

    For i = LBound(Buchung, 1) To UBound(Buchung, 1)
        Buchung(i, 1) = ReNrAendern(Buchung(i, 1))
    Next i

    Range("J2").Resize(UBound(Buchung, 1), 1) = Buchung
End Sub


then I have in column J something like

Mr FormR, Testinghof 25 8888 Sunny Place RE 2012
Christian Muster, Schillerhof IBAN: ATxx xxxx xxxx xxxx xxxx Re Nr. 2036
Christian Muster, Schillerhof IBAN: ATxx xxxx xxxx xxxx xxxx R2088

output should be

Mr FormR, Testinghof 25 8888 Sunny Place RNR 2012
Christian Muster, Schillerhof IBAN: ATxx xxxx xxxx xxxx xxxx RNR. 2036
Christian Muster, Schillerhof IBAN: ATxx xxxx xxxx xxxx xxxx RNR. 2088

and so on there are as you are aware many different ways of Re. Numbers.. as the array shows..

And some of them even write R2301 then it fails to but not sure how to change it in the strOld()array. Maybe you can also have a look at that please?
With the code I am using now there will be nothing updated.

Thanks for your help!

Much appreciated
 
Last edited:
Upvote 0
You have declared strOld as a zero based array but have assigned the values starting at strOld(1) - the instr() function thinks there is a match when comparing the empty value in strOld(0).

Try changing this line.

Rich (BB code): 
Dim strOld(0 To 82) As Variant

to

Rich (BB code): 
Dim strOld(1 To 82) As Variant
 
Upvote 0
Hi,
oh yes I did :( I did't look careful enough!
Sorry )

Now I think it is working ..) but just one last thing..
About R2012 that can not be fixed with the array or?

Many thanks for your help!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Extract rows based upon unique data in first column of an array
Replies
2
Views
380
RJC40
R
K
  • Question Question
VBA Code using Table References instead of column numbers
Replies
1
Views
622
CraigTriesHisBest
C
B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
222
Big_Al_fae_the_rigs
B
R
  • Question Question
Add a code or number at the end of the file name using VBA (Looped)
Replies
3
Views
122
HighAndWilder
H
H
  • Question Question
Permision Denied-VBA Code to list sub-folders
Replies
4
Views
122
howard
H

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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