I’m trying to create a VBA where I input information regarding payments across 22 columns. These columns include payment amount, account, etc. I can then run a macro and it formats the data to a usable string, then exports the data to a CSV file. The data has to be formatted with apostrophe’s and commas between the info.
For example, column B is merchant ID and column C is merchant name (Column A is blank). I type 123 in column B and xyz in column C. It gets converted to ‘123’,’xyz’, … , …, The goal is to have the formatting completed, concatenated and then exported to a CSV. Currently I’m using a mix of formulas/functions and a macro.
I use formulas to get to the formatting correct by using: =”‘”&B2&”‘”&”,”
Then use concatenate to merge all 22 rows to a single string
Then I recorded a macro with the following:
Sub BCM_CSV_export() ' ' BCM_CSV_export Macro Range("B21").Select ActiveCell.FormulaR1C1 = "=""'""&R[-19]C&""'""&"",""" Range("B21:V21").Select Selection.FillRight Range("B21:V30").Select ActiveWindow.SmallScroll Down:=9 Selection.FillDown Range("B40").Select ActiveCell.FormulaR1C1 = "=CONCAT(R[-19]C:R[-19]C)" Range("B40:B49").Select Selection.FillDown Workbooks.Add Windows("BCM CSV Workbook.xlsm").Activate Selection.Copy Windows("Book1").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("E13").Select Application.CutCopyMode = False ActiveWorkbook.SaveAs "CSVDate.csv", _ FileFormat:=xlCSV, CreateBackup:=False ActiveWindow.Close Selection.ClearContents Range("B21").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.ClearContents ActiveWindow.SmallScroll Down:=-24 End Sub
The code seems to work on my laptop. The CSV file shows up in ‘My Documents’. But my colleague encounters errors. The code doesnt seem to run.
My question is 2 fold:
1. Is there something inherently wrong with my macro that it only runs on my laptop?
2. Is there a way to convert the 22 rows of data into a single text string and into a new CSV workbook without the intermediate steps of my using the text string formatting and concatenate function?
I used the search function and didnt see anything exactly matching this. Thanks a lot everyone!