Modify VBA code to input value to specific column

M

max_cali

New Member
Joined
Apr 23, 2017
Messages
21
Hi dears,

I have 2 different excel files and I'm in need to merge them into one in an order.

File 1: ABC1.csv

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1TRANSACTION_REF:TESTING1
2SUM OF PAYMENT_AMOUNT:22
3PAYMENT_CCY:USD
4PAYMENT_VALUE_DATE:27/11/2017
5
6MA_LHMA_XNSO_VAN_DON_1NGAY_DKSO_TKNGAY_HOAN_THANH_KTMA_PHAN_LOAI_KTTEN_NXKMA_NUOC_NXKNGUOI_UY_THAC_XUAT_KHAUMA_SO_THUE_NNKTEN_NNKSO_HOA_DONNGAY_PHAT_HANHPHUONG_THUC_THANH_TOANTONG_TRI_GIA_HOA_DONNGUYEN_TE_TONG_TRI_GIA_HOA_DONTONG_SO_TIEN_MIEN_GIAMPAYMENT_AMOUNTFILE_NAMENGAY_IMPORTREPORTINGSO_TIEN_LOIMA_XN_LOIPHUONG_THUC_TT_LOINGOAI_TE_LOI
7A41NABC127/06/201712345628/06/20173BCD1VN1.00E+08CDE112320/06/2017TTR11EUR011ABC1.CSV##########STLGB
8A41NABC126/06/201723456728/06/20173BCD1VN1.00E+08CDE145614/06/2017TTR11EUR011ABC1.CSV##########STLGB

<tbody>
</tbody>
Sheet1​
File 2: ABC2.csv

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1TRANSACTION_REF:TESTING2
2SUM OF PAYMENT_AMOUNT:12
3PAYMENT_CCY:USD
4PAYMENT_VALUE_DATE:27/11/2017
5
6MA_LHMA_XNSO_VAN_DON_1NGAY_DKSO_TKNGAY_HOAN_THANH_KTMA_PHAN_LOAI_KTTEN_NXKMA_NUOC_NXKNGUOI_UY_THAC_XUAT_KHAUMA_SO_THUE_NNKTEN_NNKSO_HOA_DONNGAY_PHAT_HANHPHUONG_THUC_THANH_TOANTONG_TRI_GIA_HOA_DONNGUYEN_TE_TONG_TRI_GIA_HOA_DONTONG_SO_TIEN_MIEN_GIAMPAYMENT_AMOUNTFILE_NAMENGAY_IMPORTREPORTINGSO_TIEN_LOIMA_XN_LOIPHUONG_THUC_TT_LOINGOAI_TE_LOI
7A12NABC327/10/201734567827/10/20172BCD2VN2.00E+08CDE278924/10/2017TTR12USD012ABC2.csv##########NIL12USD27/11/2017

<tbody>
</tbody>
Sheet1​
After running a code to merge them into one worksheet, the result I expect would be:

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1MA_LHMA_XNSO_VAN_DON_1NGAY_DKSO_TKNGAY_HOAN_THANH_KTMA_PHAN_LOAI_KTTEN_NXKMA_NUOC_NXKNGUOI_UY_THAC_XUAT_KHAUMA_SO_THUE_NNKTEN_NNKSO_HOA_DONNGAY_PHAT_HANHPHUONG_THUC_THANH_TOANTONG_TRI_GIA_HOA_DONNGUYEN_TE_TONG_TRI_GIA_HOA_DONTONG_SO_TIEN_MIEN_GIAMPAYMENT_AMOUNTTRANSACTION_REFNGAY_IMPORTREPORTINGSUM OF PAYMENT_AMOUNTPAYMENT_CCYPAYMENT_VALUE_DATESO_TIEN_LOIMA_XN_LOIPHUONG_THUC_TT_LOINGOAI_TE_LOI
2A41NABC16/26/20171234566/28/20173BCD1VN100000000CDE112320/06/2017TTR11EUR011TESTING1.CSV11/27/2017 14:55STLGB22USD11/27/2017
3A41NABC26/26/20172345676/28/20173BCD1VN100000000CDE145614/06/2017TTR11EUR011TESTING1.CSV11/27/2017 14:55STLGB22USD11/27/2017
4-------
5A12NABC310/27/201734567810/27/20172BCD2VN200000000CDE278924/10/2017TTR12USD012TESTING2.CSV11/27/2017 12:12NIL12USD11/27/2017

