gpt4 book ai didi

excel - 保存特定单元格的地址以便以后更改它们

转载 作者:行者123 更新时间:2023-12-04 22:28:39 25 4
gpt4 key购买 nike

我正在使用 Excel VBA 尝试解决以下问题:

在 A 列中,我得到了 42 个国家/地区的列表。在 D 列中,我得到了那个国家的巨无霸的美元价格。第 1 行有标题,因此数据从第 2 行开始。我需要构建一个宏,允许用户输入 2 个国家(国家 1 和国家 2),将遍历 A 列以查找用户输入的国家及其对应的国家价格。它应该将国家的单元格位置保存到某个变量中,而价格只是一个数字。如果 Country1 的价格大于国家 2 的价格,则 Country1 的名称应为绿色,Country2 的字体颜色为红色。反之亦然。

现在,我得到的错误是我的变量 TheCell 的“对象变量或未设置 block 变量”。

如果你想测试它,这里是表格的顶部:

Top of the data, with headers

我试着玩弄我把 TheCell 调暗的东西。我试着把它变成一个变种,但这行不通。我很确定 Range 是正确的,因为它是实际的单元格。

Sub CountryComparison()

Dim Counter As Integer
Dim Country1 As String
Dim Country2 As String
Dim TheCell As Range
Dim Price1Cell As Range
Dim Price2Cell As Range
Dim Price1 As Single
Dim Price2 As Single

'The user inputs what countries they want to compare

Country1 = InputBox("Enter Country 1")
Country2 = InputBox("Enter Country 2")

'We are starting at row 2, column 1. Since we're going to check every row, I'm making counter a variable so that I can continuously add 1 to it after every loop.

Counter = 2
TheCell = Cells(Counter, 1)

'Here's my loop. It will select TheCell, and if it contains the name of Country1, then it will save that cell as Price1Cell (to be used later), and save the price of a Big Mac in that country (also to be used later). It does the same thing for Country2. And if neither is a match, it goes on to the next row. Since there are 42 rows, it does this until Counter is greater than 43 (maybe it should be until greater than 42, but that shouldn't matter.)

Do
TheCell.Select
If ActiveCell.Value = Country1 Then
Set Price1Cell = Range(ActiveCell.Address)
Price1 = ActiveCell.Offset(0, 3).Value
End If

If ActiveCell.Value = Country2 Then
Set Price2Cell = Range(ActiveCell.Address)
Price2 = ActiveCell.Offset(0, 3).Value
End If

Counter = Counter + 1

Loop Until Counter > 43

'Here's the final point. If Country1's price is greater than Country2's Price, then Country1 should be colored red and Country2 green. And vice-versa.

If Price1 > Price2 Then
Price1Cell.Font.Color = vbRed
Price2Cell.Font.Color = vbGreen
End If

If Price2 > Price1 Then
Price1Cell.Font.Color = vbGreen
Price2Cell.Font.Color = vbRed
End If


End Sub

最佳答案

除了对象初始化之外,您的代码是有序且基本正确的。
当您与 打交道时对象 ,您必须使用 Set初始化它们,如下所示:

Set TheCell = Cells(Counter, 1)

所以最终的工作代码应该是这样的:
Sub CountryComparison()

Dim Counter As Integer
Dim Country1 As String
Dim Country2 As String
Dim TheCell As Range
Dim Price1Cell As Range
Dim Price2Cell As Range
Dim Price1 As Single
Dim Price2 As Single

'The user inputs what countries they want to compare

Country1 = InputBox("Enter Country 1")
Country2 = InputBox("Enter Country 2")

'We are starting at row 2, column 1. Since we're going to check every row, I'm making counter a variable so that I can continuously add 1 to it after every loop.

Counter = 2
Set TheCell = Cells(Counter, 1)

'Here's my loop. It will select TheCell, and if it contains the name of Country1, then it will save that cell as Price1Cell (to be used later), and save the price of a Big Mac in that country (also to be used later). It does the same thing for Country2. And if neither is a match, it goes on to the next row. Since there are 42 rows, it does this until Counter is greater than 43 (maybe it should be until greater than 42, but that shouldn't matter.)

Do
TheCell.Select
If ActiveCell.Value = Country1 Then
Set Price1Cell = Range(ActiveCell.Address)
Price1 = ActiveCell.Offset(0, 3).Value
End If

If ActiveCell.Value = Country2 Then
Set Price2Cell = Range(ActiveCell.Address)
Price2 = ActiveCell.Offset(0, 3).Value
End If

Counter = Counter + 1

Loop Until Counter > 43

'Here's the final point. If Country1's price is greater than Country2's Price, then Country1 should be colored red and Country2 green. And vice-versa.

If Price1 > Price2 Then
Price1Cell.Font.Color = vbRed
Price2Cell.Font.Color = vbGreen
End If

If Price2 > Price1 Then
Price1Cell.Font.Color = vbGreen
Price2Cell.Font.Color = vbRed
End If


End Sub

我做了一些测试,它有效。

关于excel - 保存特定单元格的地址以便以后更改它们,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55306564/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com