Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
In this line:
C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) the Round statement is giving this error: Wrong number of arguments or invalid property assignment Why is this, please? C is dimmed as Range, the other variables As Long. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Hi Stuart
works for me. you may post the entire macro? -- Regards Frank Kabel Frankfurt, Germany "Stuart" schrieb im Newsbeitrag ... In this line: C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) the Round statement is giving this error: Wrong number of arguments or invalid property assignment Why is this, please? C is dimmed as Range, the other variables As Long. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Thanks. Yes, I got it to run once, 2 days ago, and left it
alone. Returned to it today and no go! Confused. Sub TestRound() Dim ws As Worksheet, R As Range, C As Range Dim qty As Long, rate As Long, Data As Long For Each ws In ActiveWorkbook.Worksheets If Not (ws.Name Like "DataStore" Or ws.Name Like _ "*Summary*" Or ws.Name Like _ "*SUMMARY*" Or ws.Name Like "*MASTER*") Then With ws .Unprotect On Error Resume Next Set R = .UsedRange.Columns(4).SpecialCells _ (xlCellTypeConstants, xlTextValues) On Error GoTo 0 If R Is Nothing Then GoTo Line1 For Each C In R If C.Value = "Item" Or C.Value = "item" _ Or C.Value = "ITEM" Then C.Value = "1" End If Next Line1: .Columns(8).NumberFormat = "0.00" .Columns(13).ColumnWidth = 4 'now calculate the VAT On Error Resume Next Set R = .UsedRange.Columns(4).SpecialCells _ (xlCellTypeConstants, xlNumbers) On Error GoTo 0 If R Is Nothing Then GoTo Line5 For Each C In R Data = C.Offset(0, 9).Value If Not IsNumeric(Data) Then Data = Application.InputBox("There is no rate of VAT " & _ "in " & C.Offset(0, 9).Address & " " & vbNewLine & _ " " & ws.Name & vbNewLine & _ " " & ActiveWorkbook.Name & vbNewLine & _ vbNewLine & "Please enter the correct rate of VAT" & _ vbNewLine & "in the form: 17.50 ") C.Offset(0, 9).Value = Data End If qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If Next End If End If End With End If Next End Sub I have extracted the above from the main code sequence. Regards. "Frank Kabel" wrote in message ... Hi Stuart works for me. you may post the entire macro? -- Regards Frank Kabel Frankfurt, Germany "Stuart" schrieb im Newsbeitrag ... In this line: C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) the Round statement is giving this error: Wrong number of arguments or invalid property assignment Why is this, please? C is dimmed as Range, the other variables As Long. Regards. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Hi
difficult to test as I got at least 3 compiler errors: - two End Ifs without an opening If - an undefined label But looking at the lines: If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) Data is defined as long but you make a string comparison with (If Data = "5.00") Also try to set a breakpoint before these lines and look at the values for each variable and check the results -- Regards Frank Kabel Frankfurt, Germany |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Sorry, should have checked that code.
Here's a simpler option: Active sheet has the following values D2 = "1.68" F2 = "393.75" M2 = "5" on it I ran this sub: Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet .Unprotect Set C = .Range("D2") C.Offset(0, 9).Value = Data qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With End Sub Regards. "Frank Kabel" wrote in message ... Hi difficult to test as I got at least 3 compiler errors: - two End Ifs without an opening If - an undefined label But looking at the lines: If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) Data is defined as long but you make a string comparison with (If Data = "5.00") Also try to set a breakpoint before these lines and look at the values for each variable and check the results -- Regards Frank Kabel Frankfurt, Germany --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Hi
changing it a little bit to the following it runs without an error for me Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet '.Unprotect Set C = .Range("D2") Data = C.Offset(0, 9).Value qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = 17.5 Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With MsgBox Round(qty * rate * Data / 100, 2) End Sub -- Regards Frank Kabel Frankfurt, Germany "Stuart" schrieb im Newsbeitrag ... Sorry, should have checked that code. Here's a simpler option: Active sheet has the following values D2 = "1.68" F2 = "393.75" M2 = "5" on it I ran this sub: Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet .Unprotect Set C = .Range("D2") C.Offset(0, 9).Value = Data qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With End Sub Regards. "Frank Kabel" wrote in message ... Hi difficult to test as I got at least 3 compiler errors: - two End Ifs without an opening If - an undefined label But looking at the lines: If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) Data is defined as long but you make a string comparison with (If Data = "5.00") Also try to set a breakpoint before these lines and look at the values for each variable and check the results -- Regards Frank Kabel Frankfurt, Germany --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
Copied and pasted your code into the module and
renamed my old routine. Ran yours ....same problem. Wouldn't even step through. Remmed out the 3 lines containing Round and it stepped through, and set correct values for the variables. Using Excel 2000, Win2k, and totally befuddled (a normal state). Regards. "Frank Kabel" wrote in message ... Hi changing it a little bit to the following it runs without an error for me Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet '.Unprotect Set C = .Range("D2") Data = C.Offset(0, 9).Value qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = 17.5 Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With MsgBox Round(qty * rate * Data / 100, 2) End Sub -- Regards Frank Kabel Frankfurt, Germany "Stuart" schrieb im Newsbeitrag ... Sorry, should have checked that code. Here's a simpler option: Active sheet has the following values D2 = "1.68" F2 = "393.75" M2 = "5" on it I ran this sub: Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet .Unprotect Set C = .Range("D2") C.Offset(0, 9).Value = Data qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With End Sub Regards. "Frank Kabel" wrote in message ... Hi difficult to test as I got at least 3 compiler errors: - two End Ifs without an opening If - an undefined label But looking at the lines: If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) Data is defined as long but you make a string comparison with (If Data = "5.00") Also try to set a breakpoint before these lines and look at the values for each variable and check the results -- Regards Frank Kabel Frankfurt, Germany --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple code error statement
May have found the reason for my error.
I had been running these tests from an addin's module. Created a new workbook, added a module. Populated the 3 required data cells in the new activesheet . Copied your code into this new book's module1. Ran the code .........faultless! Apologies for this dragging on, but can you tell what may be happening, please? Regards. "Stuart" wrote in message ... Copied and pasted your code into the module and renamed my old routine. Ran yours ....same problem. Wouldn't even step through. Remmed out the 3 lines containing Round and it stepped through, and set correct values for the variables. Using Excel 2000, Win2k, and totally befuddled (a normal state). Regards. "Frank Kabel" wrote in message ... Hi changing it a little bit to the following it runs without an error for me Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet '.Unprotect Set C = .Range("D2") Data = C.Offset(0, 9).Value qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = 17.5 Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With MsgBox Round(qty * rate * Data / 100, 2) End Sub -- Regards Frank Kabel Frankfurt, Germany "Stuart" schrieb im Newsbeitrag ... Sorry, should have checked that code. Here's a simpler option: Active sheet has the following values D2 = "1.68" F2 = "393.75" M2 = "5" on it I ran this sub: Sub TestRound2() Dim ws As Worksheet, R As Range, C As Range Dim qty As Single, rate As Single, Data As Single With ActiveSheet .Unprotect Set C = .Range("D2") C.Offset(0, 9).Value = Data qty = C.Value rate = C.Offset(0, 2).Value If Data 0 Then If Data = 5 Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) End If End If End With End Sub Regards. "Frank Kabel" wrote in message ... Hi difficult to test as I got at least 3 compiler errors: - two End Ifs without an opening If - an undefined label But looking at the lines: If Data 0 Then If Data = "5.00" Then C.Offset(0, 4).Value = Round(qty * rate * Data / 100, 2) ElseIf Data = "17.50" Then C.Offset(0, 5).Value = Round((qty * rate * Data) / 100, 2) Data is defined as long but you make a string comparison with (If Data = "5.00") Also try to set a breakpoint before these lines and look at the values for each variable and check the results -- Regards Frank Kabel Frankfurt, Germany --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.601 / Virus Database: 382 - Release Date: 29/02/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with simple if statement | Excel Worksheet Functions | |||
Simple IF statement | Excel Worksheet Functions | |||
Simple IF statement | Excel Worksheet Functions | |||
Simple IF statement | Excel Worksheet Functions | |||
Simple IF statement | Excel Worksheet Functions |