查找，匹配，然后在选项卡上相乘并替换活动单元格 [英] Look up, match, then multiply across tabs and replace active cell

问题描述

好吧，我今天才刚开始使用VBA/macros，到目前为止，我对我的进步感到非常满意:)，但是碰到一堵砖墙，我认为我的薪水等级...

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

更新的光标标签 然后，我有一个货币转换标签，称为货币"(刷新的网络数据):

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

我想做什么:在数据"标签上的货币栏中搜索，找到"CNY"或"HKD"或其他货币.当它找到它时，我要转到相应的"MSR"列单元格，然后将该值乘以与正确的货币兑换对应的货币"选项卡中的单元格，然后将结果放在数据选项卡上的该单元格中.

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.

我已经为此工作了大约5个小时，将来自线程的不同代码集中在一起.我所拥有的东西看起来太基本了，无法满足我的需求:

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 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!

推荐答案

首先，您必须:

• 要么将所有货币"工作表列A的货币名称字符串都更改为其相应的首字母缩写(例如:将人民币"更改为"CNY"，...)

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

或将所有报价工具"工作表L栏的货币缩写更改为相应的名称(例如:将"CNY"更改为人民币"，...)

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

我想前者会更好

然后您可以使用类似以下代码(注释)的代码:

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

这篇关于查找，匹配，然后在选项卡上相乘并替换活动单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！