Lookup, match, change cells, then multiply across tabs VBA

[IFianTHENdisplayname]

okay i just started using VBA/macros today and im pretty pleased with my progress so far but hit a brick wall i think above my pay grade...

not looking for a bailout i'd like to understand this for the future. My job could really use these....

Data tab called "Quotation Tool":

trade	product	date	per	currency	cost	MSR	SSR
NW	ocean	dec-12	kg	USD	200	400	450
SW	ocean	dec-12	kg	CNY	2	4	5
SW	ocean	de-12	kg	HKD	4	8	9

<tbody>
</tbody>

then i have a currency conv tab called "Currencies" (refreshed web data):

currency	1USD	inv.1USD
CNY	6.9	1.4
HKD	7.1	1.2

<tbody>
</tbody>

what i want to do: search the column for currency on data tab, find "CNY" or "HKD" or another currency. when it finds it i want to go corresponding "MSR" column cell and multiply that value by the cell in the "currencies" tab corresponding with the right currecy conversion then put result in that cell on the data tab.

I have been working on this for about 5 hours peicing together different codes from threads all over. what i have looks too basic for what i need:

    Sub CurrencyConv()
    '
    ' CurrencyConv Macro
    '
    
    '
        Dim Rng As Range
        On Error Resume Next
        With ActiveSheet.UsedRange
            Set Rng = .Range("L3:L" & .Rows(.Rows.Count).Row)
        End With
        If Range(Rng) = "CNY" Then
            ActiveCell.Offset(0, 3).Select
        End If
        If Rng = "CNY" Then
            ActiveCell.Formula = "=(ActiveCell.Value*Worksheets(Currencies)$C$12)"
        End If
    End Sub

I actually managed to run macros across workbooks using different lookups and replacements but this is stumping me. your thoughts are appreciated!

 

[LateralThinker]

Hi

Try this code:


LateralThinker

 Sub CurrencyConv()
 
 Dim shtCur           As Worksheet
 Dim shtQuoat         As Worksheet
 Dim rngCurr          As Range
 Dim rngCurrRate      As Range
 Dim rngCell          As Range
 Dim dblTargetCurRate As Double
 
 Set shtCur = Worksheets("Currencies")
 Set shtQuoat = Worksheets("Quoatation Tool")
 Set rngCurr = shtQuoat.Range("L2:L" & shtQuoat.Cells(Rows.Count, "L").End(xlUp).Row)
 Set rngCurrRate = shtCur.Range("A2:C" & shtCur.Cells(Rows.Count, "A").End(xlUp).Row)
 Set rngCurrTarget = shtCur.Range("A2")
  
 For Each rngCell In rngCurr
     
     If rngCell.Value <> "USD" Then
        dblTargetCurRate = Application.VLookup(rngCell.Value, rngCurrRate, 3, False)
        rngCell.Offset(0, 3).FormulaR1C1 = "=RC14*" & dblTargetCurRate
     End If
 
 Next rngCell
 
End Sub

 

[IFianTHENdisplayname]

Lateral,

sorry for the delays i am just getting a second to look at this again. i input the code and adjusted as i altered the columns a bit. but when running i got a "mismatch" "13" runtime error for:

dblTargetCurRate = Application.VLookup(rngCell.Value, rngCurrRate, 3, False)

is it because rngCell is not "Set"?

seems to be the onl

 

[IFianTHENdisplayname]

*only range not "set"

thank you!