PDA

View Full Version : [SOLVED] Converting a string by detecting Chr(183)



paulked
12-21-2017, 05:10 PM
Hi there.

I am updating a program that handles a small business accounts system.

At the moment the Invoice area is a grid of 8 columns by 20 rows and all the data is stored in a string.

The grid headers are like this:



Cat
Code
Description
Price
Qty
Net
VAT
Total













The string with the data is:

kk·22008·Replacement INTERNAL panels only for ····0·0···surround provided under Inv 3906 ····0·0···as follows:-·350·1·350·70·420···right hand panel @ 600mm high; ····0·0···h/b end @ 600mm high; ····0·0···left hand panel @ 650mm high; ····0·0···reinforced f/b end @ 600mm high. ····0·0·······0·0·kk·1001·Delivery and installation:·180·1·180·36·216···(respite unit) ····0·0·······0·0·······0·0·······0·0·······0·0·······0·0·······0·0·······0 ·0·······0·0·······0·0·kk·54001·XtraCare cover·100·1·100·20·120·

And the code to fill the grid from the above string is:



Dim St As String, Srow, L, p, ps, Col, s, E, c, rw, x As Integer
St = Cells(1, 1) 'col 37
L = Len(St): p = 1: s = 1: rw = 4: Col = 4: c = 0
'select strings between chr(183) markers
For x = 1 To L
p = Mid(St, x, 1)
If p = Chr(183) Then
E = x - 1: c = c + 1: Col = Col + 1
ps = Mid(St, s, E - s + 1)
s = E + 2
Cells(rw, Col) = ps
If c = 8 Then
rw = rw + 1: Col = 4: c = 0
End If
End If
Next



The new Invoice area has only 5 columns to fill as the Net, VAT and Total are calculated using the Price and Qty:



Cat
Code
Description
Price
Qty
Net
VAT
Total







=P+Q
=N*0.2
=N+V



There are over 10,000 of these strings so I need a sub to convert them all to suit the new format when I import the data to the new program.

This is what the converted string above would look like:

kk·22008·Replacement INTERNAL panels only for ·····surround provided under Inv 3906 ·····as follows:-·350·1···right hand panel @ 600mm high; ·····h/b end @ 600mm high; ·····left hand panel @ 650mm high; ·····reinforced f/b end @ 600mm high. ········kk·1001·Delivery and installation:·180·1···(respite unit) ················································kk·54001·XtraCare cover·100·1·

I've tied myself up in loops (literally!) trying to figure it out, but have got nowhere :banghead:

Any help would be greatly appreciated.

Best regards

Paul Ked

paulked
12-21-2017, 05:31 PM
This is the closest I've got:



Sub ConvertOld()
Dim x As Long, c As Long, d As Long, OldStr As String, NewStr As String
OldStr = Cells(1, 1)
c = 1
d = 1
For x = 1 To Len(OldStr)
If c < 5 Then NewStr = NewStr & Mid(OldStr, x, 1)
If Mid(OldStr, x, 1) = Chr(183) Then
c = c + 1
If c = 6 Then
d = d + 1
c = c - 1
If d = 4 Then
c = 1
d = 1
End If
End If
End If
Next
Cells(3, 1) = NewStr
End Sub

paulked
12-21-2017, 05:46 PM
Got it!



Sub ConvertOld()
Dim x As Long, c As Long, d As Long, OldStr As String, NewStr As String
OldStr = Cells(1, 1)
c = 1
d = 1
For x = 1 To Len(OldStr)
If Mid(OldStr, x, 1) = Chr(183) Then
c = c + 1
If c = 7 Then
d = d + 1
c = c - 1
If d = 4 Then
c = 1
d = 1
End If
End If
End If
If c < 6 Then NewStr = NewStr & Mid(OldStr, x, 1)
Next
Cells(3, 1) = NewStr
End Sub




Thank you forum for providing time out to clear the head!!!