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

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.
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
Extract rows based upon unique data in first column of an array
Replies
2
Views
194
RJC40
R
K
  • Question
VBA Code using Table References instead of column numbers
Replies
1
Views
456
CraigTriesHisBest
C
S
  • Question
User verification-VBA code
Replies
2
Views
392
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
56
Fluff
Fluff

Forum statistics

Threads
1,214,606
Messages
6,120,497
Members
448,967
Latest member
visheshkotha

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