DataTable convert one column in absolute values

Help
1

Hi guys,

I’m trying to find the best way to convert one column in a large datatable to data with only absolute values.

And i want to add that the Headers are not known. So the conversion has to be specified on the column. For example column K.

Any suggestions would be fine!

Kind Regards!

2

If you know the column you want you can use the column index - 0 for column A and so on.

If you want to set all the values to the same value you can use (for column K)

datatable.columns(10).DefaultValue = "ABC"

Otherwise use a for each loop and set each row value

1 Like
3

IT looks like the fix column way you are suggesting seems perfect for this case. I know the column is always K so that might work.

One more question. to apply this methode

Do you use an assign? Cause if i do it this way it gives an error.

4

Forget the use of default value - it should work but it isn’t as secure as it should be and may have some implications when trying to access that data.

An easier way would be to use a for each row loop and just as effective:

For Each Row
Assign - row.Item(10) = “ABC”

1 Like
5

Fixed it with your previous suggestion and it works smoothly. Thanks for your time and effords!

invokedCode

6

Are you sure the default value might fail from time to time?

7

No problem :slight_smile: Should have mentioned use the invoke code bit rather than assign - bypassed that comment

It shouldn’t fail but I have had some issues previously when trying to insert a datatable into a database when setting values with .DefaultValue.

Aside from that it works perfectly :smile:

1 Like
8

Ahh i over looked something. The data was already sorted but because the list is to large i didn’t scroll down. Where the negative values still are.

I tryd both methods now the first one in the “Invoke Code” Part doesn’t really do anything. And the second one you mentiond changes every cel to ABC but does not convert to to the absolute value.

Might have any other suggestions?

9

Instead of"ABC" put Math.ABS(cdbl(row.item))

1 Like
10

Thanks your my hero for today! :face_with_hand_over_mouth:

1 Like
11

Happy to help :smile:

12

Thought it worked… it doesn’t. It if i use the cdbl method it return error that it in overcapacity. So i thought adding the row.item(10) in there would solve it. Well it did solve the issue, but if i run it gives me a syntax error:

Assign : Conversion from type ‘DBNull’ to type ‘Double’ is not valid.

Any other suggestions on this?
Sorry to keep bothering you with this. but i really want to get trough this part!

EDIT: I’m guessing the cdbl makes a double value out of it. I never worked with these so i’m searching for DBNull variably change now… Just thinking out loud, that this might work.

EDIT2:

SOLUTION

I had empty cells. which returned this value. i ignored those and it worked perfect!

1 Like
13

Yep was going to say it is due to blank cells / null values - :slight_smile:

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.