CODEWITHSUNDEEP
vbaexcelsplit
p.Jam
p.Jam

Reputation: 21

VBA Split string and convert to Int

I have an Excel sheet where I have data in a column J4:J163, Each cell contains a string e.g. J4 = "1 6, 8 18, 20 24"

Using VBA I want to use the Split function to separate on the delimiter ',' then convert string values e.g. "1 6, 8 18, 20 24" into integers. Which should result into something like this '1 6' '8 18' '20 24'

I want to iterate through each two integer values subtracting like so

1-6 = 5

8-18 = 10

20-24 = 4

Each time adding these results to a variable i.e. 'total'

Depending on how many subtractions have been performed increment a variable, cnt++, in this case 3 times, (total + cnt).

total = 22

This result calculated from cell J4 i.e. 22 should be inputted into L4. The result from J5 should be inputted into L5 etc.

Hope this makes sense. Below is a very bare pseudo code, (newbie to VBA) your guidance would be well received.

Sub CalcHrs()

'Variables
Dim wrdArray() As String
Dim textString As String
Dim total As Integer 'substration results accumulation
Dim cnt As Integer 'for loop iteration count

' Grab String
textString = Range("J4")
' Place string into Array and Split
wrdArray() = Split(textString)

'Loop to convert string array to int array

'Some loop (i < array.length, bla bla)
'array index 0 - array index 1 place result into 'total'
'iterate through until length of array is reached, while cnt++

'place result of total + cnt into cell "L4"

'Move onto cell "J5" until "J163"

End Sub

Upvotes: 0

Views: 3334

Answers (1)

Mituga
Mituga

Reputation: 58

Like somebody writted up. It's not a free "write-my-code" service, You should try yourself and If your code doesn't work then You can post it here to get help.

I will give you some tips:

  • Split the cell value like this: Split (Cell, ",")
  • Iterate the array result of this Split with for-each and split each value like this: Split(value, " ")
  • Add 1 to the normal count and add ABS(cint(value1) - cint(value2)) to the total count. Value1 and Value2 are the values retorned by the second split.

Upvotes: 2

Related Questions