Codings and current changes update to
SummaryRequestsIPsAlls(Merge)_______.xlsm
This is the file I update occasionally, and in doing so try to consolidate daily measurements following a trend or strategies etc.
This helps me update the posts from about here, https://www.excelfox.com/forum/showt...ll=1#post27694
Those are the posts attempting to be monitoring in words what the Bots are up to daily or weekly, or for( If any trend/ strategy period
So far I run a couple of sets of codings to fill the two worksheets, AllIPs, and AllRequests ……..
Worksheet AllIPs code module
Sub CopyTLIPsToAllIPs()
Sub SingleIPList()
Sub SortOf()
Worksheet AllRequests code module
Sub CopyTLReqToAllReq()
Sub SingleReqListDics()
Sub SortOf()
As I have recently been catching up on this file, I have been thinking of things to change, update, or add etc. …..
_a) Monthly files
Although this is approximately half the size currently of the dailys file, ( SummaryRequestsIPsDailys.xlsm ) , it will become much bigger, and is already appearing to have size related problems
So I think I will keep it to about a two month size, centred around each month. So there will be some overlap.( I would prefer to have kept it all on one file. Perhaps later I can try to make a single file again… )
The first is from July 2025, meaning it is from about the start of automated measurements and into August
_b) Partial IPs
The first half of an IP address is called a network part, and seems to be the main bit that might crudely in a Layman way of speaking be called a Bot. I have been looking and manually noting those mostly appearing. Some additional coding / coding changes could help with this.
___b)(i) I am thinking I could develop a coding to maybe list in order. Perhaps in the immediate window or in a another worksheet or both another IP list using just the first half. Perhaps a new worksheet named Stats. This coding I will a few posts down
___b)(ii) A snag to b)(i) is that the list in worksheet AllIPs has something sometimes…._
________........ probably a vbTab https://i.postimg.cc/gX7YJZrK/Probably-a-vb-Tab.jpg
Actually that is not a problem, what is a problem is that I was stupidly doing LookAt:=xlWhole whish is Match entire cell contents whereas I should have been doing LookAt:=xlPart : In the coding , the thing I am looking for is the stripped down number, thus
Code:
Dim Wot As String
Let Wot = Replace(wsAllIPs.Cells(Cnt, NxtClm + 1).Value2, vbTab, "", 1, -1, vbBinaryCompare)
Let Wot = Replace(Wot, vbCr & vbLf, "", 1, -1, vbBinaryCompare)
Let Wot = Trim(Wot)
Set rngFnd = rngSrch.Find(What:=Wot, After:=wsAllIPs.Cells(Cnt + 1, NxtClm + 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
Do While Not rngFnd Is Nothing
Let wsAllIPs.Cells(Cnt, NxtClm) = wsAllIPs.Cells(Cnt, NxtClm) + rngFnd.Offset(0, -1).Value ' Hits
rngFnd.Offset(0, -1).Resize(1, 2).Delete Shift:=xlUp ' Delete duplicate row after adding hits and ip addresses
So I will not catch any numbers in the list with something like a vbTab tacked on it. So I fucked up.
Just to clarify: Run the following coding on a spare worksheet.
It puts this in the first cell, 6 times, 43.156.168.86
The coding may or may not tack on the end a vbTab or vbLf
The VBA .Find in the coding is always trying to find ( 6 times ) just that number, 43.156.168.86, with nothing tacked on.
' When using LookAt:=xlWhole, the first three uses of .Find , it will only find that if only 43.156.168.86 is in the first cell.
' When using LookAt:=xlPart, the next three uses of .Find, it will always find 43.156.168.86regardless of whether w have just that number or that number with anything tacked on
The coding tells us the address if it finds it.
Code:
Sub Finding() ' Run on a spare worksheet
Dim rngFnd As Range, rngSrch As Range
' LookAt:=xlWhole
Let ActiveSheet.Range("A1") = "43.156.168.86"
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Let ActiveSheet.Range("A1") = "43.156.168.86" & vbTab
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Let ActiveSheet.Range("A1") = "43.156.168.86" & vbLf
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Debug.Print
' LookAt:=xlPart
Let ActiveSheet.Range("A1") = "43.156.168.86"
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Let ActiveSheet.Range("A1") = "43.156.168.86" & vbTab
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Let ActiveSheet.Range("A1") = "43.156.168.86" & vbLf
Set rngSrch = ActiveSheet.Range("A1:A10")
Set rngFnd = rngSrch.Find(What:="43.156.168.86", After:=ActiveSheet.Range("A10"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If Not rngFnd Is Nothing Then
Debug.Print rngFnd.Address
Else
Debug.Print "---"
End If
Let ActiveSheet.Range("A1").WrapText = False
End Sub
Here is the 6 results
Code:
$A$1
---
---
$A$1
$A$1
$A$1
So, I probably want to start again, and do both the new coding idea ( ___b)(i) ) and correct the Sub SingleIPList() , and while I am at it, do a Sub SingleIPListDics() , as there is since Xmas 2025 a lot of high view counts to consider.
I will effectively "retire" the SummaryRequestsIPsAlls(Merge)B.xlsm here
Share ‘SummaryRequestsIPsAlls(Merge)B.xlsm’ https://app.box.com/s/nc9nu7ifqbp907oq3ixyl0lcz6ne7go5
