CODEWITHSUNDEEP
vbaexcelcsv
Tom Drier
Tom Drier

Reputation: 17

Excel new column macro

I have the following data imported from a .csv. So when I open this CSV (using a macro). The following lines will be printed.

Group Name  Name              UserName  Enabled   Lastchanged
Grp-Doe     John doe          j.doe     True      9-10-2017 07:25
Grp-Doe     Janna x           j.x       False     10-10-2017 08:10

Now, I want new column called NameChanged that uses an excel formula. Basically I wan't the following output.

Group Name  Name              UserName  Enabled   Lastchanged      NameChanged
Grp-Doe     John doe          j.doe     True      9-10-2017 07:25  John doe disabled on:09-10-2017
Grp-Doe     Janna x           j.x       False     1-10-2017 08:10  Janna X

The column NameChanged is calculated by a formula I wrote and I still posess.

Is there a macro function that automatically add's this data?

I tried this so far:

Sub AddData() 
lastRow = Sheets("Sheet2").Cells(65536, 2).End(xlUp).Row 
Sheets("Sheet2").Range("A2:A" & lastRow).Value = "=ALS(D2="False"; B2&" 
"&"Disabled op: "&TEKST(E2;"d-m-jjjj uu:mm"); B2)" 
End Sub

This gave me a syntax error

Upvotes: 0

Views: 53

Answers (2)

L.Stephen
L.Stephen

Reputation: 1

If you've created two functions call ALS() and TEKST() then just change .Value to .Formula and you need double "" for quotation within the formula

Upvotes: 0

YowE3K
YowE3K

Reputation: 23984

If you are going to enter a formula you should

  • Use the Formula property instead of the Value property
  • Write the formula using EN-US locale (for function names, etc) - or use the FormulaLocal property if you like, but then someone in another locale will get an error if they try to use your code
  • ensure that any " marks inside the formula are escaped by using "" instead


Sub AddData() 
    lastRow = Sheets("Sheet2").Cells(65536, 2).End(xlUp).Row 
    Sheets("Sheet2").Range("A2:A" & lastRow).Formula = "=IF(D2=""False"", B2&"" Disabled op: ""&TEXT(E2,""d-m-jjjj uu:mm""), B2)" 
End Sub

I think I translated your formula correctly. See if it works (i.e. after entering in the cell it looks like what you wanted) and, if not, I'll have another look.

Upvotes: 1

Related Questions