Text in in formula

[pecisc]

Hi,

I am very new to VBA, hence the question is quite basic. However, after browsing forums, i could not tackle it myself. I was doing some modifications to the below part of a SUB.

It extracts information from one sheet as per the first row and should make the ''desired output''. I have the problem with syntax of text SEK, which is a constant. How do I correctly add it in the code, since the current syntax does not work? Thanks in advance

.Range("A1").FormulaR1C1 = "=TRIM('sheet1'!R[2]C[14])&"",""&TEXT(TODAY(),""ddmmyy"")&"",""&SEK&"",""&TEXT('sheet1'!R[2]C[7],""#.00000"")"
.Columns("A:A").ColumnWidth = 54.71
.Range("A1").AutoFill Destination:=Range("A1:A" & lngRows), Type:=xlFillDefault
.Columns(1).Copy
.Columns(1).PasteSpecial Paste:=xlPasteValues

Desired output:
SE0055762806,060319,SEK,99.999
SE0117623456,060319,SEK,98.111
SE0011957842,060319,SEK,100.089
etc.

 

[JLGWhiz]

You need double quote marks around SEK. Any string require quotation marks in a formula so in vba when creating as worksheet formula to be entered by code, it needs an extra set of quotation marks to let Excel not to put the single set on in the formula that is created in the cell.

Range("A1").FormulaR1C1 = "=TRIM('sheet1'!R[2]C[14])&"",""&TEXT(TODAY(),""ddmmyy"")&"",""&[COLOR=#ff0000]""[/COLOR]SEK[COLOR=#ff0000]""[/COLOR]&"",""&TEXT('sheet1'!R[2]C[7],""#.00000"")"

 

[Joe4]

Welcome to the Board!

Here is a little trick how you can get this code yourself.
Turn on the Macro Recorder, and then record yourself manually entering the formula into a cell in your workbook.
Then, stop the Macro Recorder and view your code. You will have recorded how that formula needs to look in VBA, so you can copy and paste that formula to your code above.

 

[pecisc]

Hi again,

I have added the text with double quotation marks ""&""SEK""&"" and it works while debugging:
[TABLE="width: 443"]
<colgroup><col></colgroup><tbody>[TR]
[TD]SE0011762806,060319,SEK,99.999[/TD]
[/TR]
[TR]
[TD]SE0011762814,060319,SEK,99.999[/TD]
[/TR]
[TR]
[TD]SE0011985159,060319,SEK,00.100

However, if i run the macro, it incorrectly puts the date and excludes SEK after first line:

[TABLE="width: 443"]
<colgroup><col></colgroup><tbody>[TR]
[TD]SE0011762806,000100,SEK,99.999[/TD]
[/TR]
[TR]
[TD]SE0011762814,000100,,99.999[/TD]
[/TR]
[TR]
[TD]SE0011985159,000100,,00.100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Are you sure it is copying down correctly? If you have hard-coded that "SEK" into your formula and copy it down, there is no way it should not appear.
Temporarily comments out the two "Columns" line of code, and take a look at the formulas on the sheet to make sure that they are correct.
If they are at that point, I suggest stepping through your code one line at a time (using F8) and monitoring what is happening on the sheet with each step, to see where things are going off track.

 

[JLGWhiz]

Using the R1C1 cell designation can be misleading and cause unexpected results if not properly applied. In vba it refernces from the base range, which in this case is A1, so it counts columns starting with column B as 1 and rows start with row 2 as 1. If you are not careful you can reference the wrong row or column and get unexpected results. For instance your 'lngRows' variable could be returning a number less than desired.