gleamng
Board Regular
- Joined
- Oct 8, 2016
- Messages
- 98
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Platform
- Windows
- MacOS
- Mobile
- Web
Good day everyone,
i have no formal knowledge about vba programming, i hereby seek your help.
The macro below was developed by this forum member Dante Amor, much gratitude to him
Ver. No. is Column A, Names = Col B & Deduction = Col. C
instead of the macro to insert the split results for all cell under deduction into columns H onward, i want the macro the do the spit based on criteria in column A.
what i mean is that once i enter the ver. no. in cell "K1" in Sheet2, while the table is in Sheet1 (image sample1) macro should split the merged deduction into a table from cell "K5" just as displayed in the attached image named (sample2)
Sub deductions()
Dim a As Variant, b() As Variant, c As Variant, ded As Variant
Dim dic As Object, i As Long, col As Long, tax As String
Set dic = CreateObject("Scripting.Dictionary")
a = Range("C2", Range("C" & Rows.Count).End(3)).Value2
For i = 1 To UBound(a, 1)
For Each c In Split(a(i, 1), ",")
If InStr(1, c, "=") > 0 Then
tax = Split(c, "=")(0)
ded = Val(Replace(Split(c, "=")(1), "N", "", , , vbTextCompare))
If Not dic.exists(tax) Then
col = col + 1
ReDim Preserve b(1 To UBound(a, 1) + 1, 1 To col)
dic(tax) = col
b(1, col) = tax
End If
b(i + 1, dic(tax)) = ded
End If
Next
Next
Range("H1").Resize(UBound(a) + 1, col).Value = b
End Sub
Thank you all for your continued support.
i have no formal knowledge about vba programming, i hereby seek your help.
The macro below was developed by this forum member Dante Amor, much gratitude to him
Ver. No. is Column A, Names = Col B & Deduction = Col. C
instead of the macro to insert the split results for all cell under deduction into columns H onward, i want the macro the do the spit based on criteria in column A.
what i mean is that once i enter the ver. no. in cell "K1" in Sheet2, while the table is in Sheet1 (image sample1) macro should split the merged deduction into a table from cell "K5" just as displayed in the attached image named (sample2)
Sub deductions()
Dim a As Variant, b() As Variant, c As Variant, ded As Variant
Dim dic As Object, i As Long, col As Long, tax As String
Set dic = CreateObject("Scripting.Dictionary")
a = Range("C2", Range("C" & Rows.Count).End(3)).Value2
For i = 1 To UBound(a, 1)
For Each c In Split(a(i, 1), ",")
If InStr(1, c, "=") > 0 Then
tax = Split(c, "=")(0)
ded = Val(Replace(Split(c, "=")(1), "N", "", , , vbTextCompare))
If Not dic.exists(tax) Then
col = col + 1
ReDim Preserve b(1 To UBound(a, 1) + 1, 1 To col)
dic(tax) = col
b(1, col) = tax
End If
b(i + 1, dic(tax)) = ded
End If
Next
Next
Range("H1").Resize(UBound(a) + 1, col).Value = b
End Sub
Thank you all for your continued support.