Hi I need to know how I can do the above.

Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 ,
10.70.10.50 etc

Now What I want to do is using a Macro if posiible add a cell next the the
one containing the IP address which will give me the location that I will
specify it should be .

Reason for this is we get a lot of lists with Ip address and we can only see
where these IP belong by manually searching the Gateways and then typing the
Sites in the next Colums next to the Ip colums .. will make my life easier if
I can run a Macro that can do this for me ...
Any help will be appreciated.

Regards Gerhard

Gerhard,

Easier, I think, would be to make a list of IP addresses and locations, then
use VLOOKUP formulas to extract that data. Your list could be expanded as
needed. Also, it could be based on all or part of the IP.

Something like this to find the entire address:

=VLOOKUP(A2,IPData!A:B,2,False)

Or, based on the first three numbers:
=VLOOKUP(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-1),IPData!A:B,2,False)
First two numbers:
=VLOOKUP(LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1),IPData!A:B,2,False)
First number only:
=VLOOKUP(LEFT(A4,FIND(".",A4)-1),IPData!A:B,2,False)

where your data table is on a sheet named IPData, in columns A and B.

HTH,
Bernie
MS Excel MVP


"Gerhard Kriek" <Gerhard (E-Mail Removed)> wrote in message
news:6C367F72-F930-4564-807F-(E-Mail Removed)...
> Hi I need to know how I can do the above.
>
> Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 ,
> 10.70.10.50 etc
>
> Now What I want to do is using a Macro if posiible add a cell next the the
> one containing the IP address which will give me the location that I will
> specify it should be .
>
> Reason for this is we get a lot of lists with Ip address and we can only
> see
> where these IP belong by manually searching the Gateways and then typing
> the
> Sites in the next Colums next to the Ip colums .. will make my life easier
> if
> I can run a Macro that can do this for me ...
> Any help will be appreciated.
>
> Regards Gerhard

Look at this:
http://www.contextures.com/xlFunctions03.html

This is worth a look too:
http://www.ozgrid.com/VBA/ExcelRanges.htm

Finally, this is very good!
http://www.rondebruin.nl/find.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Bernie Deitrick" wrote:

> Gerhard,
>
> Easier, I think, would be to make a list of IP addresses and locations, then
> use VLOOKUP formulas to extract that data. Your list could be expanded as
> needed. Also, it could be based on all or part of the IP.
>
> Something like this to find the entire address:
>
> =VLOOKUP(A2,IPData!A:B,2,False)
>
> Or, based on the first three numbers:
> =VLOOKUP(LEFT(A2,FIND(".",A2,FIND(".",A2,FIND(".",A2)+1)+1)-1),IPData!A:B,2,False)
> First two numbers:
> =VLOOKUP(LEFT(A3,FIND(".",A3,FIND(".",A3)+1)-1),IPData!A:B,2,False)
> First number only:
> =VLOOKUP(LEFT(A4,FIND(".",A4)-1),IPData!A:B,2,False)
>
> where your data table is on a sheet named IPData, in columns A and B.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Gerhard Kriek" <Gerhard (E-Mail Removed)> wrote in message
> news:6C367F72-F930-4564-807F-(E-Mail Removed)...
> > Hi I need to know how I can do the above.
> >
> > Example I have a list of Ip Address 10.70.60.121 , 10.70.60.125 ,
> > 10.70.10.50 etc
> >
> > Now What I want to do is using a Macro if posiible add a cell next the the
> > one containing the IP address which will give me the location that I will
> > specify it should be .
> >
> > Reason for this is we get a lot of lists with Ip address and we can only
> > see
> > where these IP belong by manually searching the Gateways and then typing
> > the
> > Sites in the next Colums next to the Ip colums .. will make my life easier
> > if
> > I can run a Macro that can do this for me ...
> > Any help will be appreciated.
> >
> > Regards Gerhard
>
>