vba-其他程序

最新推荐文章于 2025-07-25 11:16:35 发布

weixin_30311605

最新推荐文章于 2025-07-25 11:16:35 发布

阅读量102

点赞数

CC 4.0 BY-SA版权

文章标签：运维

原文链接：http://www.cnblogs.com/qiaoqiao123321/p/8697830.html

'1、单元格中最后一个为 . 则删除否则不作任何操作
Sub m()

x = Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim s, str
For i = 1 To x

str = Range("B" & i).Value
s = Right(str, 1)

     If s = "." Then 
         Range("B" & i) = Left(Range("B" & i), Len(Range("B" & i)) - 1)  
    End If

Next i

End Sub

'2、如果A3单元格=C3单元格的值则将B3复制到D3

Sub assignment()
For i = 3 To 52
For j = 3 To 52
If (Range("P" & i).Value = Range("U" & j).Value) Then
Range("Y" & j).Value = Range("T" & i).Value 'Range("A" & i).Interior.Color = RGB(255, 0, 0) '背景颜色
Exit For
End If
Next j
Next i
End Sub

'3切换工作表实现不同共组表中数值对比及复制必须写在thisworkbook中才行
Sub fuzhi()

Dim rows As Integer
arry_col = Array(1, 6, 11, 16, 21)

Worksheets("IP").Activate
Worksheets("IP").Select
For i = 3 To 37
    temp_ip = Range("F" & i).Value 'range/cells 表示单元格的不同方法
    Worksheets("freeSS").Activate
    Worksheets("freeSS").Select
    For Each cols In arry_col
        For rows = 3 To 52
            If (temp_ip = Cells(rows, cols).Value) Then
                temp_method = temp_method + CStr(Cells(rows, cols + 3).Value) + ", "
                Exit For
            End If
            Next rows
    Next
 Worksheets("IP").Activate
 Worksheets("IP").Select
 Range("H" & i).Value = temp_method
 temp_method = ""
Next i

End Sub

'逆向dns的ip变为正向由170.12.162.139 变为 139.162.12.170

Sub reverse()
Dim arr, brr, Tmp
Dim i&
arr = Range("A1").CurrentRegion
ReDim brr(1 To UBound(arr), 1 To 4)

For i = 1 To UBound(arr)
    Tmp = VBA.Split(arr(i, 1), ".")
    brr(i, 1) = Tmp(0)
    brr(i, 2) = Tmp(1)
    brr(i, 3) = Tmp(2)
    brr(i, 4) = Tmp(3)
    
    Range("B" & i) = Tmp(3) + "." + Tmp(2) + "." + Tmp(1) + "." + Tmp(0)


Next i

'Range("A1").Resize(UBound(brr), 2) = brr

End Sub

'单元格中是否有No such name 有：清除否则：保留

Sub m()
x = Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim s, str
For i = 1 To x

str = Range("E" & i).Value

If InStr(str, "No such name") Then
Range("E" & i).ClearContents '清除公式/值
End If

Next i
End Sub

'ip整理
Sub assignment() '2、如果A3单元格=C3单元格的值则将B3复制到D3
For i = 1 To 50
For j = 1 To 50

        If (Range("A" & i).Value = ("https://" + Range("E" & j).Value)) Then
            Range("B" & i).Value = Range("F" & j).Value
            Range("C" & i).Value = Range("G" & j).Value
            Exit For
        End If
    Next j
Next i

End Sub

转载于:https://www.cnblogs.com/qiaoqiao123321/p/8697830.html