Filling in columns counting by Hex - microsoft.public.excel.programming

3 columns: A B C 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, 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

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 > 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 > > I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, 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

Hi Ron, Assuming that the first value is in A2 insert the following formulas. In C2 enter ="00-00-00-00-"&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 > > 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 > > > > I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, 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

You didn't tell us how the front part of the values in Columns B and C are specified, so I hard-coded 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 = "00-00-00-00-" & Format(HexNum, "@@-@@") Next End Sub -- Rick (MVP - Excel) "Ron" <Ron@discussions.microsoft.com> wrote in message news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@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 >> 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 >> >> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, >> 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

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 hard-coded 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 = "00-00-00-00-" & Format(HexNum, "@@-@@") > Next > End Sub > > -- > Rick (MVP - Excel) > > > > "Ron" <Ron@discussions.microsoft.com> wrote in message > news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@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 > >> 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 > >> > >> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, > >> 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 > > . >

Well, my screaming to the engineers solved this. They are providng me with a copy of their spreadsheet, with this all filled in. Both of these seem to work for me and I use them differently on whether I needed a lot of a few. Thanks so much for the help. Ron "Ron" wrote: > 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 hard-coded 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 = "00-00-00-00-" & Format(HexNum, "@@-@@") > > Next > > End Sub > > > > -- > > Rick (MVP - Excel) > > > > > > > > "Ron" <Ron@discussions.microsoft.com> wrote in message > > news:C1C27371-BA57-4601-A99E-FC73DDA0F7FF@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 > > >> 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 > > >> > > >> I will have a list in column A, numerical...042-0-0-01234,042-0-0-01235, > > >> 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 > > > > . > >

Similiar Articles:

Filling in columns counting by Hex - microsoft.public.excel ...
3 columns: A B C 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 I will have a list in column A, num...

How to fill fill a column with numbers, beginning at number X ...
How do I set this up so I can simply ... How to fill fill a column with numbers, beginning at number X ... ... in columns ... Filling in columns counting by Hex ...

Filling In A Column With Incrementing Values ? - microsoft.public ...
Filling in columns counting by Hex - microsoft.public.excel ..... e.g. D2 = 210 I would like to scroll down column A, and fill in column B ... How to populate a column ...

Auto fill column with sequential numbers with decimals - microsoft ...
How Do I Auto Fill Columns in Excel? | eHow.com How Do I ... How to fill fill a column with numbers, beginning at number ... Excel - Stop Auto Convert Of Hex Numbers To ...

How can I change the numbers in words. ( Ex.100.00 = One Hundred ...
Filling in columns counting by Hex - microsoft.public.excel ..... C 042-0-0-01234 100.100.1.210 00-00-00-00-04-D2 I ... > > > Oh yeah...if it could go down a certain ...

Add a new column to a table that requires a calculation ...
I need to autopopulate this column based on data in two existing columns. ... microsoft.public.excel ..... set of ... calculation from the total of a count of a ...

Copying Every Other Column - microsoft.public.mac.office.excel ...
Your sheet 2 will now have all the material in it from the odd columns If you want ... rows even though the data source is now in every other row? ... drag fill it down ...

Fill in Column with a series of repeating numbers - microsoft ...
Can I fill the column with a series of numbers but ... How to fill fill a column with numbers, beginning at number X ... ... in columns counting ...

Listview Column Count - microsoft.public.access.formscoding ...
Fill(ds1.OrderTree) For rc As Integer = 0 To (ds1.OrderTree.Count ... Treeview ... listview.Columns.count When I add columns in during the design stage, a seperate form gets ...

autofill won't fill to end of used range - microsoft.public ...
... macro, I need it to autofill formulas in various columns ... entries), but when the macro runs it will only fill ... lastrow As Long lastrow = ActiveSheet.Cells(Rows.Count ...

Adding the Tutorial Data - Free Microsoft Excel Spreadsheet Tips ...
Using Excel Auto Fill. To make our freeze pane demonstration a little more dramatic ... handle in cell D12 to auto fill across to cell M12. The numbers 1 to 9 should fill columns ...

How Do I Auto Fill Columns in Excel? | eHow.com
Many spreadsheets require the entry of a repetitive series of data or labels. This time consuming task can be eradicated by using Microsoft Excel's AutoFill ...

Color Functions In Excel
Keep this in mind when using hex ... Rows.Count NumCols = InRange.Columns.Count ... function in a formula to count the number of cells whose fill color is red.

Binary numeral system - Wikipedia, the free encyclopedia
Counting in binary is similar to counting in any other ... * * * * (starred columns are borrowed from) 1 1 0 1 1 1 0 ... 0 hex = 0 dec = 0 oct: 0: 0: 0: 0: 1 hex = 1 dec = 1 oct: 0: 0: 0: 1: 2 hex = 2 dec = 2 oct

7/23/2012 5:29:53 AM