complex macro??

E

Eqa

I think I need a macro but maybe someone has a better idea.
I want col P to recognise the entry in col O which is one of 8 currrency
symbols and to insert the symbol into P so the a numeric value can be added
to the symbol.Col Q recognise col O and insert one of the 8 x change rates
from a drop down list.
col T to look at col I and col C and insert a paticular duty rate from which
I need to set up.and depending on the type of product it is (col I) and the
country of orgin col C insert the relevent duty rate.

Hope this all makes sense and thanks

Eqa
 
I

iliace

For the number format, put this in the worksheet where you're entering
currency:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Excel.Range
Dim str As String
Dim currSymbol() As String
Dim i As Integer

On Error Resume Next
Set Target = Intersect(Target, Me.Range("O:O"))
On Error GoTo 0

If (Target Is Nothing) Or _
(Target.Address = Target.EntireColumn.Address) Then Exit Sub

ReDim currSymbol(Target.Rows.Count)

i = LBound(currSymbol)
For Each rng In Target
currSymbol(i) = rng.Value
i = i + 1
Next rng

Set Target = Target.Offset(0, 1)

i = LBound(currSymbol)
For Each rng In Target
str = ""
str = str & "_(" & Chr(34) & currSymbol(i) & _
Chr(34) & "* #,##0.00_);"
str = str & "_(" & Chr(34) & currSymbol(i) & _
Chr(34) & "* (#,##0.00);"
str = str & "_(" & Chr(34) & currSymbol(i) & _
Chr(34) & "* " & Chr(34) & "-" & Chr(34) & "??_);_(@_)"
rng.NumberFormat = str
i = i + 1
Next rng
End Sub


You would use a lookup function to get the relevant duty rate. Set up
a separate duty rates table, and address it in your formula. If you
provide more specifics as to how the duty rate is calculated, we can
figure out that part too.
 
I

iliace

Replace this:
If (Target Is Nothing) Or _
(Target.Address = Target.EntireColumn.Address) Then Exit Sub
Click to expand...

with this:

If (Target Is Nothing) Then Exit Sub
If (Target.Address = Target.EntireColumn.Address) Then Exit Sub
 
E

Eqa

iliace,

Thanks for that but I think I have gone in over my head here. Could I
possiblly email the spread sheet to your gmail address and you can see
exactly what all the different currencies and x-change rates are and help me
insert these into the spread sheet. Also different countries have different
duty levels.

Thanks,

Eqa
 
Top