blocks|key|652765|text|这似乎是可以用查找函数解决的问题。看看VLOOKUP或HLOOKUP。或者，如果您想学习高级方法，请使用索引(匹配())。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|652766|entityMap|0|LINK|mutability|MUTABLE|url|https://support.office.com/en-us/article/Lookup-and-reference-functions-reference-8aa21a3a-b56a-4055-8257-3ec89df2b23e|1|http://www.randomwok.com/excel/how-to-use-index-match/^0|7|4|0|1G|8|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

This seems like something that could be solved with a <a href="https://support.office.com/en-us/article/Lookup-and-reference-functions-reference-8aa21a3a-b56a-4055-8257-3ec89df2b23e" rel="nofollow">Lookup function</a>. Look at the VLOOKUP or the HLOOKUP. Or, if you want to learn an advanced method, use <a href="http://www.randomwok.com/excel/how-to-use-index-match/" rel="nofollow">INDEX(MATCH())</a>.

blocks|key|2089447|text|无法理解如何使用欺骗值，即DateRow，它基本上是一个匹配函数，它返回我的日期所在的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2089448|Sub+InventoryUpdate()

Set+DateRow+=+Sheets("Inputs").Cells(40,+8)
Set+DateValues+=+Sheets("Inputs").Range("B40:F40")

DateValues.Copy
Sheets("Inventory").Activate
Range("A1").Offset(DateRow+-+1,+30).Select
Selection.PasteSpecial+Paste:=xlPasteValues,+Operation:=xlNone,+SkipBlanks+_
++++++++:=False,+Transpose:=False

End+Sub|code-block|syntax|javascript|2089449|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Couldn't figure out how to work around using a cheat value i.e. DateRow which basically is just a match function that returns me what row my date is in.

<pre><code>Sub InventoryUpdate()

Set DateRow = Sheets("Inputs").Cells(40, 8)
Set DateValues = Sheets("Inputs").Range("B40:F40")

DateValues.Copy
Sheets("Inventory").Activate
Range("A1").Offset(DateRow - 1, 30).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False

End Sub
</code></pre>

blocks|key|2089504|text|詹姆斯，让我把这个也扔给你，给你一些工作上的东西，然后在你需要帮助的时候再来问另一个问题(不想为你弄清楚整个项目)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2089505|短手代码：|2089506|Sub+FindStuff()

Application.ScreenUpdating+=+False

Dim+VariableToLookUp()+as+Variant+'i+used+all+variants+because+i+don't+know+what+data+you+have
Dim+Results()+as+Variant+
Dim+VariableWithValues()+as+Variant
Dim+I,II+as+long

Redim+Results(UBound(VariableToLookUp))

