SOLVED

I have couple formulas. I want hard code into vba

%3CLINGO-SUB%20id%3D%22lingo-sub-2778956%22%20slang%3D%22en-US%22%3EI%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778956%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20worksheet%20called%20PTW1%20and%20I%20also%20have%20other%20worksheet%20called%201%3C%2FP%3E%3CP%3EIn%20cell%20D2%20have%20this%20formula%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3DIF('1'!D2%26lt%3B%26gt%3B%22%22%2C'1'!D2%2C%22BWT%22)%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3DIF('1'!G2%26lt%3B%26gt%3B%22%22%2C'1'!G2%2C%22BWT%22)%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20delete%20worksheet%20called%201%20I%20getting%20%23REF%26nbsp%3B%20in%20D2%20%26amp%3B%20G2%20formulas%20before%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20want%20that%20anymore%20I%20delete%201.%20Next%20Year%20I%20create%20new%201%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3DIF(%23REF!D2%26lt%3B%26gt%3B%22%22%2C%23REF!D2%2C%22BWT%22)%20%3DIF(%23REF!G2%26lt%3B%26gt%3B%22%22%2C%23REF!G2%2C%22BWT%22)%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20hard%20code%20vba%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778956%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2779123%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779123%22%20slang%3D%22en-US%22%3EInstead%20of%20hard%20coding%20in%20VBA%20you%20could%20instead%20use%20the%20function%20INDIRECT()%3CBR%20%2F%3E%3DIF(INDIRECT(%22'1'!D2%22)%26lt%3B%26gt%3B%22%22%2CINDIRECT(%22'1'!D2%22)%2C%22BWT%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2779309%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779309%22%20slang%3D%22en-US%22%3E%3CP%3Efunction%20INDIRECT()%3CBR%20%2F%3E%3DIF(INDIRECT(%22'1'!D2%22)%26lt%3B%26gt%3B%22%22%2CINDIRECT(%22'1'!D2%22)%2C%22BWT%22)%3C%2FP%3E%3CP%3E%3DIF(INDIRECT(%22'1'!G2%22)%26lt%3B%26gt%3B%22%22%2CINDIRECT(%22'1'!G2%22)%2C%22BWT%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20I%20do%20for%20G2%3F%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2779785%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2779785%22%20slang%3D%22en-US%22%3Eyes%20that%20is%20what%20I'm%20suggesting%20and%20it%20appears%20that%20it%20is%20working%3F%20Glad%20it%20helped.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2780180%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2780180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20function%20doesn't%20work%20it%20is%20in%20red%20can%20check%20wrong.%20if%20possible%20can%20send%20copy%20of%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2780458%22%20slang%3D%22en-US%22%3ERe%3A%20I%20have%20couple%20formulas.%20I%20want%20hard%20code%20into%20vba%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2780458%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%20works%20for%20me%20(see%20attached).%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have worksheet called PTW1 and I also have other worksheet called 1

In cell D2 have this formula      

 

 

 

 

 

=IF('1'!D2<>"",'1'!D2,"BWT")

 

 

 

 

 

   

 

 

 

 

 

  =IF('1'!G2<>"",'1'!G2,"BWT")

 

 

 

 

 

 

When I delete worksheet called 1 I getting #REF  in D2 & G2 formulas before

 

I do not want #REF anymore when I delete 1. Next Year I create new 1 worksheet.

 

 

 

 

 

=IF(#REF!D2<>"",#REF!D2,"BWT")

=IF(#REF!G2<>"",#REF!G2,"BWT")

 

 

 

 

 

 

 

 

I want hard code vba  have both formulas in code. and basic code 

 

Thanks You

8 Replies
Instead of hard coding in VBA you could instead use the function INDIRECT()
=IF(INDIRECT("'1'!D2")<>"",INDIRECT("'1'!D2"),"BWT")
best response confirmed by sf49ers (Contributor)
Solution

function INDIRECT()
=IF(INDIRECT("'1'!D2")<>"",INDIRECT("'1'!D2"),"BWT")

=IF(INDIRECT("'1'!G2")<>"",INDIRECT("'1'!G2"),"BWT")

 

That I do for G2?


Thanks You

yes that is what I'm suggesting and it appears that it is working? Glad it helped.

@mtarler 

 

The function doesn't work it is in red can check wrong. if possible can send copy of file

 

 

Thanks You

@sf49ers  works for me (see attached).  

@mtarler 

 

I delete worksheet called 1 in PTW1 still saying #REF on your file you  posted

 

 

Thanks You

Did you create a new sheet called 1? Maybe I misunderstood your intentions. I thought you have these formulas that refer to a sheet called "1" but you delete that sheet and create a new sheet that is named "1" and you need those formulas to still work and now use this new sheet "1"? Am i mistaken?

Thanks You for formulas It is working now.