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;(@)" )
Printable View
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;(@)" )
there is no error in excel
what is the acceptable input for myscreen.area?
to test your errors, try like
this will allow you to find which part is giving the errorvb Code:
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
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
i can understand that, you need to stop it from processing K if it is empty
yes please help me rows has either j or k
the above code still has error.
asheet.Cells(i, "J").Value <> "0" returns true for empty cell
try
val(asheet.Cells(i, "J").Value) <> "0"
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
@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.Quote:
Originally Posted by westconn1
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:
Code:.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.
anhn
thanks for your explanation. val (aSheet.Cells(i, "J").Value) <> ""
what is the difference <>0 and <> ""
you can test if a cell is empty using Isempty(celladdress)
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 valueQuote:
@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 = 0Quote:
@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.
"" = 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
If aSheet.Cells(8,"e") <> "CAD" AND val( aSheet.Cells(i, "J").Value) <> "0" Then
it says mismatch
i don''t know why, it tests ok for me