在A列中查找多个单元格值,并替换为同一行D列中的变量值

时间:2021-11-01 10:04:01

I have searched high and low for something close to this to get me started with the VBA code, but I keep striking out. I'm having a bit of a writers block, and could use any help.

为了让我开始使用VBA代码,我已经搜索了高低不同的东西,但我一直在罢工。我有一些编写器块,可以使用任何帮助。

I'm trying to write a macro that will find multiple specific values in column A and replace the contents of column D in that row with specific values.

我正在尝试编写一个宏,它将在A列中找到多个特定值,并用该特定值替换该行中D列的内容。

Basically it's going to be a price override function for specific SKUs. The SKUs are populated in column A, and the prices are populated in column D. It's only 6 SKUs, but I can't quite figure this one out.

基本上它将成为特定SKU的价格优先功能。 SKU填充在A列中,价格填充在D列中。它只有6个SKU,但我不能完全理解这个。

It has to be a macro because I am manipulating a file that is fetched fresh twice a day. I use a sort of master macro sheet to open this file and then manipulate the data.

它必须是一个宏,因为我正在操作一个每天两次获取的文件。我使用一种主宏表打开此文件,然后操纵数据。

Thanks as always in advance! I appreciate the help!

一如既往地感谢!我很感激帮助!

Edited to add:

编辑添加:

Here's something I tried, but I can't figure out how to do this for multiple values, etc.

这是我尝试过的东西,但我无法弄清楚如何为多个值等做到这一点。

Sub Test()
Cells.Find(What:="26860", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
Range("D1704").Select
ActiveCell.FormulaR1C1 = "63.94"
Range("D1705").Select

End Sub

2 个解决方案

#1


0  

This is a very optimized way of manipulation of data in VBA: In this example I merge the values of A and D and put it in E. You can just change the formula for your application

这是在VBA中处理数据的一种非常优化的方法:在这个例子中,我合并了A和D的值并将其放入E.您可以只更改应用程序的公式

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Integer
Dim cellsArray() As Variant
cellsArray = Range("A1:A20").value
Dim cells2Array() As Variant
cells2Array = Range("D1:D20").value
For i = LBound(cellsArray, 1) To UBound(cellsArray, 1)
   ' Put your code here
   cellsArray(i,1) = cellsArray(i,1) & cells2Array(i,1)
Next i
Range("E1:E20").value = cellsArray

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

#2


0  

Nevermind. I was obviously way too tired to be thinking about writing code because I couldn't remember the most simple solution:

没关系。我显然太累了,无法考虑编写代码,因为我记不起最简单的解决方案:

 For Each Cel In Range("A2:A3000")
   If Cel.Value = "Number" Then Cel.Offset(0, 3).Value = "Othernumber"
 Next               

Yikes. That's embarrassing! =p

让人惊讶。那太尴尬了! = p

#1


0  

This is a very optimized way of manipulation of data in VBA: In this example I merge the values of A and D and put it in E. You can just change the formula for your application

这是在VBA中处理数据的一种非常优化的方法:在这个例子中,我合并了A和D的值并将其放入E.您可以只更改应用程序的公式

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Integer
Dim cellsArray() As Variant
cellsArray = Range("A1:A20").value
Dim cells2Array() As Variant
cells2Array = Range("D1:D20").value
For i = LBound(cellsArray, 1) To UBound(cellsArray, 1)
   ' Put your code here
   cellsArray(i,1) = cellsArray(i,1) & cells2Array(i,1)
Next i
Range("E1:E20").value = cellsArray

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

#2


0  

Nevermind. I was obviously way too tired to be thinking about writing code because I couldn't remember the most simple solution:

没关系。我显然太累了,无法考虑编写代码,因为我记不起最简单的解决方案:

 For Each Cel In Range("A2:A3000")
   If Cel.Value = "Number" Then Cel.Offset(0, 3).Value = "Othernumber"
 Next               

Yikes. That's embarrassing! =p

让人惊讶。那太尴尬了! = p