VB - Ask User to Select Sheet Then Input That Sheet Name Into Formulas

[Cat129]

Hey,

I have an existing document with information in, each sheet is named by date that it gets from a cell within that sheet.

I have written code to update from a text file, but where I am stuck is I need the user to select what sheet they want the lookups to work from. I can't add the sheet name into the vb because it will be forever changing.

I found this from googling but dont understand how I implement this into my code

Dim desiredSheetName As String
desiredSheetName = Application.InputBox("Select Any Cell From The Sheet You Want To Lookup", "Sheet Lookup", Type:=8).Worksheet.Name
Debug.Print desiredSheetName

What I basically want is to add the sheet name the user defines into this code

"=IFNA(IFNA(IF(VLOOKUP(RC[-2],'30th May'!C[-2],1,FALSE)=RC[-2],""No Change"",""""),IF(VLOOKUP(RC[-1],'30th May'!C[-1],1,FALSE)='Tue Jun  4'!RC[-1],""Revision Change"","""")),""New UA"")"

Help greatly appreciated
Cat

 

[tyija1995]

Hey,

Your code is referencing 2 different worksheets currently, do you want both to point at the worksheet defined by the user via the input box?

 

[Fluff]

If it's the 30th May sheet you want to be a variable try

   Dim ShtNm As String
   ShtNm = Application.InputBox("Select Any Cell From The Sheet You Want To Lookup", "Sheet Lookup", Type:=8).Worksheet.name
   Range("A1:A20").FormulaR1C1 = "=IFNA(IFNA(IF(VLOOKUP(RC[-2],'" & ShtNm & "'!C[-2],1,FALSE)=RC[-2],""No Change"",""""),IF(VLOOKUP(RC[-1],'" & ShtNm & "'!C[-1],1,FALSE)='Tue Jun  4'!RC[-1],""Revision Change"","""")),""New UA"")"

 

[Fluff]

@tyija1995
Please do not quote entire posts as it just clutters up the thread.
Thanks

 

[Cat129]

If it's the 30th May sheet you want to be a variable try

   Dim ShtNm As String
   ShtNm = Application.InputBox("Select Any Cell From The Sheet You Want To Lookup", "Sheet Lookup", Type:=8).Worksheet.name
   Range("A1:A20").FormulaR1C1 = "=IFNA(IFNA(IF(VLOOKUP(RC[-2],'" & ShtNm & "'!C[-2],1,FALSE)=RC[-2],""No Change"",""""),IF(VLOOKUP(RC[-1],'" & ShtNm & "'!C[-1],1,FALSE)='Tue Jun  4'!RC[-1],""Revision Change"","""")),""New UA"")"

Perfection! Thank You

 

[Fluff]

You're welcome & thanks for the feedback