Network IP,Gateway, VLAN Automatic assign macro

[excel2425]

So I have a bit of a challenge to try to automate a tedious job of assigning IP addresses, Gateways etc in a static network from a standard scheme. I make the Ip scheme for the general switch devices are attached and branch down and assign IP's to each device(camera, panel, etc) . So I'd like to search row 1 of "IP Scheme" for a word in cells of column E on "Network List" AND directly match the cell in I of "Network IP List" then take the value in cell below the matching cells that match and in the row of the direct match. Each cell from J:M will have their own formulas and I'll drag this down for each device.


Example:I want the IP of the C-C-02
So search and make sure that a value in E2 of Network IP List matches a value in row 1 of IP Scheme i.e. E2 has Network Camera and IP Scheme row 1 has camera starting IP so I'm in cell Z1 of IP Scheme now. I'll always want the ____Starting IP column.
Now make sure that I2 of Network IP matches a cell in column A and follow that row and find the cell that's below the Network Camera and Cameras Starting IP matchup. So now I display the value in that cell.
The tricky part is that no two cells in column J can have the same IP so I'd like to add 1 to the ending of each value based on the previously matched device.

I know this might be a little tricky and if its easier to split it up into smaller pieces to make sure each part of the formula is correct I'd love to help. Maybe someone has an overall design idea on how to tackle this? This list is often hundreds of lines long and very tedious to type and search to make sure each is unique and follows the scheme properly. The same idea will be applied for "Subnet Mask", "Default Gateway", and "VLAN ID" cells.

[excel2425]

My apologies on the thread title. It should not be phrased as a macro but instead just a formula. I dont know how to change the title.

[bulina2k]

see the attachement:

[excel2425]

Thank you so much for your help, it looks great!

There were a few items I'd like tp improve on although to make this more robust and able to tackle different formats from user to user.
-Can I make it so that multiple descriptions work for the same scheme? i.e. when I have intercom desk OR intercom wall work for the same scheme?
-Also, if there is a scheme that starts later in the list and begins with a later VLAN but "earlier" IP address can I avoid the No Hosts available? So say on line 1 I have Network camera, first floor and on the 4th line I have an intercom first floor....I get no hosts available? Does this work on the fact that IP addresses progress forward in numerical order and cannot work in reverse?

Thanks again so much for this VBA. Also, how did you learn VBA? I'd love to take it up in my spare time because of how useful it can be in automating and revising code? I'd love it if I can be the one on the other end one day giving help and blasting through something easy.

[bulina2k]

Hi there!

For learning sources I recommend the sticky thread on the excel programming forum. You'll find many good learning materials in there.
As for the file, I did modified it to verify if the previous IP address is in fact in the network of the assigned ip (to be). Also if you want the freedom to write more in the description column, be sure to complete the Device Type column with one of the Category items.

Pls test it and tell me what you think.

[excel2425]

I'm getting run-time error 91...Do you know what could be causing this? Also, in order to change to my actual situation with different "Location", and "Switch" do I need to modify the code? I want to make this as robust as possible i.e. change the everything except items in IP Scheme Row 2 and Row 1 of Network IP List.

[excel2425]

Does anyone know how to start this VBA at say row 5 if I want to add a few rows that wont need IPs? I've tried manipulating the VBA to say J5:J100 but I keep getting an error after the frist 2 rows fill in.

[excel2425]

I need to add to this and wondering if someone might be able to help me. I'm looking to for the formula to search for something matching from column E on Network IP List to the IP scheme. Example: I enter "Network Camera" in a cell in E but have "Network Camera, Sensor" in row 1 on IP Scheme in which I'd like it to use the proper scheme. Another is if I leave a field blank in column E on Network IP List I'd like it to skip over. I'm getting error "Run-time error '91'." Also, is it possible if the formula cannot match column E device to row 1(IP Scheme) to have the fields in columns I, J, K, L, M, N to be filled with "TBD"?

Any tips would be a huge help! Thanks

[excel2425]

Bueler...?