我有一個具有以下結構的excel檔案
Profile Gene Refseq
Panel1_yes AAA NA123
Panel1_yes BBB NA456
Pane_no CCC NA123
Panel1_yes DDD NA123
我想遍歷 Profile 列，如果值與值串列匹配，則生成一個新列，在新書中連接列 Gene 和 Refseq
Profile New_column
Panel1_yes AAA_NA123
Panel1_yes BBB_NA456
Panel1_yes DDD_NA123
在教程上搜索我找不到如何讓 excel 在值與我的串列元素匹配的行中執行某些操作。
Sub test()
Dim awb As Workbook
Dim ws As Worksheet
Dim a_lastrow As Integer 'last row of column A
Dim b_lastrow As Integer 'last row of column B
Set awb = ThisWorkbook
Set ws = awb.Worksheets("Sheet1")
With ws
a_lastrow = .Range("A100000").End(xlUp).Row
b_lastrow = .Range("B100000").End(xlUp).Row
For r = 1 To a_lastrow
If .Range("A" & r).Value = "My_list" Then
.Range("B" & r).Value = ...
End If
Next r
End With
MsgBox ("done")
End Sub
uj5u.com熱心網友回復：
這是一個設定源作業簿和目標作業簿的示例，并向您展示如何在它們之間傳輸資料。加上以下一些有用的提示：
Option Explicit
Sub testme()
FindValues "Panel1_yes"
End Sub
Sub FindValues(ByVal value As String)
Dim srcWB As Workbook
Dim srcWS As Worksheet
Set srcWB = ThisWorkbook
Set srcWS = srcWB.Sheets("Sheet1")
Dim dstWB As Workbook
Dim dstWS As Worksheet
Set dstWB = ThisWorkbook '--- change to the new workbook
Set dstWS = dstWB.Sheets("Sheet2")
'--- find the end of the data in the destination sheet
Dim dstRow As Long
With dstWS
dstRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
End With
With srcWS
Dim lastRow As Long
lastRow = .Cells(.Cells.Rows.Count, 1).End(xlUp).Row
Dim i As Long
For i = 1 To lastRow
If IsInMyList(.Cells(i, 1).value) Then
dstRow = dstRow 1
dstWS.Cells(dstRow, 1).value = .Cells(i, 1).value
dstWS.Cells(dstRow, 2).value = .Cells(i, 2).value & "_" & .Cells(i, 3).value
End If
Next i
End With
End Sub
Function IsInMyList(ByVal value As String) As Boolean
Dim theList() As String
theList = Split("Panel1,Panel1_yes,Panel2_yes", ",")
Dim item As Variant
For Each item In theList
If item = value Then
IsInMyList = True
Exit Function
End If
Next item
IsInMyList = False
End Function
以下是 VBA 代碼的好習慣：
- 始終使用
Option Explicit - 始終清楚所參考的作業表或范圍
- 使用中間變數（例如
lastRow）來幫助自己使代碼更具可讀性。是的，這是幾行額外的代碼。但在許多情況下，它可以讓你的代碼更快（如果這是一個問題的話），但你會發現從長遠來看，可讀性總是對你有更大的幫助。
祝你好運！
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/417846.html
標籤：