Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default challenge! javascript function into excel function

Hello,
I am sending again my question or problem to you. i am not able to do so.
please help me.
I have one columen witch contain Ids 11 sifer in excel. i have one formula
in javascript to validate id number. i have 3500 id numbers and i can not
incert every number to check it is write or wrong. is there any way to find
out or convert that javascript function into excel and get all wrong and
right ids in next columen?

Right IDs,
01014927367
01015726136

Wrong Ids
15010933773
45290122333

I got this function.

<html
<head
<titlechecking ID number formula </title
</head
<body

<form
<INPUT NAME="ID" SIZE=11 maxlength=11
<INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number"
onClick="(ValidateID(this.form.ID.value))"<br
</form

<script type="text/jscript"

function ValidateID(ID)
{
var TmpStr
var Tmpr
var vekt1 = "376189452"
var vekt2 = "5432765432"
var tot = 0
var base = 11
var value
var vekt
var TmpStr2
var Tmpr2
var vekt2 = "5432765432"
var tot2= 0
var base = 11

// for first number
for (i=0;i<9 ;i++)
{
value = ID.substr(i,1)
vekt = vekt1.substr(i,1)
tot = tot + (parseInt(value) * parseInt(vekt))
}
i=tot/11
i=tot-(parseInt(i)*11)
i=11-i
TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

// for second number
for (y = 0; y < 10 ; y++)
{
value = ID.substr(y,1)
vekt = vekt2.substr(y,1)
tot2 = tot2 + (parseInt(value) * parseInt(vekt))
}
y=tot2/11
y=tot2-(parseInt(y)*11)
y=11-y
TmpStr2 = ID
TmpStr2 = ID.substr(10,1)
Tmpr2=(ID.length)

// integer check

if (base!=Tmpr)
{
document.write("You must enter 11 number for ID (0 - 9) ")
}
else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2)
{
document.write(ID +" " + "is right ID")
}
else
{
document.write("Obs!! You write wrong ID number!")
}

}

</script
</body
</html


best regards,

--
Kamila
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default challenge! javascript function into excel function

This involves two separate tasks:

1. translate the JavaScript into a simple, journalistic, English description
of the algorithm desired.

2. implement the above in VBA

If you can do the first, we can do the second.
--
Gary's Student
gsnu200706


"Kamila" wrote:

Hello,
I am sending again my question or problem to you. i am not able to do so.
please help me.
I have one columen witch contain Ids 11 sifer in excel. i have one formula
in javascript to validate id number. i have 3500 id numbers and i can not
incert every number to check it is write or wrong. is there any way to find
out or convert that javascript function into excel and get all wrong and
right ids in next columen?

Right IDs,
01014927367
01015726136

Wrong Ids
15010933773
45290122333

I got this function.

<html
<head
<titlechecking ID number formula </title
</head
<body

<form
<INPUT NAME="ID" SIZE=11 maxlength=11
<INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number"
onClick="(ValidateID(this.form.ID.value))"<br
</form

<script type="text/jscript"

function ValidateID(ID)
{
var TmpStr
var Tmpr
var vekt1 = "376189452"
var vekt2 = "5432765432"
var tot = 0
var base = 11
var value
var vekt
var TmpStr2
var Tmpr2
var vekt2 = "5432765432"
var tot2= 0
var base = 11

// for first number
for (i=0;i<9 ;i++)
{
value = ID.substr(i,1)
vekt = vekt1.substr(i,1)
tot = tot + (parseInt(value) * parseInt(vekt))
}
i=tot/11
i=tot-(parseInt(i)*11)
i=11-i
TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

// for second number
for (y = 0; y < 10 ; y++)
{
value = ID.substr(y,1)
vekt = vekt2.substr(y,1)
tot2 = tot2 + (parseInt(value) * parseInt(vekt))
}
y=tot2/11
y=tot2-(parseInt(y)*11)
y=11-y
TmpStr2 = ID
TmpStr2 = ID.substr(10,1)
Tmpr2=(ID.length)

// integer check

if (base!=Tmpr)
{
document.write("You must enter 11 number for ID (0 - 9) ")
}
else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2)
{
document.write(ID +" " + "is right ID")
}
else
{
document.write("Obs!! You write wrong ID number!")
}

}

