complex macro??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
Back
Top