Ping Continuously Multiple IPs From Column - VBA Code?
Apr 15, 2013
I want to create a excel file with VBA code that ping's the IP's from column A, give a response to column B (OK or NOT OK) and in column C gives the last "OK" ping date and time. Something like:
Code:
A B C
194.154.200.10 OK 14/04/2013 13:10
194.154.200.11 NOT OK 14/04/2013 13:00
194.154.200.12 OK 14/04/2013 13:10
The ping process should be a continuous loop.
What I found this far involved text files and I don't want that.
View 3 Replies
ADVERTISEMENT
Aug 21, 2013
So I have an Excel Table that consists of data from a Ping utility (we're having a lot of connection issues on our DSL line), every 5-10 seconds the program pings various websites like google, yahoo, etc. and logs the ping time.
I'd like to make a chart or graph that shows the average ping per Hour (or 30 minutes, or whatever) for all these sites combined, basically take an hour of pings, find the average of all or just the pings from one IP, plot.
Table is
ColA = Date Time (8/20/2013 15:57)
ColB = IP Address (173.252.110.27)
ColC = Ping (123)
ColD = Result (Succeeded/Failed)
ColD = DNS (facebook.com, it's a vlookup from a table of IPs and their DNS address)
View 7 Replies
View Related
Dec 11, 2007
I have a local area network with a couple of hundred computers which share one internet connection. Each user has a CPE (customer premise equipment) with an assigned (by me) IP. This CPE connets to the users equipment via CAT5. My responsibility is to provide internet. Sometimes users call and say their comupter cannot access the internet. I need a quick way to see if the problem lies with my network or if the problem is with the customer's computer. It seems to me that if I could open my spreedsheet with all the network connections and user data and simply ping their IP I would see if the problem is with my equipment or the users.
Column A of my spreedsheet has the actual IP addresses: 192.168.1.1 thru 192.168.1.254 and 10.0.0.1 thru 10.0.0.254 but not all are currently being used. Each row has distinct user account information. I have created a shortcut, named it PING106.bat and listed the target as %windir%system32ping.exe 192.168.1.106 which I can click on and it runs. Next I inserted a hyperlink in A:107 and it does work (it brings up a DOS screen and pings 192.168.1.106 three times then closes the DOS screen)... But there must be a better way. I don't want to create hundreds of shortcuts and insert hyperlinks to specific cells one at a time. It would be nice if I could click on a cell which contains an IP and know if that particular IP is up and reachable on my LAN.
View 4 Replies
View Related
Oct 28, 2009
I would like to “ping” each computer ip address to check if it is online or offline and then write the result in Column C.
There needs to be no limit to how many ip addresses the script can ping.
View 4 Replies
View Related
Oct 18, 2012
I'm trying to work out some code that will take the contents of a cell (either alias or IP name), carry out a ping operation and return the result into the worksheet in the next columns to the right.
I've searched this site and found something similar that returns the result of the ping to a MSGBOX but this isn't quite what I want.
The endgame is to have a For/Next loop for a list of IP addresses in (say) A1:A10 and have the results pasted into columns B, C and so on, with each line of the ping result pasted into subsequent columns.
View 9 Replies
View Related
Mar 21, 2012
the following code has been devised to populate a userform listbox ("listbox1") with seven select columns (F,H,K,L,N,O) of filtered rows of a worksheet ("Data")
Code:
Sub AddMultipleColumn()
Dim rngCell As Range
Dim wks As Worksheet
[Code]...
Regrettably, there is something amuck that is preventing it from providing the expected results. Seems as though this code wishes to process all the rows, instead of just those revealed from the filter. The routine runs indefinately, and will ultimately lock up Excel and never revealing any information. On a random stop, rngCell value far exceeded the (what I felt) to be the preferred max of 28 ... the number of rows of the filter. This routine is called from within the userform's initialization code.
Question 1 ... those that are able to shed light on the flaw ...
Question 2 ... data starts on row 6 of ther database, with rows 1-5 being header. What needs to be done, if anything, to reflect this?
And question 3 ... if the worksheet "Data" were in a second workbook , would be as simple as changing this ... Set wks = workbooks("workbook2.xls").Worksheets("Data")
View 9 Replies
View Related
Feb 13, 2014
The code I have below is relevant to this spreadsheet Copy of Invoice test.xlsm
In the code below I marked the script that has issues in red. The code takes the last row and the last column and works from there. This works fine with single commands, however, when I take a cell (based off the last row and the last column) and try to give that cell multiple commands excel sends back an error. When I use this multiple command (see part of code that is marked in red) for a range that is not based off the last column it works fine.
[Code] .....
View 9 Replies
View Related
Aug 20, 2013
I have a challenge I haven't quite been able to figure out:
I need a 6 digit trace code to be automatically generated for each job based on the date, the product type and the job number.
The first 2 digits on the trace-code are alphanumeric and the sequence for each job is AA, AB, AC etc.
The last 4 digits are a date code (YYWW.... two digits for the year and two digits for the week number).
The criteria is that multiple jobs of the same product type within the same work week must not have identical trace codes. So Job 1 gets "AA" plus the date code... Job 2 gets "AB" plus the date code, etc. If Job 3 is done in the next work week, it cycles back to "AA" and gets the new date-code.
Generating the date-code was simple enough, but coming up with a function or rule to assign the two-digit alpha has been a challenge that I have not been able to beat.
Below is an example of how the Trace-code column should work....note that Trace code on one product type might be identical to the trace code on another product type.
A
B
C
D
1
Date
Product type
Lot number
Trace code
[Code] .........
View 3 Replies
View Related
Mar 18, 2008
I have the following function in a worksheet module:
Private Function NixEmptyColumnsAndSuperfluousRows()
Me. Range("A1:A7").EntireRow.ClearContents
Me.Range("A1:A7").EntireRow.Delete Shift:=xlUp
Me.Range("BE1").EntireColumn.ClearContents
Me.Range("BE1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AT1").EntireColumn.ClearContents
Me.Range("AT1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("AK1").EntireColumn.ClearContents
Me.Range("AK1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("Y1").EntireColumn.ClearContents
Me.Range("Y1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("O1").EntireColumn.ClearContents
Me.Range("O1").EntireColumn.Delete Shift:=xlToLeft
Me.Range("K1").EntireColumn.ClearContents
Me.Range("K1").EntireColumn.Delete Shift:=xlToLeft..............
View 9 Replies
View Related
Jan 3, 2008
Suppose there are two ranges
rngA = range("A1:B5") ' The cells that change
rngB = range("D1:E5") ' The results
Assume that based on an iterative lookup process the values in rngA change. After each iteration the values from rngA are determined and placed into rngB (this i can do). Each time rngA changes i need those values to be added to the previous rngA values (otherwise the values being stored in rngB).
View 4 Replies
View Related
Mar 17, 2014
code to find/replace the letters: "A" with "Active", "P" with "Contract", and "C" with "Settled sale", all in column "Q".
View 2 Replies
View Related
Jul 31, 2009
I'm looking to have my stopwatch run continuously until "and only until" a command button ("Stop") is clicked. I already have a code and stopwatch in place. I have 5 command buttons: start (starts the timer), call (activates/hyperlinks to another sheet where I have more command buttons and data entry needed/), stop (stops the timer), reset (resets the timer), and record (stamps the elapsed time on the sheet1).
They work great BUT the problem is, when the cell is touched (i.e. when you type a character or double click on any cell even if it's on a different sheet, or even on a different workbook) the timer stops!
I'd think that all this would require just a simple string/line inserted in my code. I'm really, really new to VBA and doesn't know how to write my own codes yet. I have to google what I need every time and do trial and error! Just to give you more info-- I'm using this for a call simulation program, in which the user is an operator taking in a call. She would have to be aware of how much time have passed after performing some actions/data entry on the different sheets on the workbook. I hope I've explained it enough. :D Anyway's here's the code I got, which I think needs just a few edits.
View 10 Replies
View Related
Jan 15, 2008
I have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....
Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?
View 6 Replies
View Related
Jul 24, 2014
I have data in excel sheet in the below format:
Existing view.png
How to write a VBA code or Macro to get it in below format:
Required View.png
Timestamp column is the unique key.
View 1 Replies
View Related
Apr 4, 2009
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
View 9 Replies
View Related
Dec 22, 2013
I am currently trying to sum up values every day of the week. (salaries )
That means I have a sheet called Salary where I have the entire year mapped out pr. day. Like you see underneath:
Date
30/12/13 0
31/12/13 0
01/01/130
02/01/130
03/01/130
04/01/130
05/01/130
etc.
In another sheet called Overview, I have another column divided into weeks, like this:
Date
30/12/12 0
06/01/13 0
13/01/13 0
20/01/13 0
27/01/13 0
Now what I need to do is, take the 7 days in a week in the first sheet, and summarize it in the first week in the overview sheet. Now that is simple the first time, =SUM(Salary!C1:C7)
But how do I drag down and automize it so that the next row becomes =SUM(Salary!C8:C14) instead of =SUM(Salary!C2:C8)?
So basically I need to autofill with intervals of 7 (the 7 days in the week) on every row in my Overview sheet.
View 1 Replies
View Related
Jul 13, 2014
I am working on making a spreadsheet that will rack which guests are using which membership for a certain client. Here is what I've got so far. My goal is to have the spreadsheet work like a calendar where the dates are changing daily as well as all the information with it. I was able to make the dates change, but I am unable to have the specific data change with them. Is there a way to set a column of information to a specific date?
View 5 Replies
View Related
Apr 25, 2007
With the MsgBox code, when that part of the VBA is reached, it requires the user to press the OK button for the next part of the VBA to execute. But is it possible when running a macro etc, for a MsgBox to appear right at the start, and remain for the duration of the macro, and then disappear (or an OK button appear) when the macro is finished? As some of the macros we run are long and it would be good to stop people from doing other things whilst it is running.
View 6 Replies
View Related
Mar 7, 2013
I have a large matrix, with categories as columns and entries as rows, that have an "x" or a blank for each category showing if that entry has it or not. Some of these categories will get hidden based on the current user's usage. I want to use a subset of the unhidden columns and hide all the row entries that have ‘x’’s for this subset of columns.
The way I want to go is to create a list of the unhidden columns that match my subset criteria, I was thinking in a Range object. From there, I can use these columns and go row by row checking the rows value at that column to determine if I should hide it or not. This is how I think I should approach this and I'm having a bit of trouble with objects/syntax in VBA. Here is the code/pseudo code I imagine making this possible.
VB:
Dim Subsets As Range
For Each col In Sheets("Test").Range("A3:M3")
row 3 has the option # For Each column that determines If it Is In the subset
If col.EntireColumn.Hidden = False Then
[Code] .....
I am pretty sure everything except the Set Subsets = Union(Subsets, col) line is okay. The questions I have about using the Subsets Range object as this data structure are
Obviously, how can I "build" this object as I go along checking for unhidden columns that include my options? Why wouldn't Set Subsets = Union(Subsets, col.Entirecolumn) work?What's a good way to put ranges into Subsets so that I can easily use it to know which columns to check in each row entry? Can I add an entire column, or should I just reference the column of the cell that's in Subsets?
I've read over Ranges in VBA and am still coming up short with this usage.
View 1 Replies
View Related
Jun 10, 2009
DISCUSSION
I have a spreadsheet that I have been creating for work. It involves a UserForm (FRM_TubingTransfer) and writing the data entered onto sheets (either the ‘MASTER SHEET’ or ‘COMBOBOX DATA’) in the same workbook.
PROBLEM
I have (with your help and much trial and error) gotten the workbook to a reasonably working model. The only major thing left is the ‘AVAILABLE TUBING’ sheet. This sheet is the most complicated, I think, because it isn’t just strictly writing the data.
First, any time there is a NEW PROPERTY / SUPERVISOR entered in the form onto the COMBOBOX DATA screen, the code needs to check the AVAILABLE TUBING sheet and check if that data has already been entered there (This is because there may be some instances where the PROPERTY NAME, and PROPERTY NUMBER are the same but have a different PROPERTY AFE. Aka, there may be two entries with ‘WELL 1’ that have different AFE numbers but they are the same location and need to be treated as such.). Also, if a pipe yard (denoted by “-YARD-“ in the Property number and AFE number column) is entered, do not enter it on the AVAILABLE TUBING SHEET.
Second, when a TUBINGTRANSFER is processed, the program needs to do several things;
For the FROM LOCATION (if a well and not a pipe yard)
Add Buried Joints to the buried cell
Add (E) to the damaged cell
Subtract (A), (B), and (C) from the total on location cell
For the TO LOCATION (if a well and not a pipe yard)
Add (A), (B), and (C) from the total on location cell
View 11 Replies
View Related
Jun 24, 2012
I'm currently doing a new worksheet for work
Cell's A1: Commission Per Sale Cell B1 Running total of commission
I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.
E.g.
Commission $5 $7 $9
Running Total $5 $12 $21
View 2 Replies
View Related
Jun 29, 2006
I’m trying to program a specific label within a userform to continuously display the system time… I’ve tried to code something that loops while the userform.visibile property is true. This obviously just hangs the system… Is there an easier (or any other way) to do this?
View 7 Replies
View Related
Aug 28, 2006
currently i am putting together a vba code to do the following:
1. Cycle through 3 sheets and waiting for 2 seconds on each sheet
2. Refresh after the cycle has finished
3. and then be contiously looped.
4. a button or something to make it stop looping.
This is the code i have got thus far:
Sub SwitchingSheets()
NewHour = Hour(Now())
NewMinute = Minute(Now())
NewSecond = Second(Now()) + 2
WaitTime = TimeSerial(NewHour, NewMinute, NewSecond)
Sheets("Sheet1").Select
View 3 Replies
View Related
Jul 11, 2012
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
View 8 Replies
View Related
Oct 18, 2011
I have data on my excel spread but can not transform them into continuously compounded monthly returns.
View 1 Replies
View Related
Jul 24, 2009
I'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
View 4 Replies
View Related
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
Jun 30, 2014
I have a excelsheet that looks like this:
Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept
Health Services
Internal Services
Public Works
Social Services
Los Angeles
3
12
New York
8
22
100
7
Chicago
15
56
4
View 8 Replies
View Related
May 28, 2014
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
View 7 Replies
View Related
Mar 25, 2008
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
View 7 Replies
View Related
