You are here:

Excel/How to change the cell reference in this macro code

Advertisement


Question
QUESTION: Hi Bob ! Hope you are fine ! Glad to send a question again.
Below is the code that were provided by you and its working fine...

Sub Extractor()
  Set curr = ActiveSheet
  Set rg = Application.InputBox("Select columns you want - if they're not together, hold the ctrl key down", Type:=8)
  Set rg = Intersect(Rows(1), rg.EntireColumn)
  rg.Copy
  Sheets.Add
  Range("C1").PasteSpecial
  x = Range("IV1").End(xlToLeft).Column - 2
  Range("C2").Resize(100, x).FormulaR1C1 = "=IFERROR(INDEX(Sheet1!R1:R1048574,MATCH(RC2,Sheet1!C2,0),MATCH(R1C,Sheet1!R1,0)),"""")"
End Sub

But can you please help me as i want to change the query field like for example if i want to get the report from different header from which i can prepare report by entering that value... please give me a way so that i can use this code for different works.

Thanks & Regards
Amit

ANSWER: I don't understand "...get the report from different header..."
or "... by entering that value... "
or "...use this code for different works."



---------- FOLLOW-UP ----------

QUESTION: when I enter any value in "column b" it matches it from "sheet1" and if finds that value in "sheet1" populates all related information regarding the value or id entered in "column b". Now I want to modify this code so that I can change my search value for example...

There is columns a to az in "sheet1" when run this macro it asks "Select columns you want - if they're not together, hold the ctrl key down" suppose i am selecting the columns c,d,f,h,k,l,m then it copy these columns with headers and paste special in an other new worksheet. when I enter any id no. in column "b" in new sheet that were made after macro runs it will match the ID no. in column b in "sheet1".

What i want is to modify this macro to work withan other column as a search ID like if there is an other id in column "e" then how can i modify this code to work for me.

Thanks & Regards

ANSWER: Do you want the macro to prompt (ask) you to supply the column which contains the ID? Or is the macro to be changed to look in column E instead of B?

the part of the code near the bottom, ..MATCH(RC2,Sheet1!C2,0),... it what needs changing. The RC2 is column B. If you want column E, then RC5 would do. If you want column Z, then RC26 would do. If you wanted it to be variable where you're prompted, let me know because that's a bit more involved.

---------- FOLLOW-UP ----------

QUESTION: I want A macro to prompt (ask) me to supply the column which contains the ID?

Answer
Sub Extractor()
  Set curr = ActiveSheet
  Set rg = Application.InputBox("Select columns you want - if they're not together, hold the ctrl key down", Type:=8)
  Set rg = Intersect(Rows(1), rg.EntireColumn)
  col = InputBox("Enter the column that contains the ID (like B, E, AA, etc)")
  col = Range(col & "1").Column
  rg.Copy
  Sheets.Add
  Range("C1").PasteSpecial
  x = Range("IV1").End(xlToLeft).Column - 2
  Range("C2").Resize(100, x).FormulaR1C1 = "=IFERROR(INDEX(Sheet1!R1:R1048574,MATCH(RC" & col & ",Sheet1!C2,0),MATCH(R1C,Sheet1!R1,0)),"""")"
End Sub
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.