Set+VariableToLookUp+Range(Cell(1,1),Cell(LastRow,1)+
Set+VariableWithValues+Sheet2.Range(Cell(1,1),Cell(LastRow,2)+'this+gets+column2

For+I+=+0+to+UBound(VariableToLookUp)
++++For+II+=+0+to+UBound(VariableWithValues)
++++++++If+VariableToLookup(I,1)+=+VariableWithValues(II,1)+Then
++++++++++++++Results(I)+=+VariableWithValues(II,2)
++++++++++++++exit+for
++++++++endIf
++++next
next

Sheet1.Range("B2").Activate
For+I+=+0+to+Ubound(Results)
+++++ActiveCell.Offset(I,0).Value+=+Results(I)
next

Application.ScreenUpdating+=+True
)|code-block|syntax|javascript|2089507|现在，请记住一些事情，...1，我没有测试这个，因为我希望它能为您提供更多关于下一个问题的指导。我可能有错误的语法，但我承诺正确的想法或足够接近。如果你发布另一个问题，用链接回答这个问题，这样我就能找到它。另外，如果您可以同时设置表或添加逻辑按某种顺序对表进行排序，则可以获得真正复杂的搜索功能以加快它们的速度，我必须检查VBA中90K的3MM%2B数字，因此必须想出一些索引逻辑。|2089508|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

James Let me throw this too you to give you something to work with and then come back with another question when you need help (don't want to figure the whole project out for you) 

short hand code: 

<pre><code>Sub FindStuff()

Application.ScreenUpdating = False

Dim VariableToLookUp() as Variant 'i used all variants because i don't know what data you have
Dim Results() as Variant 
Dim VariableWithValues() as Variant
Dim I,II as long

Redim Results(UBound(VariableToLookUp))

Set VariableToLookUp Range(Cell(1,1),Cell(LastRow,1) 
Set VariableWithValues Sheet2.Range(Cell(1,1),Cell(LastRow,2) 'this gets column2

For I = 0 to UBound(VariableToLookUp)
 For II = 0 to UBound(VariableWithValues)
 If VariableToLookup(I,1) = VariableWithValues(II,1) Then
 Results(I) = VariableWithValues(II,2)
 exit for
 endIf
 next
next

Sheet1.Range("B2").Activate
For I = 0 to Ubound(Results)
 ActiveCell.Offset(I,0).Value = Results(I)
next

Application.ScreenUpdating = True
)
</code></pre>

Now keep in mind a few things ...1 i did not test this since i hope it to be more of guide to get you to the next question. and 2 I may have the wrong syntax but i promise the right ideas or close enough. If you post another question respond to this one with a link so i can find it
.. As a side note, if you can set both to tables or add a logic to sort them in some order you can get real complex search features to speed them up, I have had to check 3MM+ numbers against 90K in VBA so had to come up with some index logic

blocks|key|652810|text|取决于如何和多久更新此源表，将有不同的更好的解决方案。由于您没有说明源数据是如何更新的，所以我认为新数据将出现在不同的表上，这是另一回事。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|652811|您可能根本不需要基于您手头的宏。|652812|命名源数据范围(静态或动态)，例如SourceRange|unordered-list-item|offset|length|style|BOLD|652813|确保它们按升序排序(可以在宏中完成)|652814|然后在另一张表的Col+B中，使用公式
=IFERROR(VLOOKUP(<col+A>,SourceRange,2,FALSE),0)|CODE|652815|为了举例说明，我把所有的内容都放在一张纸上：|652816|📷|atomic|652817|​|652818|而且，由于您没有提到在更新时如何存储历史数据，所以没有做任何事情。|652819|这种方法将根据源数据的更新/检索方式以及如何处理旧数据而改变。|652820|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://developer.qcloudimg.com/http-save/yehe-900000/9ba9cfe7a5a0b383761d01d6839cd3b3.png|imageAlt^0|0|0|H|B|0|0|K|1C|K|1C|0|0|0|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1A|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1B|8|@$G|1C|H|1D|I|J]]|9|@]|A|$]]|$1|K|3|L|5|F|7|1E|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|1F|8|@$G|1G|H|1H|I|J]|$G|1I|H|1J|I|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1K|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1L|8|@]|9|@$G|1M|H|1N|1|1O]]|A|$]]|$1|U|3|V|5|6|7|1P|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1R|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|11|$12|$5|13|14|15|A|$16|17|18|-4]]]]

Depends how and how often this Source sheet is updated, there will be different better solution. Since you didn't say how the source data is updated, I assume it's one off thing where new data will be on different sheet.

You may not need a macro at all based on what you have in hand. 
 - Name the source data range (static or dynamic), say SourceRange 
 - Ensure they are sorted in Ascending order (can be done in macro) 
 - Then in the Col B of the other sheet, use formula 
<code>=IFERROR(VLOOKUP(&lt;col A&gt;,SourceRange,2,FALSE),0)</code>

To illustrate, I have put all in one sheet: 
<a href="https://i.stack.imgur.com/Usmeo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Usmeo.png" alt="illustration"></a>

And since you did not mention how the historical data is stored when updated, nothing has done.

The approach will change based on how the source data is updated/retrieved and what to do with the old data.

I need to update daily volumes calculated from one sheet to a table with historical data. 

For example: In sheet 1 I have a date in 1 cell and a value in the adjacent cell. In another sheet I have several columns with the column A being dates and the rest of the columns filled with data. 

I need a macro that references the date in sheet 1, and pastes the value in sheet 2 in column B that corresponds to the date in sheet 1.

This is the code I'm using now but it's not efficient. 

The date that I'm looking up is in sheet1 cell(1,2) Datematch is basically a numeric value that counts how far down I'll need to offset in sheet2 and DateValues is the value that I'm trying to enter in column B

I want to do this without using the Datematch value 

<pre><code>Sub testing()
Set Datematch = Sheet1.Cells(1, 1)
Set DateValues = Sheet1.Cells(2, 2)
Sheet2.Activate
Range("A1").Offset(Datematch, 1).Value = DateValues
End Sub
</code></pre>

How to reference a cell in VBA that's dependent on a date value

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我需要更新计算出的每日卷从一张表到一张表的历史数据。例如:在工作表1中，我在1单元格中有日期，在相邻单元格中有值。在另一个工作表中，我有几个列，其中A列是日期，其余的列都填充了数据。我需要一个宏，该宏引用表1中的日期，并粘贴B列中表2中的值，该值对应于表1中的日期。这是我现在使用的代码，但效率不高。我正在查找的日期是在...

问如何引用VBA中依赖日期值的单元格
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何引用VBA中依赖日期值的单元格EN