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
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