Custom number format

[pujo]

I cannot figure this out, may no be possible with a number format. If not, is there any other way to accomplish this?
I have thousands of these to enter into a spreadsheet.

Using this custom number format "MNET_1.WriteData["#0"]" I can get the number to increase dragging the cell and filling series.
Example

MNET_1.WriteData[0]

MNET_1.WriteData[1]

MNET_1.WriteData[2]

MNET_1.WriteData[3]

MNET_1.WriteData[4]

I want to add a second number format MNET_1.WriteData[#0"]."#0
Example

MNET_1.WriteData[0].0

MNET_1.WriteData[1].1

MNET_1.WriteData[2].2

MNET_1.WriteData[3].3

MNET_1.WriteData[4].4

Also, I need the number to start over at 0 after the value reaches 15
Example

MNET_1.WriteData[0].13

MNET_1.WriteData[1].14

MNET_1.WriteData[1].15

MNET_1.WriteData[2].0

MNET_1.WriteData[2].1

MNET_1.WriteData[2].2

 

[Micron]

I've been wrong before when I've said I think you're looking at a code solution because someone posts some mind-bending formula. To help others help you, isn't your example incorrect? I'm thinking you meant

MNET_1.WriteData[0].13
MNET_1.WriteData[0].14
MNET_1.WriteData[0].15
MNET_1.WriteData[1].0
MNET_1.WriteData[1].1
MNET_1.WriteData[1].2
MNET_1.WriteData[1]…14
MNET_1.WriteData[1].15
MNET_1.WriteData[2].0
MNET_1.WriteData[2].1
MNET_1.WriteData[2].2 and so on

 

[James006]

Hi,

Custom Format ... can warn you ... but Custom Format cannot change the values ...

You can test following : [<16]0.00;"Numbers Lower than 16 only !"

 

[pujo]

I've been wrong before when I've said I think you're looking at a code solution because someone posts some mind-bending formula. To help others help you, isn't your example incorrect? I'm thinking you meant

MNET_1.WriteData[0].13
MNET_1.WriteData[0].14
MNET_1.WriteData[0].15
MNET_1.WriteData[1].0
MNET_1.WriteData[1].1
MNET_1.WriteData[1].2
MNET_1.WriteData[1]…14
MNET_1.WriteData[1].15
MNET_1.WriteData[2].0
MNET_1.WriteData[2].1
MNET_1.WriteData[2].2 and so on

Yea, I was off but it was just for an example, good eye!

 

[pujo]

If the numbers were 0-9, it wouldn't be an issue however it's 16 bit integers.....
Number format: "MNET_1.WriteData["0"]."0
Drag down, fill series

MNET_1.WriteData[0].6

MNET_1.WriteData[0].7

MNET_1.WriteData[0].8

MNET_1.WriteData[0].9

MNET_1.WriteData[1].0

MNET_1.WriteData[1].1

MNET_1.WriteData[1].2

MNET_1.WriteData[1].3

 

[Micron]

So 14 isn't base 10, it's 1110 base 2?
If that's true, I still don't see how your posted pattern relates.

I need the number to start over at 0 after the value reaches 15

and yet you show it starting over when you get to 9.

I'm guessing you need an integer to binary or binary to decimal converter function. Not sure because I don't understand the pattern when you get to 9. Or 15 for that matter - that suggests it Hex. Hope you get a solution.

 

[pujo]

So 14 isn't base 10, it's 1110 base 2?
If that's true, I still don't see how your posted pattern relates.

and yet you show it starting over when you get to 9.

I'm guessing you need an integer to binary or binary to decimal converter function. Not sure because I don't understand the pattern when you get to 9. Or 15 for that matter - that suggests it Hex. Hope you get a solution.

Starting over at 9 was only an example of this number format: "MNET_1.WriteData["0"]."0 dragging down, fill series.
It looks like this

MNET_2.WriteData[0].0

MNET_2.WriteData[0].1

MNET_2.WriteData[0].2

MNET_2.WriteData[0].3

MNET_2.WriteData[0].4

MNET_2.WriteData[0].5

MNET_2.WriteData[0].6

MNET_2.WriteData[0].7

MNET_2.WriteData[0].8

MNET_2.WriteData[0].9

MNET_2.WriteData[0].10

MNET_2.WriteData[0].11

MNET_2.WriteData[0].12

MNET_2.WriteData[0].13

MNET_2.WriteData[0].14

MNET_2.WriteData[0].15

MNET_2.WriteData[1].0

MNET_2.WriteData[1].1

MNET_2.WriteData[1].2

MNET_2.WriteData[1].3

MNET_2.WriteData[1].4

MNET_2.WriteData[1].5

MNET_2.WriteData[1].6

MNET_2.WriteData[1].7

MNET_2.WriteData[1].8

MNET_2.WriteData[1].9

MNET_2.WriteData[1].10

MNET_2.WriteData[1].11

MNET_2.WriteData[1].12

MNET_2.WriteData[1].13

MNET_2.WriteData[1].14

MNET_2.WriteData[1].15

MNET_2.WriteData[2].0

 

[Micron]

Well, never let it be said that I don't like a challenge (at least sometimes). I spend at least 2 hours on this and it is far from perfect. Some reasons for taking so long to write would be:
- cell formatting seemed caused udf to not be called (are you kidding me??)
- generating type mismatch error (13), which I wrote a handler for, then it mysteriously went away
- until I wrote the handler, I could not seem to overcome the fact that if there was 1 iteration it ran - if 2 or more, then error 13. I put that down to the dependent cell in prior row to not being calculated, so
- tried to force calculation, to no avail
If I drag the formula down from A1 for about 2K rows it takes about 4 seconds to calculate. I guess it would be better if I use arrays instead, but it is what it is.

Some results as pics (because copy/paste of the results just runs the udf from scratch):

and so on.

'MNET_2.WriteData[0].0 'positions 18 and 21 for 0 and 0
Public Function MNET(rng As range) As String
Dim x As Integer, y As Integer, z As Integer

On Error GoTo errHandler
If rng.Row = 1 Then
     MNET = "MNET_2.WriteData[0].0"
     Exit Function
Else
     x = Val(Mid(rng.Offset(-1, 0), 18))
     y = Val(Mid(rng.Offset(-1, 0), 21))
End If

If y = 15 Then
     x = x + 1
     y = 0
Else
     y = y + 1
End If

MNET = "MNET_2.WriteData[" & x & "]." & y

exitHere:
Application.EnableEvents = True

Exit Function

 

[pujo]

Well, never let it be said that I don't like a challenge (at least sometimes). I spend at least 2 hours on this and it is far from perfect. Some reasons for taking so long to write would be:
- cell formatting seemed caused udf to not be called (are you kidding me??)
- generating type mismatch error (13), which I wrote a handler for, then it mysteriously went away
- until I wrote the handler, I could not seem to overcome the fact that if there was 1 iteration it ran - if 2 or more, then error 13. I put that down to the dependent cell in prior row to not being calculated, so
- tried to force calculation, to no avail
If I drag the formula down from A1 for about 2K rows it takes about 4 seconds to calculate. I guess it would be better if I use arrays instead, but it is what it is.

Some results as pics (because copy/paste of the results just runs the udf from scratch):
View attachment 84684 View attachment 84685 View attachment 84686
and so on.

'MNET_2.WriteData[0].0 'positions 18 and 21 for 0 and 0
Public Function MNET(rng As range) As String
Dim x As Integer, y As Integer, z As Integer

On Error GoTo errHandler
If rng.Row = 1 Then
     MNET = "MNET_2.WriteData[0].0"
     Exit Function
Else
     x = Val(Mid(rng.Offset(-1, 0), 18))
     y = Val(Mid(rng.Offset(-1, 0), 21))
End If

If y = 15 Then
     x = x + 1
     y = 0
Else
     y = y + 1
End If

MNET = "MNET_2.WriteData[" & x & "]." & y

exitHere:
Application.EnableEvents = True

Exit Function

Thank you for your time!
I will give this a try and see what happens.
Good Day!