我有一個今天在電子表格上找到的宏，然后我想在列中搜索一個值，如果找到該值，我想轉到最后一個單元格 xlleft 并將兩個值保存在一個陣列中。我有兩個宏，今天要找到一個宏并將其設定為起點，我找到了另一個宏來保存找到的資料。我需要幫助將從 today 宏中找到的值分配到陣列宏中，以下是我擁有的宏，即：日期代碼的地址應該是第二個 marco 從 (n) 中搜索的地址，在我擁有的代碼中（ n) = "I2"。如果可能的話，這可能是一個宏嗎？
Sub FindDate()
Dim R As Range
Dim rng As Range
For Each cell In ActiveSheet.Range("A1:IV1")
if cell.Value = [Today()] Then
Set R = cell
End If
Next
Set rng = Range(R, R.Offset(29, 0)
MsgBox R.Address
End Sub
Sub FindNames()
Dim strNames() As String
Dim n As Integer
n = Range("I2", Range("I2").End(xlDown)).Rows.Count
ReDim strNames(n)
Dim i As Integer
For i = 0 To n
f Range("I2").Offset(i, 0).Value = "HA (F)" Or _
Range("I2").Offset(i, 0).Value = "HA (H)" Then
strNames(i) = Range("I2").Offset(i, -8).Value & " " & _
Range("I2").Offset(i, 0).Value
End If
Next i
MsgBox Join(strNames, vbCrLf)
End Sub
截屏
uj5u.com熱心網友回復：
我重構了您在問題中提供的兩個子項。兩者現在都是回傳值的函式。
此外，我添加了一個具有兩個功能的主子：
- 配置您的需求中可能會在一段時間內發生變化的部分
- 呼叫函式并回傳值
Option Explicit
'rename this according to your business model
Public Sub getNamesForHAForToday()
'Configuration part
Dim arrToLookFor(1) As Variant
arrToLookFor(0) = "HA (F)"
arrToLookFor(1) = "HA (H)"
Dim rgLookForDate As Range
Set rgLookForDate = ActiveSheet.Range("B1:IV1")
Dim rgFirstPartOfName As Range 'choose a more useful name here according to your business model
Set rgFirstPartOfName = ActiveSheet.Range("A:A") 'adjust to your needs
'part where the work is done
Dim rgColToday As Range
Set rgColToday = getTodaysColumn(rgLookForDate)
If rgColToday is nothing then
MsgBox "Todays date not found in " & rgLookForDate.address
exit sub
End If
Dim arrNames As Variant
arrNames = getNamesForHA(rgColToday, arrToLookFor, rgFirstPartOfName)
MsgBox Join(arrNames, vbCrLf)
End Sub
Public Function getTodaysColumn(rgToSearch As Range) As Range
Dim c As Range
For Each c In rgToSearch.Cells
If cDate(c.value) = Date Then
'return the intersection of the currentregion-area with the column of todays cell
Set getTodaysColumn = Intersect(rgToSearch.CurrentRegion, c.EntireColumn)
Exit For
End If
Next
End Function
'rename this function so that it fits to your business logic
Private Function getNamesForHA(rgToSearch As Range, arrLookFor As Variant, _
rgGetFirstPartOfName As Range)
Dim arrNames() As Variant
Dim i As Long
Dim c As Range
For Each c In rgToSearch.Cells
If LenB(c.value) > 0 Then 'only check cells with content
If isInArray(c.value, arrLookFor) Then
ReDim Preserve arrNames(i)
arrNames(i) = rgGetFirstPartOfName.Cells(c.Row).value & " " & c.value
i = i 1
End If
End If
Next
getNamesForHA = arrNames
End Function
Private Function isInArray(value As Variant, arrLookFor As Variant) As Boolean
Dim i As Long
For i = LBound(arrLookFor) To UBound(arrLookFor)
If arrLookFor(i) = value Then
isInArray = True
Exit For
End If
Next
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/313362.html
標籤：擅长