I'm trying to create an automatic username generator function. This is
what I have so far:

Function createusername(konto, art, adgang) As String

If UCase(adgang) = UCase("ja") Then

If UCase(art) = UCase("kunde") Then
usnam = konto & 1
ElseIf UCase(art) = UCase("agent") Then
usnam = konto & "_AG" & 1
End If
End If

createusername = usnam

End Function

If
Konto = "MM", Art = "Kunde" and adgang = "ja" then the userid will
created as MM1

If
Konto = "SS", Art = "agent" and adgang = "ja" then the userid will
created as SS_AG1

Now to my question. If userid MM1 alrready exist in column where the
function is used, the next useridf for same "konto" and "art" should
be MM2, next MM3 and so on. Same goes for art = "agent", like

SS_AG1
SS_AG2 an so on

How do I get the function to look above so to speak, compare "konto"
and "art" and then add 1 to the highest number used?

Jan

use the caller method ...this example returns the formula in the cell to the
left of teh cell with the function

Function xyx() As String
xyx = Range(Application.Caller.Address).Offset(, -1).Formula
End Function


"jkrons" wrote:

I'm trying to create an automatic username generator function. This is
what I have so far:

Function createusername(konto, art, adgang) As String

If UCase(adgang) = UCase("ja") Then

If UCase(art) = UCase("kunde") Then
usnam = konto & 1
ElseIf UCase(art) = UCase("agent") Then
usnam = konto & "_AG" & 1
End If
End If

createusername = usnam

End Function

If
Konto = "MM", Art = "Kunde" and adgang = "ja" then the userid will
created as MM1

If
Konto = "SS", Art = "agent" and adgang = "ja" then the userid will
created as SS_AG1

Now to my question. If userid MM1 alrready exist in column where the
function is used, the next useridf for same "konto" and "art" should
be MM2, next MM3 and so on. Same goes for art = "agent", like

SS_AG1
SS_AG2 an so on

How do I get the function to look above so to speak, compare "konto"
and "art" and then add 1 to the highest number used?

Jan



.

This works great, now i just have to put it into a loop that will loop
thru all cells above the function.

Jan

On 27 Okt., 13:40, Patrick Molloy
wrote:
use the caller method ...this example returns *the formula in the cell to the
left of teh cell with the function

Function xyx() As String
xyx = Range(Application.Caller.Address).Offset(, -1).Formula
End Function



"jkrons" wrote:
I'm trying to create an automatic username generator function. This is
what I have so far:

Function createusername(konto, art, adgang) As String

If UCase(adgang) = UCase("ja") Then

* * If UCase(art) = UCase("kunde") Then
* * * * usnam = konto & 1
* * ElseIf UCase(art) = UCase("agent") Then
* * * * usnam = konto & "_AG" & 1
* * End If
End If

createusername = usnam

End Function

If
Konto = "MM", Art = "Kunde" and adgang = "ja" then the userid will
created as MM1

If
Konto = "SS", Art = "agent" and adgang = "ja" then the userid will
created as SS_AG1

Now to my question. If userid MM1 alrready exist in column where the
function is used, the next useridf for same "konto" and "art" should
be MM2, next MM3 and so on. Same goes for art = "agent", like

SS_AG1
SS_AG2 an so on

How do I get the function to look above so to speak, compare "konto"
and "art" and then add 1 to the highest number used?

Jan

.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -