我想找到作業表上“價格標簽”的位置，然后按照該列向下選擇。我寫過
Range(Cells.Find("價格標簽"), Range(Cells.Find("價格標簽")).End(xlDown))
但我收到了 [range method of object _global failed] 訊息。代碼有什么問題，我該如何解決？
uj5u.com熱心網友回復：
使用查找方法
- 如果未找到搜索值，則結果將為
Nothing，因此最安全的方法是使用該Find方法中的范圍變數，然后針對 測驗該變數Nothing。
Option Explicit
Sub Test()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
Dim ColumnRange As Range
Dim fCell As Range ' simplified due to assuming it is never in cell `A1`
Set fCell = ws.Cells.Find("Price Tag", , xlFormulas, xlWhole, xlByRows)
' Decide what to do if found or not.
If fCell Is Nothing Then
MsgBox "'Price Tag' not found.", vbCritical
Exit Sub
Else
Set ColumnRange = ws.Range(fCell, fCell.End(xlDown))
MsgBox "'Price Tag' was found in cell '" & fCell.Address(0, 0) _
& "' and the address of your range is '" _
& ColumnRange.Address(0, 0) & "'.", vbInformation
End If
' But usually you know in which row...
With ws.Rows(1)
Set fCell = .Find("Price Tag", .Cells(.Cells.Count), xlFormulas, xlWhole)
End With
' or in which column it is:
With ws.Columns("A")
Set fCell = .Find("Price Tag", .Cells(.Cells.Count), xlFormulas, xlWhole)
End With
End Sub
uj5u.com熱心網友回復：
此功能將為您擴展選擇。
Public Function DataCells(Source As Range) As Range
Dim ColUsedRange As Range
Dim Col As Range
Dim RowCount As Long
For Each Col In Source.Columns
Set ColUsedRange = Range(Col, Col.EntireColumn.Cells(Source.Parent.Rows.Count).End(xlUp))
If RowCount < ColUsedRange.Rows.Count Then RowCount = ColUsedRange.Rows.Count
Next
Set DataCells = Source.Resize(RowCount)
End Function
用法
Sub Test()
Dim Target As Range
Set Target = Cells.Find("Price tag")
If Not Target Is Nothing Then
Set Target = DataCells(Target)
Application.Goto Target
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/437784.html
上一篇：列中的每個新值都會自動乘以X
下一篇：從日期VBA獲取整數值