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 |
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 |