none
index Reference letter change when i insert a column in refrence sheet RRS feed

  • Question

  • hi guys,

    i have an issue where the following index letter $E7$:$E$100000 changes whenever i enter a call to refrence sheet "Raw Data for PR Dist"

    Code:
    =IF(INDEX('Raw Data for PR Dist'!$E$7:$E$100000,MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0))="","",INDEX('Raw Data for PR Dist'!$E$7:$E$100000,MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0)))

    such as when i run the vba the first time it will insert a column in the refrence sheet.

    every time i run the Macro the same thing happen, thus $E7$:$E$100000 becomes $F7$:$F$100000 then $G7$:$G$100000...etc

    how can i lock $E7$:$E$100000 to NOT change whenever i run the Macro ?

    i saw something about indirect but am not sure if it will work or if there is a better solution.

    THank you
    Friday, August 16, 2019 3:56 PM

All replies

  • You can indeed use the INDIRECT function for this:

    =IF(INDEX(INDIRECT("'Raw Data for PR Dist'!$E$7:$E$100000"),MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0))="","",INDEX(INDIRECT("'Raw Data for PR Dist'!$E$7:$E$100000"),MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0)))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, August 16, 2019 9:17 PM
  • Thank you, i have another issue now.

    I have a code that has INDEX then MATCH in it

    it will paste a formula on a cell till ( THE LAST DATA EXISTING IN THAT SHEET even if there is a blank and then a value after ) using a reference column A.

    this is the code "

    Selection.FormulaArray = IF(INDEX('Raw Data for PR Dist'!$C$7:$C$100000,MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0))="","",INDEX('Raw Data for PR Dist'!$C$7:$C$100000,MATCH(1,(A3='Raw Data for PR Dist'!$A$7:$A$100000)*(B3='Raw Data for PR Dist'!$B$7:$B$100000),0))).Select

    then copy it to the required cell as per array

    am getting an error due to array having max 244 charchters, error 1004

    what is the way around using this code in VBA ?

    Saturday, August 17, 2019 9:37 AM
  • The easiest workaround is to shorten the name of the Raw Data for PR Dist sheet, for example to Raw Data.

    Apart from that, you must double all double quotes within a quoted string in VBA, and you cannot use Select after a formula.

    If you rename the sheet, the following should work:

    Selection.FormulaArray = "=IF(INDEX('Raw Data'!$C$7:$C$100000,MATCH(1,(A3='Raw Data'!$A$7:$A$100000)*(B3='Raw Data'!$B$7:$B$100000),0))="""","""",INDEX('Raw Data'!$C$7:$C$100000,MATCH(1,(A3='Raw Data'!$A$7:$A$100000)*(B3='Raw Data'!$B$7:$B$100000),0)))"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, August 17, 2019 10:38 AM