csv macro

gleamng

Board Regular
Joined
Oct 8, 2016
Messages
98
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 2007
  9. 2003 or older
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Good evening everyone here,
i need your help with a macro
i want the macro to split the CSV value in the column D into different column
also i want the macro to look through the column create heading for each deductions separately (i.e. is should create a column for all deduction type that appeared in column D - NHF, TAX, NASU etc), then put each figure i its respective column without the "N" before the figures.
again i want the column to be inserted after column D

thank you all for your anticipated support, as i hope to hear from the forum soonest.

thanks all.

csv - Copy.jpg
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Try this, the results in cell H1 onwards

VBA Code:
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("D2", Range("D" & Rows.Count).End(3)).Value2
  
  For i = 1 To UBound(a, 1)
    For Each c In Split(a(i, 1), ",")
      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
    Next
  Next
  Range("H1").Resize(UBound(a) + 1, col).Value = b
End Sub
 
Upvote 0
Thank you for your help.
I run the code but it returned error

Run-time error '9'
Subscript out of range
 
Upvote 0
Thank you for your help.
I run the code but it returned error

Run-time error '9'
Subscript out of range

when i click on debug it highlighted the code below
ded = Val(Replace(Split(c, "=")(1), "N", "", , , vbTextCompare))
 
Upvote 0
Probably some of your deductions do not have the "=" separator, try the following and tell me.

VBA Code:
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("D2", Range("D" & 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
 
Upvote 0
Thank you so much for helping me out, the macro worked perfectly.
I will like the macro to insert its own columns and not over-write other column from H
Again i want to be able to decide which column it will start inserting the split result by popping out a message box which i will then type in the column
Range("H1").Resize(UBound(a) + 1, col).Value = b
God bless you
 
Upvote 0
I will like the macro to insert its own columns and not over-write other column from H
Again i want to be able to decide which column it will start inserting the split result by popping out a message box which i will then type in the column
Range("H1").Resize(UBound(a) + 1, col).Value = b

If I understand correctly, if you have data in H1 then change H1 for an available cell, for example BC1.
Range("BC1").Resize(UBound(a) + 1, col).Value = b
 
Upvote 0
Thank you a million, i got it.
Finally, i want this macro to be able to run on any excel workbook i opened.
I am sincerely grateful for your understanding, patience and support.
 
Upvote 0
Probably some of your deductions do not have the "=" separator, try the following and tell me.

VBA Code:
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("D2", Range("D" & 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


Good evening, how are you and yours
i have a small issue as regards the above macro.
instead of the way the macro runs by splitting the whole column
i want the macro to split just one cell and display it in the way attached image samplee1 appears, provided that the ver. no. appears in cell B10,
and insert the splitted deductions as table from cell b20

thanking you for your untiring support.
regards to yours
 

Attachments

  • samlee1.jpg
    samlee1.jpg
    203.4 KB · Views: 2
  • samlee2.jpg
    samlee2.jpg
    246 KB · Views: 3
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top