VBA Formula Copy Issue

[vikas_newports]

In my Column C some text data available I want to Add Quotes " in the start and end so tried below code but it is copy C1 value till last used row of C
the sample data of C is as

Mleme your salary for nov.20 is Basic 35000 Phone 5000 Gross 40000 TAX 0 Pension 2000 Sacco 500 ABS. 1000 NET 36500

Chilinkhwambe your salary for nov.20 is Basic 38000 Arrears 2000 Overtime 1200 Gross 41200 TAX 0 Pension 0 Sacco 0 NET 41200

Chapuma your salary for nov.20 is Basic 35000 Overtime 1200 Gross 36200 TAX 0 Pension 1810 Sacco 400 ABS. 500 Uniform 500 NET 32990

Namasipa your salary for nov.20 is Basic 35000 Bonus 5000 Gross 40000 TAX 0 Pension 2000 Sacco 0 NET 38000

Kajanga your salary for nov.20 is Basic 35000 Gross 35000 TAX 0 Pension 0 Sacco 100 NET 34900

Najira your salary for nov.20 is Basic 40500 Leave 5000 Gross 45500 TAX 0 Pension 0 Sacco 900 Uniform 1000 NET 43600

    Range("D1").Value = Chr(34)
    Range("D1").Copy
    Last_Row1 = Range("A" & Rows.Count).End(xlUp).Row
    Range("D1").Copy Range("D1:D" & Last_Row1)
    Range("E1").Formula = Range("D1") & Range("C1") & Range("D1")
    
    Range("E1").Copy Range("E1:E" & Last_Row1)
'    Range("E:E").Copy Range("C:C")

 

[vikas_newports]

I am trying to import this csv to sms script file which will read and insert this data in sms body

 

[ZVI]

You may try the below code to save contents of column C into MyExport.txt file.
Open that file in Notepad++ to find double quote characters surrounding for each cell value.

Sub ExportRangeToTextfile()
' This code saves values of column C to MyExport.txt file in the folder of the active workbook
' Each cell value is in the new line of the text file and surrounded by double quote symbols
 
  Const FILENAME = "MyExport.txt"
 
  Dim Rng As Range, a, b(), i As Long, s As String, FN As Integer

  Set Rng = Range("C1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
  a = Rng.Value
  If Not IsArray(a) Then
    ReDim a(1 To 1)
    a(1, 1) = Rng.Value
  End If
 
  ReDim b(1 To UBound(a))
 
  For i = 1 To UBound(a)
    b(i) = Replace(a(i, 1), Chr(34), Chr(34) & Chr(34))
    b(i) = Chr(34) & b(i) & Chr(34)
  Next
  s = Join(b, vbCrLf)
 
  FN = FreeFile
  Open FILENAME For Binary Access Write As #FN
  Put #FN, , s
  Close #FN
 
End Sub

 

[vikas_newports]

Thanks ZVI

 

[ZVI]

You are welcome!