VBA 获得circle输入文件

最新推荐文章于 2024-09-24 17:20:24 发布

huyu20122

最新推荐文章于 2024-09-24 17:20:24 发布

阅读量332

点赞数

分类专栏： VBA 文章标签： vba 宏 excel

本文链接：https://blog.csdn.net/huyu20122/article/details/50695719

版权

VBA 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

复制cog文件的前两列放至sheet1，复制整个gff文件放至sheet2，sheet3为cog结果，sheet5为RNA结果。即sheet1、sheet2为输入信息，sheet4为操作界面，sheet3、sheet5为输出信息。VBA宏代码如下（sheet4操作界面下）：

Private Sub CommandButton1_Click()
Dim a, b As Integer
a = TextBox1.Text
b = TextBox2.Text

'RNA
Dim m As Integer
For j = 1 To b
    If Sheet2.Cells(j, 3) = "tRNA" Then
        m = m + 1
    End If
    If Sheet2.Cells(j, 3) = "rRNA" Then
        m = m + 1
    End If
Next
'Sheet5.Cells(1, 5) = m
For i = 1 To m
    Sheet5.Cells(i, 1) = "genome"
Next

Dim n As Integer
n = 1
For j = b - m + 1 To b
        Sheet5.Cells(n, 2) = Sheet2.Cells(j, 3)
        Sheet5.Cells(n, 3) = Sheet2.Cells(j, 4)
        Sheet5.Cells(n, 4) = Sheet2.Cells(j, 5)
        n = n + 1
Next

'cog
For i = 1 To b
    If Sheet2.Cells(i, 3) <> "gene" Then
        Sheet2.Rows(i).Delete
    End If
    If Sheet2.Cells(i, 7) = "-" Then
        Sheet2.Cells(i, 6) = Sheet2.Cells(i, 5)
        Sheet2.Cells(i, 5) = Sheet2.Cells(i, 4)
        Sheet2.Cells(i, 4) = Sheet2.Cells(i, 6)
    End If
Next
Sheet2.Columns(8).Delete
Sheet2.Columns(7).Delete
Sheet2.Columns(6).Delete
Sheet2.Columns(2).Delete
Sheet2.Columns(1).Delete

For i = 1 To a
    Sheet3.Cells(i, 1) = Sheet1.Cells(i, 1)
    Sheet3.Cells(i, 4) = Sheet1.Cells(i, 2)
Next

For j = 1 To b
    Sheet2.Cells(j, 4).Replace What:="locus_tag=", Replacement:=""
Next

'For j = 1 To b
'    If Len(Sheet2.Cells(j, 4)) = 12 Then
'        Sheet2.Cells(j, 4) = Left(Sheet2.Cells(j, 4), 11) + "0000" + Right(Sheet2.Cells(j, 4), 1)
'    End If
'    If Len(Sheet2.Cells(j, 4)) = 13 Then
'        Sheet2.Cells(j, 4) = Left(Sheet2.Cells(j, 4), 11) + "000" + Right(Sheet2.Cells(j, 4), 2)
'    End If
'    If Len(Sheet2.Cells(j, 4)) = 14 Then
'        Sheet2.Cells(j, 4) = Left(Sheet2.Cells(j, 4), 11) + "00" + Right(Sheet2.Cells(j, 4), 3)
'    End If
'    If Len(Sheet2.Cells(j, 4)) = 15 Then
'        Sheet2.Cells(j, 4) = Left(Sheet2.Cells(j, 4), 11) + "0" + Right(Sheet2.Cells(j, 4), 4)
'    End If
'Next

For i = 1 To a
    For j = 1 To b
        If Sheet3.Cells(i, 1) = Sheet2.Cells(j, 4) Then
            Sheet3.Cells(i, 2) = Sheet2.Cells(j, 2)
            Sheet3.Cells(i, 3) = Sheet2.Cells(j, 3)
        End If
    Next
Next

'For j = 1 To b
'    Sheet4.Cells(j, 12) = Right(Sheet2.Cells(j, 4), 15)
'Next

For i = 1 To a
    If Sheet3.Cells(i, 4) = "" Then
        Sheet3.Cells(i, 4) = 0
    End If
Next
End Sub