From: Ron on 31 Mar 2010 15:30 3 columns: A B C 0420001234 100.100.1.210 0000000004D2 I will have a list in column A, numerical...0420001234,0420001235, etc Column C will have a MAC and the last 2 segments are based on the last 5 of column A. e.g. 01234 = 04D2 (first 4 segments are fixed) Column B is decimal of last segment of column C, e.g. D2 = 210 I would like to scroll down column A, and fill in column B anc C, which I am doing manual right now. Way beyond me.....Any ideas? Thanks, Ron
From: Ron on 31 Mar 2010 15:33 Oh yeah...if it could go down a certain number of rows, which I would tell it, or until it hit a filled cell in Column B or C...that would help. Thanks, wizards "Ron" wrote: > 3 columns: > A B C > 0420001234 100.100.1.210 0000000004D2 > > I will have a list in column A, numerical...0420001234,0420001235, etc > Column C will have a MAC and the last 2 segments are based on the last 5 of > column A. e.g. 01234 = 04D2 (first 4 segments are fixed) > Column B is decimal of last segment of column C, e.g. D2 = 210 > > I would like to scroll down column A, and fill in column B anc C, which I am > doing manual right now. > > Way beyond me.....Any ideas? > > Thanks, > Ron
From: OssieMac on 31 Mar 2010 15:51 Hi Ron, Assuming that the first value is in A2 insert the following formulas. In C2 enter ="00000000"&LEFT(DEC2HEX(RIGHT(A2,5),4),2)&""&RIGHT(DEC2HEX(RIGHT(A2,5),4),2) In B2 enter ="100.100.1."&HEX2DEC(RIGHT(C2,2)) Copy the formulas down the columns.  Regards, OssieMac "Ron" wrote: > Oh yeah...if it could go down a certain number of rows, which I would tell > it, or until it hit a filled cell in Column B or C...that would help. > > Thanks, wizards > > "Ron" wrote: > > > 3 columns: > > A B C > > 0420001234 100.100.1.210 0000000004D2 > > > > I will have a list in column A, numerical...0420001234,0420001235, etc > > Column C will have a MAC and the last 2 segments are based on the last 5 of > > column A. e.g. 01234 = 04D2 (first 4 segments are fixed) > > Column B is decimal of last segment of column C, e.g. D2 = 210 > > > > I would like to scroll down column A, and fill in column B anc C, which I am > > doing manual right now. > > > > Way beyond me.....Any ideas? > > > > Thanks, > > Ron
From: Rick Rothstein on 31 Mar 2010 15:58 You didn't tell us how the front part of the values in Columns B and C are specified, so I hardcoded what you showed us into the following macro (you will have to adjust this on your own or tell us how this information is derived so we can show you how to do it). Also your specification on which cells to operate on was kind of sketchy, so I took the easy way out and simply made the code operate on each cell in the current selection... Sub HexSplitter() Dim Cell As Range, HexNum As String For Each Cell In Selection HexNum = Right("0000" & Hex(Right(Cell.Value, 5)), 4) Cell.Offset(0, 1).Value = "100.100.1." & CLng("&H" & Right(HexNum, 2)) Cell.Offset(0, 2).Value = "00000000" & Format(HexNum, "@@@@") Next End Sub  Rick (MVP  Excel) "Ron" <Ron(a)discussions.microsoft.com> wrote in message news:C1C27371BA574601A99EFC73DDA0F7FF(a)microsoft.com... > Oh yeah...if it could go down a certain number of rows, which I would tell > it, or until it hit a filled cell in Column B or C...that would help. > > Thanks, wizards > > "Ron" wrote: > >> 3 columns: >> A B C >> 0420001234 100.100.1.210 0000000004D2 >> >> I will have a list in column A, numerical...0420001234,0420001235, >> etc >> Column C will have a MAC and the last 2 segments are based on the last 5 >> of >> column A. e.g. 01234 = 04D2 (first 4 segments are fixed) >> Column B is decimal of last segment of column C, e.g. D2 = 210 >> >> I would like to scroll down column A, and fill in column B anc C, which I >> am >> doing manual right now. >> >> Way beyond me.....Any ideas? >> >> Thanks, >> Ron
From: Ron on 7 Apr 2010 16:06 Sorry guys, I've been away...thanks for the responses, I'll see what works and let you know. "Rick Rothstein" wrote: > You didn't tell us how the front part of the values in Columns B and C are > specified, so I hardcoded what you showed us into the following macro (you > will have to adjust this on your own or tell us how this information is > derived so we can show you how to do it). Also your specification on which > cells to operate on was kind of sketchy, so I took the easy way out and > simply made the code operate on each cell in the current selection... > > Sub HexSplitter() > Dim Cell As Range, HexNum As String > For Each Cell In Selection > HexNum = Right("0000" & Hex(Right(Cell.Value, 5)), 4) > Cell.Offset(0, 1).Value = "100.100.1." & CLng("&H" & Right(HexNum, 2)) > Cell.Offset(0, 2).Value = "00000000" & Format(HexNum, "@@@@") > Next > End Sub > >  > Rick (MVP  Excel) > > > > "Ron" <Ron(a)discussions.microsoft.com> wrote in message > news:C1C27371BA574601A99EFC73DDA0F7FF(a)microsoft.com... > > Oh yeah...if it could go down a certain number of rows, which I would tell > > it, or until it hit a filled cell in Column B or C...that would help. > > > > Thanks, wizards > > > > "Ron" wrote: > > > >> 3 columns: > >> A B C > >> 0420001234 100.100.1.210 0000000004D2 > >> > >> I will have a list in column A, numerical...0420001234,0420001235, > >> etc > >> Column C will have a MAC and the last 2 segments are based on the last 5 > >> of > >> column A. e.g. 01234 = 04D2 (first 4 segments are fixed) > >> Column B is decimal of last segment of column C, e.g. D2 = 210 > >> > >> I would like to scroll down column A, and fill in column B anc C, which I > >> am > >> doing manual right now. > >> > >> Way beyond me.....Any ideas? > >> > >> Thanks, > >> Ron > > . >

Next

Last
Pages: 1 2 Prev: using Forms and VBA within Excel Next: Moving cells to row with matching data 