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")

 

[ZVI]

May be this will suit:

Sub Test()
  Dim Rng As Range
  Set Rng = Range("C1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
  With Rng.Offset(, 2)
    .Formula = "=" & String(4, Chr(34)) & "&C1&" & String(4, Chr(34))
    .Value = .Value
  End With
End Sub

 

[vikas_newports]

ZVI, it isinserting "" in start and "" in end of Column C
I just need " in start and end

 

[ZVI]

Like this?

Sub Test1()
  
  Dim RngFrom As Range, RngTo As Range
  
  Set RngFrom = Range("C1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
  Set RngTo = RngFrom.Offset(, 2)
  
  RngFrom.Copy RngTo
  
  With RngTo
    .Cells(1).Value = Chr(34) & .Cells(1).Value
    .Cells(.Cells.Count).Value = .Cells(.Cells.Count).Value & Chr(34)
  End With

End Sub

 

[vikas_newports]

ZVI , it is just inserting " in E1 in start and missing from end
Screenshot attached

 

[ZVI]

Just scroll horiosontaly to see the last submol in E6.
Or select E6, press F2 and press End to see the last symbol

 

[vikas_newports]

ZVI, you are right it is actually doing Putting " in E1 start and and " in the end of E6
while I am trying to get " in start and end of from E1 to E6

 

[ZVI]

To get " in start and end of from E1 to E6 use the code of post #2.
Could you please post the expected result?

 

[vikas_newports]

you are right it is giving " in start end but when I open it wordpad or Notepad++ it is showing """ in start and end of each row

 

[ZVI]

It is how text values are exported from Excel to CSV format in case double quote characters is in the text value:

• text value is surrounded by double quote characters
• each double quote character inside text value is doubled

Nothing was said previously about export. What is your real task?