</script
</body
</html


best regards,

--
Kamila

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,365
Default challenge! javascript function into excel function

Gary''s Student is correct - we need an explanation of how the input number
is to be manipulated to get the test for correctness value out of it.

I gave it a shot earlier in the day and was unable to get a valid ID result.
Either my translation of some of the Java constructs is incorrect or there's
a deeper problem.

For example, the java construct substr(L,s) is, to my way of interpreting
what I read about it the same as VB's Mid("text",s,L) where s is the starting
character position and L is the length of the portion (# of characters). I
also deduce that Java's parseInt(string) is the same as VB's Val(string).

The loops, I thought were C-like with
for(y=0; y<10,y++)
being equivalent to
For y = 1 to 9
in VB.

But then java threw me for a loop (so to speak) when it then does a
parseInt() on the y - which I was thinking was numeric.

Finally, that comparison in the else if statement really got me, because
that makes it look like ou're comparing integers to strings for equality.
The problem with that in VB would be that the string would, in some cases
have a leading zero and the integer, even when converted to a string, would
not have the leading zero.

As I said, I failed this one. So unless a someone proficient in Java
explains what's going on in the algorithm to us, or someone proficient in
both Java and VB comes along, it's a tough nut to crack.

"Kamila" wrote:

Hello,
I am sending again my question or problem to you. i am not able to do so.
please help me.
I have one columen witch contain Ids 11 sifer in excel. i have one formula
in javascript to validate id number. i have 3500 id numbers and i can not
incert every number to check it is write or wrong. is there any way to find
out or convert that javascript function into excel and get all wrong and
right ids in next columen?

Right IDs,
01014927367
01015726136

Wrong Ids
15010933773
45290122333

I got this function.

<html
<head
<titlechecking ID number formula </title
</head
<body

<form
<INPUT NAME="ID" SIZE=11 maxlength=11
<INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number"
onClick="(ValidateID(this.form.ID.value))"<br
</form

<script type="text/jscript"

function ValidateID(ID)
{
var TmpStr
var Tmpr
var vekt1 = "376189452"
var vekt2 = "5432765432"
var tot = 0
var base = 11
var value
var vekt
var TmpStr2
var Tmpr2
var vekt2 = "5432765432"
var tot2= 0
var base = 11

// for first number
for (i=0;i<9 ;i++)
{
value = ID.substr(i,1)
vekt = vekt1.substr(i,1)
tot = tot + (parseInt(value) * parseInt(vekt))
}
i=tot/11
i=tot-(parseInt(i)*11)
i=11-i
TmpStr = ID
TmpStr = ID.substr(9,1)
Tmpr=(ID.length)

// for second number
for (y = 0; y < 10 ; y++)
{
value = ID.substr(y,1)
vekt = vekt2.substr(y,1)
tot2 = tot2 + (parseInt(value) * parseInt(vekt))
}
y=tot2/11
y=tot2-(parseInt(y)*11)
y=11-y
TmpStr2 = ID
TmpStr2 = ID.substr(10,1)
Tmpr2=(ID.length)

// integer check

if (base!=Tmpr)
{
document.write("You must enter 11 number for ID (0 - 9) ")
}
else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2)
{
document.write(ID +" " + "is right ID")
}
else
{
document.write("Obs!! You write wrong ID number!")
}

}

</script
</body
</html


best regards,

--
Kamila

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A challenge: referencing cell contents in nested function formula Needs Assistance Excel Worksheet Functions 3 October 28th 06 02:17 AM
Javascript Question jmkratt New Users to Excel 1 August 22nd 06 09:02 AM
Converting a row or column in excel to a Javascript array ncikusa Excel Discussion (Misc queries) 0 April 4th 06 10:04 PM
MATCH FUNCTION?...challenge cecilluen Excel Worksheet Functions 3 June 27th 05 09:06 PM
A COUNTIF function Challenge Mr. G. Excel Worksheet Functions 1 April 19th 05 09:04 PM


All times are GMT +1. The time now is 12:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"