excel - 将行范围分配给变量
问题描述
我一直在努力。我想不通。我正在处理两张纸。一张纸搜索条件“RR”,如果有一个 RR,它为变量分配一个序列,以便在另一张纸中搜索。如果在另一张表中找到序列号,我想确定它所在的行并将其分配给一个变量。"DidTransfer = Sheets(PreviousTabName).Range("B" & thiscell.Row).Value" 当我使用 thiscell.Row 时的问题,它给了我很多问题。我需要行号,以便我可以引用同一行以从同一行的另一个单元格获取信息。请帮忙。
Sub TempModifier()
Dim NYSID, PLookUpTabRange, IsRR, DidTransfer As String
Dim thiscell As Range
'Variable for Temp
Dim TempFirstRow As Integer
Dim TempLastRow As Long
'Variables for the previous
Dim PreviousTabLastRow As Long
Dim PreviousTabFirstRow As Integer
'Initialize the temp variables
TempLastRow = Sheets("Temp").Range("D" & Rows.Count).End(xlUp).Row
PreviousTabName = "February"
PreviousTabFirstRow = 7
With Sheets(PreviousTabName)
PreviousTabLastRow = .Cells(256, "H").End(xlUp).Row 'Get the last row in the data range
End With
'Create a data-range variable
PLookUpTabRange = "H" & PreviousTabFirstRow & ":" & "H" & PreviousTabLastRow
'Begin looping structure to copy data from the temp tab to the current tab
For TempFirstRow = 2 To TempLastRow
'Assign the value of the housing unit
IsRR = Sheets("Temp").Cells(TempFirstRow, 2).Value
'Check if the value is RR
If IsRR = "RR " Then
'If the value is RR, then get the NYSID
NYSID = Worksheets("Temp").Cells(TempFirstRow, 4).Value
If Not IsError(Application.Match(NYSID, Worksheets(PreviousTabName).Range(PLookUpTabRange), 0)) Then
'NYSID is Found on Current Month Sheet, do Nothing
Else
DidTransfer = ""
Set thiscell = Sheets(PreviousTabName).Columns("D").Find(What:=NYSID, LookIn:=xlValues, lookat:=xlWhole)
DidTransfer = Sheets(PreviousTabName).Range("B" & thiscell.Row).Value
Select Case DidTransfer
Case "Transferred"
DidTransfer = "Transferred"
Case Else
DidTransfer = DidTransfer
End Select
If IsError(Application.Match(NYSID, Worksheets(PreviousTabName).Range(PLookUpTabRange), 0)) Or _
(Not IsError(Application.Match(NYSID, Worksheets(PreviousTabName).Range(PLookUpTabRange), 0)) And _
DidTransfer = "Transferred") Then
'Worksheets("Temp").Rows(TempFirstRow).Delete
MsgBox "Delete"
End If
End If
End If
'Go to the next row
Next TempFirstRow
End Sub
解决方案
推荐阅读
- php - Symfony mailer raw content is weird, contains =20 =3D =
- java - 如何在 Vaadin 14 中播放 .mp3 文件
- sql - 如何检索每个对话的最后一条消息?
- c# - 控制台应用程序更新到 .Net Core 3.1 错误找不到框架“Microsoft.AspNetCore.App”,版本“3.1.0”
- r - step_rose() 在调整网格中失败
- python - 为什么从字符串转换为整数,然后再转换回字符串会引发异常?
- arrays - 从数组中提取数组
- java - 将 spring boot 更新到 2.3.5 后无法解析 SingleSignOutFilter.setCasServerUrlPrefix()
- css - 如何向 WooCommerce 单一产品页面添加类
- java - 从距离获取点的坐标