Riaaz66
02-03-2016, 06:23 AM
Hi,
Below, I have this peace of VBA code and the result I expect is that in Sheet "Input" Cell F7 the formula below is entered:
=IF(MID('TXT Data'!C1;1;1)="1";CONCATENATE((MID('TXT Data'!C1;3;5));" ";"Comdty");CONCATENATE((MID('TXT Data'!C1;3;5));" ";"Index"))
However, somewhere in the code it gives this result:
=IF(MID('TXT Data'!C1048576;1;1)="1";CONCATENATE((MID('TXT Data'!C1048576;3;5));" ";"Comdty");CONCATENATE((MID('TXT Data'!C1048576;3;5));" ";"Index"))
Note the underlined and bold mark.
This is the VBA code:
LastRow = Sheets("TXT Data").Range("A" & Rows.Count).End(xlUp).Row
Sheets("TXT Data").Range("C1").FormulaR1C1 = "=CONCATENATE(RC[-2],"","",RC[-1])"
Sheets("TXT Data").Range("C1:C" & LastRow).Formula = "=CONCATENATE(RC[-2],"","",RC[-1])"
Sheets("TXT Data").Range("A1:C" & LastRow).RemoveDuplicates Columns:=3, Header:=xlNo
Columns("C:C").Select
Selection.ColumnWidth = 9.29
LastRow = Sheets("TXT Data").Range("C" & Rows.Count).End(xlUp).Row
Sheets("INPUT").Range("F7").FormulaR1C1 = "=IF(MID('TXT Data'!R[-7]C[-3],1,1)=""1"",CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Comdty""),CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Index""))"
Sheets("INPUT").Range("F7:F" & LastRow).Formula = "=IF(MID('TXT Data'!R[-7]C[-3],1,1)=""1"",CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Comdty""),CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Index""))"[/FONT][/SIZE]
My kind request is to show me where I go wrong and how to correct it.
Regards,
Riaaz
Below, I have this peace of VBA code and the result I expect is that in Sheet "Input" Cell F7 the formula below is entered:
=IF(MID('TXT Data'!C1;1;1)="1";CONCATENATE((MID('TXT Data'!C1;3;5));" ";"Comdty");CONCATENATE((MID('TXT Data'!C1;3;5));" ";"Index"))
However, somewhere in the code it gives this result:
=IF(MID('TXT Data'!C1048576;1;1)="1";CONCATENATE((MID('TXT Data'!C1048576;3;5));" ";"Comdty");CONCATENATE((MID('TXT Data'!C1048576;3;5));" ";"Index"))
Note the underlined and bold mark.
This is the VBA code:
LastRow = Sheets("TXT Data").Range("A" & Rows.Count).End(xlUp).Row
Sheets("TXT Data").Range("C1").FormulaR1C1 = "=CONCATENATE(RC[-2],"","",RC[-1])"
Sheets("TXT Data").Range("C1:C" & LastRow).Formula = "=CONCATENATE(RC[-2],"","",RC[-1])"
Sheets("TXT Data").Range("A1:C" & LastRow).RemoveDuplicates Columns:=3, Header:=xlNo
Columns("C:C").Select
Selection.ColumnWidth = 9.29
LastRow = Sheets("TXT Data").Range("C" & Rows.Count).End(xlUp).Row
Sheets("INPUT").Range("F7").FormulaR1C1 = "=IF(MID('TXT Data'!R[-7]C[-3],1,1)=""1"",CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Comdty""),CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Index""))"
Sheets("INPUT").Range("F7:F" & LastRow).Formula = "=IF(MID('TXT Data'!R[-7]C[-3],1,1)=""1"",CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Comdty""),CONCATENATE((MID('TXT Data'!R[-7]C[-3],3,5)),"" "",""Index""))"[/FONT][/SIZE]
My kind request is to show me where I go wrong and how to correct it.
Regards,
Riaaz