PDA

View Full Version : VBA code for entering a formula not as expected



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

p45cal
02-03-2016, 07:10 AM
You're trying to put a formula in row 7 which has a relative reference to a row R[-7] 7 rows above it (albeit on a different sheet - it's still a relative reference) and this is row 0 which doesn't exist, so this wraps round to the bottom cell of the sheet.
The only line you need for all the formulae in column F of the INPUT sheet is:

Sheets("INPUT").Range("F7:F" & LastRow).FormulaR1C1 = "=IF(MID('TXT Data'!R[-6]C[-3],1,1)=""1"",CONCATENATE((MID('TXT Data'!R[-6]C[-3],3,5)),"" "",""Comdty""),CONCATENATE((MID('TXT Data'!R[-6]C[-3],3,5)),"" "",""Index""))"