0

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":

data tab

UPDATED CURRN TAB Then I have a currency conv tab called "Currencies" (refreshed web data):

currencies tab

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:

UPDATED CODE FROM USER

Sub CurrencyConvTwo()

Dim cell As Range, currRng As Range, currCell As Range
With Worksheets("Currencies") '<--| reference "Currencies" sheet
    Set currRng = .Range("A3", .Cells(.Rows.Count, 1).End(xlUp)) '<--| set the range with all currencies acronyms
End With

With Worksheets("Quotation Tool") '<--| reference "Quotation Tool" sheet
    For Each cell In .Range("L3", .Cells(.Rows.Count, "L").End(xlUp)) '<--| loop through its column L ("Currency") cells from row 3 down to last not empty one
        Set currCell = currRng.Find(what:=cell.Value, LookIn:=xlValues, lookat:=xlWhole) '<--| try finding current currency in "Currencies" sheet currencies range
        If Not currCell Is Nothing Then cell.Offset(, 3) = cell.Offset(, 3) * currCell.Offset(, 3) '<--| if found, substitute current cell three columns offset to its current value times "Currencies" sheet found currency cell 2 columns offset
    Next cell
End With

End Sub

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

Community
  • 1
  • 1
IFianTHENdisplayname
  • 1
  • 2
  • CNY is the only currency i have in there becasue i figured i would just copy paste and change currencies when i figured out the right coding – IFianTHENdisplayname Dec 19 '16 at 00:19
  • 2
    You can always edit your question. You can record steps and that will show you the commands the VBA commands that Excel uses. Alt T, M, R. Repeat to stop recording. Look in the VBA editor for the recorded code. –  Dec 19 '16 at 00:22
  • Also, it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Also, you need to quality the `Rng` with `Set Rng = .Range(...` ...also I *think* you could do this with either `Vlookup` or `Index/Match`. Do you need a VBA solution? – BruceWayne Dec 19 '16 at 00:37
  • i mean VBA would seem better to me but if you have a different solution i am all ears. trying to make one master workbook for my sales meetings. so i have data pulling from all over the place so i figured having it in VBA would give it more functionality and replicability. but what do i know? :) and thanks i did go back and edit the post. – IFianTHENdisplayname Dec 19 '16 at 01:04
  • 1
    If you could post your screenshots as actual table data, so we can copy/paste (instead of having to recreate in Excel), it'd be appreciated. – BruceWayne Dec 19 '16 at 02:29
  • as said before: try with the recorder to find out how the syntax is, but then follow BruceWayne's input and make sure you get rid of .active/.select and also try to avoid working with active objects. Instead and also especially for you problem, try to set workbooks and worksheets in variables and address them correctly. For example, if you use With [range] then also put a dot to reference before the usage of Range (.Range). If you don't address and reference clearly, you run into unwanted surprises while working across different books/sheets – Bernd Stoeckel Dec 19 '16 at 09:58

1 Answers1

0

First off, you have to:

  • either change all "Currencies" sheet column A currencies names strings to their corresponding acronyms (e.g.: change "Chinese Yuan Renmimbi" to "CNY", ...)

  • or change all "Quotation Tool" sheet column L currency acronyms to their corresponding names (e.g.: change "CNY" to "Chinese Yuan Renmimbi" , ...)

I guess the former would be preferable

And then you can use a code like the following (commented) one:

Option Explicit

Sub CurrencyConv()
    Dim cell As Range, currRng As Range, currCell As Range

    With Worksheets("Currencies") '<--| reference "Currencies" sheet
        Set currRng = .Range("A3", .Cells(.Rows.count, 1).End(xlUp)) '<--| set the range with all currencies acronyms
    End With

    With Worksheets("Quotation Tool") '<--| reference "Quotation Tool" sheet
        For Each cell In .Range("L3", .Cells(.Rows.count, "L").End(xlUp)) '<--| loop through its column L ("Currency") cells from row 3 down to last not empty one
            Set currCell = currRng.Find(what:=cell.Value, LookIn:=xlValues, lookat:=xlWhole) '<--| try finding current currency in "Currencies" sheet currencies range
            If Not currCell Is Nothing Then cell.Offset(, 3) = cell.Offset(, 3) * currCell.Offset(, 2) '<--| if found, substitute current cell three columns offset to its current value times "Currencies" sheet found currency cell 2 columns offset
        Next cell
    End With
End Sub
user3598756
  • 28,893
  • 4
  • 18
  • 28
  • thanks for the thought! i did as you said and set column A and a index match to a list of abbreviations i found. when i use your code, i dont get an error but also nothing happens. this is just me thinking here and i don't know much about this, but when it looks for a match in "curencies" for abbrev. does it matter that the cell is actually a formula and the abbrev is only displayed? – IFianTHENdisplayname Dec 24 '16 at 15:50
  • here is currency abbrev. code: =INDEX(J:J,MATCH(B3,I:I,0)) – IFianTHENdisplayname Dec 24 '16 at 15:51
  • I'm not sure to get you: update your screenshots to your actual data structure – user3598756 Dec 24 '16 at 16:16
  • @IFianTHENdisplayname - If cell L12 on your Currencies sheet is showing as #N/A, that means you don't have the string "Chinese Yuan Renminbi" anywhere in column I. Because of the #N/A, the code is not able to `Find` the string "CNY" in column A of the Currencies sheet, and therefore cell O3 of the Quotations sheet is not being updated. (And cells O4 and O5 aren't being updated because they are already in USD anyway.) – YowE3K Dec 28 '16 at 18:59
  • hahaha wow i totally went right by that. i just happened to be looking at only Yuan and when it wasnt changing i figured it all wasnt changing. i figured it out now thanks!! works great. – IFianTHENdisplayname Dec 28 '16 at 20:59