我目前正在嘗試使用一個開關盒來查看一列數字，例如 (111, 56, 49, 92)，如果該值等于 111，那么我希望輸出顯示“測驗”。
Sub Test()
Dim cd As Range
Dim wsSort As Worksheet
Set wsSort = Workbooks("Test.xlsm").Worksheets(2)
Set cd = wsSort.Columns("I")
Select Case cd
Case Is = 111
wsSort.Range("I10").Offset(0, -2) = "test"
End Select
MsgBox ("Done")
End Sub
這是我到目前為止撰寫的代碼，但它用于單個單元格。所以我有：Set cd = wsSort.Range("I10")，但現在我需要為整個列復制它。Case Is = 111我相信我需要在這里進行修正，但我不是 100% 確定，因為當我運行它時，我在這一行遇到了型別不匹配錯誤。
任何幫助將不勝感激。
更新代碼
Sub Test()
Dim cd As Range
Dim wsSort As Worksheet
Set wsSort = Workbooks("Learner data Elliot.xlsm").Worksheets(2)
Set cd = wsSort.Columns("I")
With wsSort
Select Case LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
Case Is = 111
wsSort.Columns("I").Offset(0, -2) = "test"
End Select
End With
MsgBox ("Done")
End Sub
uj5u.com熱心網友回復：
你需要一個回圈：
With ws
Dim lastRow As Long
lastRow = .Range("I" & .Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = .Range("I10:I" & lastRow)
Dim cell As Range
For Each cell in rng '<--- the loop
Select Case cell.Value
Case 111
cell.Offset(,-2).Value = "Test"
End Select
Next
End With
uj5u.com熱心網友回復：
回圈遍歷列范圍的單元格
- 與之間的想法打
If和End If。 - 選擇您將使用
Set dCell = sCell.EntireRow.Columns(dCol)參考當前目標單元格的行的“點”（三個中的一個）。
Option Explicit
Sub Test()
Const sCol As String = "I"
Const dCol As String = "G"
Const dString As String = "test"
Const fRow As Long = 10
Dim wb As Workbook: Set wb = Workbooks("Learner data Elliot.xlsm")
' Better use the worksheet name, because someone could move the tab.
Dim ws As Worksheet: Set ws = wb.Worksheets(2) ' wb.Worksheets("Sheet2")
Dim lRow As Long: lRow = ws.Range(sCol & ws.Rows.Count).End(xlUp).Row
If lRow < fRow Then Exit Sub ' no data in column
Dim srg As Range: Set srg = ws.Range(sCol & fRow, sCol & lRow)
Dim sCell As Range
Dim sValue As Variant
Dim dCell As Range
For Each sCell In srg.Cells
sValue = sCell.Value
Set dCell = sCell.EntireRow.Columns(dCol)
If IsNumeric(sValue) Then ' it's a number
'Set dCell = sCell.EntireRow.Columns(dCol)
Select Case sValue
Case 111 ' could be more (separated by comma) e.g. 41, 111, 3
'Set dCell = sCell.EntireRow.Columns(dCol)
dCell.Value = dString
' add more cases if different action e.g.
'Case 41
' dCell.Value = "TEST2"
'Case Else
' dCell.Value = "Nope"
End Select
'Else ' it's not a number
' dCell.Value = ""
End If
Next sCell
MsgBox "Done", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345262.html
下一篇：錄制了一個宏以將資料透視表中的（空白）更改為空單元格。運行ExecuteExcel4Macro時出現1004錯誤