<tbody>
</tbody>
Sheet1​
Specifically, I have made some adjustment after merging the two files into one (I call the worksheet as "Master"):
(1) Remove entire row A1, A2, A3, A4 (Transaction_ref, Sum of Payment_amount...) and put the value of these items into the table (column T, W, X, Y)
(2) Add a line "-----------" between the two files.
(3) Change date format of column D, F, Y to m/dd/yyyy (not included in below code)

The code I use to do the task is as follows (I removed the code to merge 2 files into 1)

Code: 
Sub Remove_replace()
Dim lr As Long, r As Range, t As String, t1 As String, t2 As String, t3 As String
Application.ScreenUpdating = False
 
With Workbooks.Application.Sheets("<wbr>Master")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  With .Range("A1:A" & lr)
    '.Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    '.Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    '.Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
    .Replace "MA_LH", "#N/A", xlWhole, , False
    .Replace "", "-------", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(<wbr>xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For Each r In .Range("A1:A" & lr)
    If InStr(r, "TRANSACTION_REF:") Then
      t = Right(r, Len(r) - 16)
    ElseIf InStr(r, "SUM OF PAYMENT_AMOUNT:") Then
      t1 = Right(r, Len(r) - 22)
    ElseIf InStr(r, "PAYMENT_CCY:") Then
      t2 = Right(r, Len(r) - 12)
    ElseIf InStr(r, "PAYMENT_VALUE_DATE:") Then
      t3 = Right(r, Len(r) - 19)
    ElseIf r = "N" Then
      r.Offset(, 21) = t
      r.Offset(, 24) = t1
      r.Offset(, 25) = t2
      r.Offset(, 26) = t3
    End If
  Next r
  With .Range("A1:A" & lr)
    .Replace "TRANSACTION_REF*", "#N/A", xlWhole, , False
    .Replace "SUM OF PAYMENT_AMOUNT*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_CCY*", "#N/A", xlWhole, , False
    .Replace "PAYMENT_VALUE_DATE*", "#N/A", xlWhole, , False
  End With
  On Error Resume Next
  .Columns("A").SpecialCells(<wbr>xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
  .Columns("A:Z").AutoFit
  Cells(1, 1) = "MA_LH"
  Cells(1, 2) = "MA_XN"
  Cells(1, 3) = "SO_VAN_DON_1"
  Cells(1, 4) = "NGAY_DK"
  Cells(1, 5) = "SO_TK"
  Cells(1, 6) = "NGAY_HOAN_THANH_KT"
  Cells(1, 7) = "MA_PHAN_LOAI_KT"
  Cells(1, 8) = "TEN_NXK"
  Cells(1, 9) = "MA_NUOC_NXK"
  Cells(1, 10) = "NGUOI_UY_THAC_XUAT_KHAU"
  Cells(1, 11) = "MA_SO_THUE_NNK"
  Cells(1, 12) = "TEN_NNK"
  Cells(1, 13) = "SO_HOA_DON"
  Cells(1, 14) = "NGAY_PHAT_HANH"
  Cells(1, 15) = "PHUONG_THUC_THANH_TOAN"
  Cells(1, 16) = "TONG_TRI_GIA_HOA_DON"
  Cells(1, 17) = "NGUYEN_TE_TONG_TRI_GIA_HOA_<wbr>DON"
  Cells(1, 18) = "TONG_SO_TIEN_MIEN_GIAM"
  Cells(1, 19) = "PAYMENT_AMOUNT"
  Cells(1, 20) = "TRANSACTION_REF"
  Cells(1, 21) = "NGAY_IMPORT"
  Cells(1, 22) = "REPORTING"
  Cells(1, 23) = "SUM OF PAYMENT_AMOUNT"
  Cells(1, 24) = "PAYMENT_CCY"
  Cells(1, 25) = "PAYMENT_VALUE_DATE"
  Cells(1, 26) = "SO_TIEN_LOI"
  Cells([URL="https://maps.google.com/?q=1,+27)+%3D+%22MA&entry=gmail&source=g"]1, 27) = [/URL]"[URL="https://maps.google.com/?q=1,+27)+%3D+%22MA&entry=gmail&source=g"]MA[/URL]_XN_LOI"
  Cells(1, 28) = "PHUONG_THUC_TT_LOI"
  Cells(1, 29) = "NGOAI_TE_LOI"

End With
Application.ScreenUpdating = True
End Sub

However, after running the code, (1) is not happened and it's not like what I expect in first table. Can anyone look into the above code and advise me where the error is and how to do (3). Million thanks.

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1MA_LHMA_XNSO_VAN_DON_1NGAY_DKSO_TKNGAY_HOAN_THANH_KTMA_PHAN_LOAI_KTTEN_NXKMA_NUOC_NXKNGUOI_UY_THAC_XUAT_KHAUMA_SO_THUE_NNKTEN_NNKSO_HOA_DONNGAY_PHAT_HANHPHUONG_THUC_THANH_TOANTONG_TRI_GIA_HOA_DONNGUYEN_TE_TONG_TRI_GIA_HOA_DONTONG_SO_TIEN_MIEN_GIAMPAYMENT_AMOUNTTRANSACTION_REFNGAY_IMPORTREPORTINGSUM OF PAYMENT_AMOUNTPAYMENT_CCYPAYMENT_VALUE_DATESO_TIEN_LOIMA_XN_LOIPHUONG_THUC_TT_LOINGOAI_TE_LOI
2A41NABC127/06/201712345628/06/20173BCD1VN100000000CDE112320/06/2017TTR11EUR011TESTING1.CSV11/27/2017 14:55STLGB
3A41NABC226/06/201723456728/06/20173BCD1VN100000000CDE145614/06/2017TTR11EUR011TESTING1.CSV11/27/2017 14:55STLGB
4-------
5A12NABC327/10/201734567827/10/20172BCD2VN200000000CDE278924/10/2017TTR12USD012TESTING2.CSV11/27/2017 12:12NIL

<tbody>
</tbody>
Sheet1​
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
I'm not sure if I have the answers, but #1 doesn't look to be in the macro at all. You'd also need a line that deletes the first 5 rows after the values are provided to columns T, W, X, and Y (respectively I'm guessing).
Code: 
        .Range("T7:T" & lr).Value = Right(Range("A1").Value, Len(Range("A1").Value) - InStr(Range("A1").Value, ":") - 1)
        .Range("W7:W" & lr).Value = Right(Range("B1").Value, Len(Range("B1").Value) - InStr(Range("B1").Value, ":") - 1)
        .Range("X7:X" & lr).Value = Right(Range("C1").Value, Len(Range("C1").Value) - InStr(Range("C1").Value, ":") - 1)
        .Range("Y7:Y" & lr).Value = Right(Range("C1").Value, Len(Range("D1").Value) - InStr(Range("D1").Value, ":") - 1)
        .Range("A1:A5").EntireRow.Delete

On #2 , I don't see where the second table is being added to the first, but here is syntax for adding the dashes to the bottom:
Code: 
        .Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = "-----------"

#3 should be too difficult as long as Excel recognizes those values as dates. Otherwise, might have to parse the strings and reform. (Assumes Rows 1:5 has already been deleted)
Code: 
        .Range("D2:D" & lr & ", F2:F" & lr & ", Y2:Y" & lr).NumberFormat = "m/dd/yyyy"
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
Error in VBA Sub Colors() lines of code
Replies
7
Views
1K
Fluff
Fluff
BradleyS
  • Solved
Sum duration values only if -3
Replies
6
Views
940
Oaktree
Oaktree
A
  • Question
modified code and restrict splitting files each 10 files in each column
Replies
0
Views
311
Alaa mg
A
MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
434
MrDB4Excel
MrDB4Excel
E
  • Question
Excel to return work week given certain criteria across mutli year
Replies
2
Views
654
Exceltung
E

Forum statistics

Threads
1,215,726
Messages
6,126,503
Members
449,316
Latest member
sravya

Share this page

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