illgal funciton call

[india123]

the following code has illegal function error when i tried 1,501,800.00
could you help me what is the error in it?


MyScreen.Area(11,14,11,33).Value = Format(aSheet.Range("K" & i).Value,"* ##,##0.00;* #,##0.00;(@)" )

[westconn1]

there is no error in excel
what is the acceptable input for myscreen.area?

to test your errors, try like

dim mystr as string  ' return from format is a string
mystr = Format(aSheet.Range("K" & i).Value,"* ##,##0.00;* #,##0.00;(@)" )
MyScreen.Area(11,14,11,33).Value = mystr

this will allow you to find which part is giving the error

[india123]

actaully i tried the error code the same thing about your code it copies in amount column "-" and it says illegal function
i am trying to do that if celkl k has value or j has value the following code
if k has value it works fine. if j has value k has nothing it gives error.
the below code:

if aSheet.Cells(8,"e") <> "CAD" AND IsNumeric(aSheet.Cells(i, "J").Value) And aSheet.Cells(i, "J").Value <> "0" Then

MyScreen.Area(11,14,11,33).Value = Trim(aSheet.Cells(i,"J").text)
MyScreen.Area(17,14,17,33).Value = Trim(aSheet.Cells((i+1), "J").text)
MyScreen.Area(11,14,11,33).Value = Format(aSheet.Range("J" & i).Value,"* #,##0.00" )
MyScreen.Area(11,41,11,44).value = Trim(aSheet.Cells(8, "e"))
MyScreen.Area(11,45,11,46).value = "S"
MyScreen.Area(17,14,17,33).Value = Format(aSheet.Range("J" & i+1).Value,"* #,##0.00" )
MyScreen.Area(17,41,17,44).value = Trim(aSheet.Cells(8, "e"))
MyScreen.Area(17,45,17,46).value = "S"

elseiF aSheet.Cells(8, "e") <> "CAD" AND IsNumeric(aSheet.Cells(i, "K").Value) And aSheet.Cells(i, "K").Value <> "0" Then
MyScreen.Area(11,14,11,33).Value = Trim(aSheet.Cells(i, "K").value)
MyScreen.Area(17,14,17,33).Value = Trim(aSheet.Cells((i+1), "k").text)
MyScreen.Area(11,14,11,33).Value = Format(aSheet.Range("K" & i).Value,"* #,##0.00" )
MyScreen.Area(11,41,11,44).value = Trim(aSheet.Cells(8, "e"))
MyScreen.Area(11,45,11,46).value = "S"
MyScreen.Area(17,14,17,33).Value = Format(aSheet.Range("K" & i+1).Value, "* #,##0.00" )
MyScreen.Area(17,41,17,44).value = Trim(aSheet.Cells(8, "e"))
MyScreen.Area(17,45,17,46).value = "S"
End if

[westconn1]

i can understand that, you need to stop it from processing K if it is empty

[india123]

yes please help me rows has either j or k
the above code still has error.

[westconn1]

asheet.Cells(i, "J").Value <> "0" returns true for empty cell
try
val(asheet.Cells(i, "J").Value) <> "0"

[india123]

westconn
shall i try like this
if aSheet.Cells(8,"e") <> "CAD" AND IsNumeric(aSheet.Cells(i, "J").Value) And val(aSheet.Cells(i, "J").Value <> "0" AND aSheet.Cells(i, "J").Value <> "" Then
Please let me know

[anhn]

asheet.Cells(i, "J").Value <> "0" returns true for empty cell
try
val(asheet.Cells(i, "J").Value) <> "0"

@pete, val(asheet.Cells(i, "J").Value) <> "0" will be always True, because the LHS return a number so it will never equal to a string.

It will be better if use: Val(aSheet.Cells(i, "J").Value) <> 0
but it is also True if the cell value is "123ABC".
IsNumeric() won't help in cases "123D4" or "123E0" or "0D1" : these are numbers in Sci format.

To read from a cell, there are 4 different ways:

.Range("A1")         '-- same as .Range("A1").Value : default property
.Range("A1").Value   '-- return actual value of the cell, may be number or text
.Range("A1").Text    '-- return the text that is currently displayed in the cell
.Range("A1").Formula '-- return the formula text in the cell, 
                     '   if no formula exist then return Value as above.

[india123]

anhn
thanks for your explanation. val (aSheet.Cells(i, "J").Value) <> ""
what is the difference <>0 and <> ""

[westconn1]

you can test if a cell is empty using Isempty(celladdress)

@pete, val(asheet.Cells(i, "J").Value) <> "0" will be always True, because the LHS return a number so it will never equal to a string.

it was actually returning false with empty cell, i had seen it was a string, and have never had a problem comparing a value with a numerically valid string, but i do agree much better to use a value

@pete, val(asheet.Cells(i, "J").Value) <> "0" will be always True, because the LHS return a number so it will never equal to a string.

0 = 0
"" = empty sting, or vbnullstring
so 0 is not an empty string
return values can often be implicitly converted to number or a string, to match what it is being compared to, if it would be a valid conversion

[india123]

If aSheet.Cells(8,"e") <> "CAD" AND val( aSheet.Cells(i, "J").Value) <> "0" Then
it says mismatch

[westconn1]

i don''t know why, it tests ok for me