Simple code error statement

[Stuart]

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.

 

[Frank Kabel]

Hi Stuart
works for me. you may post the entire macro?

 

[Stuart]

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]

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

 

[Stuart]

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]

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

 

[Stuart]

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.

 

[Stuart]

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.