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

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

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

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

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

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

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

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