Referencing Sheets in VBA Frormulas

mgao77 · Oct 13, 2013

Hi All,

Im looking for a way to name and reference sheets to use in formulas within VBA. for example, im using a a vlookup within the code referencing specific sheets. I need to find ways to tell excel to select those sheets in specific regardless of their name or location. The reason for this requirement is to prevent errors month over month when people accidentally name the sheet something different or place it in the wrong place in the workbook..

My Code currently looks like this

Code:

Sheets("Empower -->").Select
    ActiveSheet.Next.Select
    Range("BG2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,7,FALSE),"" "")"
    Range("BG2").Select
    Selection.AutoFill Destination:=Range("BG2:BG2364")
    Range("BG2:BG2364").Select
    Range("BG2").Select
    Selection.Copy
    Range("BH2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,8,FALSE),"" "")"
    Range("BH2").Select
    Selection.AutoFill Destination:=Range("BH2:BH2364")
    Range("BH2:BH2364").Select
    Range("BH2").Select
    Selection.Copy
    Range("BI2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,13,FALSE),"" "")"
    Range("BI2").Select
    Selection.AutoFill Destination:=Range("BI2:BI2364")
    Range("BI2:BI2364").Select
    Range("BI2").Select
    Selection.Copy
    Range("BJ2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,'Calib_2nd Last'!R1C4:R7432C18,13,FALSE),"" "")"
    Range("BJ2").Select
    Selection.AutoFill Destination:=Range("BJ2:BJ2364")
    Range("BJ2:BJ2364").Select
    Range("BJ2").Select
    Selection.Copy
    Range("BK2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC1,'Calib_3rd Last'!R1C4:R7432C18,13,FALSE),"" "")"
    Range("BK2").Select
    Selection.AutoFill Destination:=Range("BK2:BK2364")
    Range("BK2:BK2364").Select
    Range("BL2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],1)"
    Range("BL2").Select
    Selection.AutoFill Destination:=Range("BL2:BL2364")
    Range("BL2:BL2364").Select
    Range("BL2").Select
    Selection.Copy
    Range("BM2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("BM2:BM2364")
    Range("BM2:BM2364").Select
    Range("BM2").Select
    Selection.Copy
    Range("BN2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("BN2:BN2364")
    Range("BN2:BN2364").Select
    Range("BP2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-7],1)"
    Range("BP2").Select
    Selection.AutoFill Destination:=Range("BP2:BP2364")
    Range("BP2:BP2364").Select
    Range("BP2").Select
    Selection.Copy
    Range("BQ2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("BQ2:BQ2364")
    Range("BQ2:BQ2364").Select
    Range("BQ2").Select
    Selection.Copy
    Range("BR2").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll ToRight:=1
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("BR2:BR2364")
    Range("BR2:BR2364").Select
    Range("A1").Select
   
    Range("A1:BZ7001").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
   
    Selection.End(xlUp).Select
    Range("BX1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("BX1").Select
    Selection.Copy
    Range("BP2:BR2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("BX1").Select
    Selection.ClearContents
 
End Sub

instead of the sheet reference "RC1,'Calib_3rd Last'" I would rather just call it latest and have the formulas reference it regardless if its named something else or its location or if it gets deleted or erased.

Any ideas? Im getting desperate.

Thanks guys!

NARAYANK991 · Oct 13, 2013

Hi ,

Do you think this will help ?

http://datapigtechnologies.com/blog...ba-errors-caused-by-changing-worksheet-names/

Narayan

Smallman · Oct 13, 2013

Hi

The way I use to address this issue is use the sheet reference number. This is best practice. So I will try to never address the sheet name directly. For example

Code:

sheet1.[a1].copy sheet2.[a1]

Now if the sheet name changes I never have to worry. Doing it like this shows you understand the sheet name can change and it will stop any bug out issues in relation to this issue. I see too many coders refer directly to the sheet name, which can be problematic. It gets more difficult when the sheet is deleted.

Now onto your code I believe we can make some efficiencies if you come on the journey. I have not been able to test the below code as I am flying blind without a file but I believe the below replaces your code above.

I have shortened the ranges at the start for testing purposes.

Code:

Option Explicit
Sub newone()
 
    Range("BG2:BG23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,7,0),"" "")"
    Range("BH2:BH23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,8,0),"" "")"
    Range("BI2:BI23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,13,0),"" "")"
    Range("BI2:BI23") = "=IFERROR(VLOOKUP(RC1,'Calib_2nd Last'!R1C4:R7432C18,13,0),"" "")"
    Range("Bk2:Bk23") = "=IFERROR(VLOOKUP(RC1,'Calib_3rd Last'!R1C4:R7432C18,13,0),"" "")"
    Range("Bl2:BN23") = "=RIGHT(RC[-3],1)"
    Range("Bp2:Br23") = "=LEFT(RC[-7],1)"
 
    [a1].CurrentRegion.Value = [a1].CurrentRegion.Value
    [bx1] = 1
    [bx1].Copy
    Range("BP2:BR23").PasteSpecial 12, 3
    [bx1].ClearContents
End Sub

If you want to post a file that will help as I will be able to nail it down. Selecting things in XL is usually not necessary, slows the code and adds additional unnecessary lines making the code harder to read.

Take it easy

Smallman

mgao77 · Oct 13, 2013

NARAYANK991 said:
Hi ,

Do you think this will help ?

http://datapigtechnologies.com/blog...ba-errors-caused-by-changing-worksheet-names/

Narayan

Hi Narayany,

I tried doing that earlier, but for some reason, the vlookup didnt recognize the sheet when I tried it. Im not sure if this works with formulas

Hui · Oct 13, 2013

MGAO77

is in normally best practice to define Worksbook/Worksheet names as objects
and then reference them as required

Dim ws As Worksheet
Set ws = Sheets("Sheet1")

then use ws.Range(... as required

This way you have always defined which worksheet is referenced and there is no ambiguity to you or VBA

Smallman · Oct 13, 2013

Hi Hui

I see this one a bit differently. I would be inclined to use;

Code:

Dim ws As Worksheet
Set ws = Sheet1

Where the sheet object number is used.

This has the added advantage when the sheet name is changed, as happens from time to time, the code does not need to change and will not bug out. If the sheet is deleted... well then we are both gonski.

Happy to hear what anyone else thinks on the subject.

Take care

Smallman

mgao77 · Oct 13, 2013

Smallman said:
Hi

The way I use to address this issue is use the sheet reference number. This is best practice. So I will try to never address the sheet name directly. For example

Code:

sheet1.[a1].copy sheet2.[a1]

Now if the sheet name changes I never have to worry. Doing it like this shows you understand the sheet name can change and it will stop any bug out issues in relation to this issue. I see too many coders refer directly to the sheet name, which can be problematic. It gets more difficult when the sheet is deleted.

Now onto your code I believe we can make some efficiencies if you come on the journey. I have not been able to test the below code as I am flying blind without a file but I believe the below replaces your code above.

I have shortened the ranges at the start for testing purposes.

Code:

Option Explicit Sub newone() Range("BG2:BG23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,7,0),"" "")" Range("BH2:BH23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,8,0),"" "")" Range("BI2:BI23") = "=IFERROR(VLOOKUP(RC1,Calib_Last!R1C4:R7432C18,13,0),"" "")" Range("BI2:BI23") = "=IFERROR(VLOOKUP(RC1,'Calib_2nd Last'!R1C4:R7432C18,13,0),"" "")" Range("Bk2:Bk23") = "=IFERROR(VLOOKUP(RC1,'Calib_3rd Last'!R1C4:R7432C18,13,0),"" "")" Range("Bl2:BN23") = "=RIGHT(RC[-3],1)" Range("Bp2:Br23") = "=LEFT(RC[-7],1)" [a1].CurrentRegion.Value = [a1].CurrentRegion.Value [bx1] = 1 [bx1].Copy Range("BP2:BR23").PasteSpecial 12, 3 [bx1].ClearContents End Sub

If you want to post a file that will help as I will be able to nail it down. Selecting things in XL is usually not necessary, slows the code and adds additional unnecessary lines making the code harder to read.

Take it easy

Smallman[/quote

Thanks Smallman, I cannot upload the file as its too large for forum. And btw, the way our model works is that someone will go in an delete the sheets every month and replace with a new one, thats why need to reference a specific area in the workbook instead of the sheet name.. For example, in my workbook, anything to the right a specific tab.. Is this possible?

mgao77 · Oct 13, 2013

Even naming the worksheet is giving me issues when I try to use it in the vlookup:

can anyone see the error is this code?

Code:

Sub MakeName_Worksheet()
 
  Dim Late As Worksheet
    Set Late = Sheet2
 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-10]C1,sheet2!R1C1:R5C2,2,FALSE)"
    Range("B12").Select
End Sub

Sorry to keep blasting you guys

NARAYANK991 · Oct 13, 2013

Hi ,

Is there any reason to use formulaR1C1 instead of just Formula ?

If you specify the exact formula that you would normally use within a worksheet cell to get your result , we can then think of implementing the same in VBA.

Narayan

mgao77 · Oct 13, 2013

It wasnt my intention to use the R1C1 format, excel just records it in that fashion when im recording macros- i am no VBA expert by any means, just started learning 2 weeks ago

The formula would look something like this:

=VLOOKUP($B2,Calib_Last!$A$1:$R$7432,7,FALSE)

but would need to look across sheets that will be getting and replaced every month..

i just need the sheet reference to be a specific location within the workbook so that if the name changes or location changes or if it is deleted, it can still be found

Thanks!

Smallman · Oct 13, 2013

The Sheet being Deleted part is a bit of a problem. You would have to trap the location if that is the only thing you can be certain of.

Give this a crack. Put the sheet name in a cell like I have. I will include a file to show workings. This is just as an example but you should get the gist.

Code:

Option Explicit
 
Sub GoSki()
Dim sh As String
sh = [f1]
    [b1].Formula = "=VLOOKUP(A1," & sh & "!A1:B10, 2, 0)"
End Sub

Take care

Smallman

NARAYANK991 · Oct 13, 2013

Hi ,

Thanks for the clarification ; now for the bad news !

The formula you have posted is a straightforward Excel worksheet formula ; however , deletion is one operation to which Excel cannot respond , since if the worksheet is deleted , how is it possible to recover from this action ?

Location change is not an issue since Excel itself never bases anything on a worksheet's location ; if your code is doing so , you must re-consider why you need to use this kind of logic.

Renaming is not an issue , since Excel automatically changes the formulae to refer to the new name ; again this is possible if it is a formula in a worksheet cell ; if the same formula is used in VBA , Excel is not going to modify the code to take into account the new name ; this is the reason to refer to the underlying name , and not the name which is displayed on the sheet tabs in the Worksheet view.

If you go into the VBE , click on any sheet name , click on the View menu option , and select the Properties window , you can change the sheet name to what ever you want , and use this name in your code ; this name will not be displayed in the sheet tabs , and the user can change the tabbed name to anything , and your code will not need to be changed.

Narayan

mgao77 · Oct 13, 2013

Thanks Narayan,

I tried to do that, but, as you can see from the attached, Its still not working- excel tries to locate the reference from an outside file

Am i Missing anything when referencing this sheet?

NARAYANK991 · Oct 13, 2013

Hi ,

Try this out and see if it works :

Code:

Public Sub temp()
      Dim x As Range
 
      Set x = Old_Calib.Range("$A$1:$R$7432")
 
      Sheet1.Activate
      ActiveCell.Formula = "=VLOOKUP($B2," & Old_Calib.Name & "!" & x.Address & ",7,FALSE)"
End Sub

Sheet1 is a different sheet , on which the formula is being entered.

Narayan

Referencing Sheets in VBA Frormulas

mgao77

New Member

NARAYANK991

Excel Ninja

Smallman

Excel Ninja

mgao77

New Member

Hui

Excel Ninja

Smallman

Excel Ninja

mgao77

New Member

mgao77

New Member

NARAYANK991

Excel Ninja

mgao77

New Member

Smallman

Excel Ninja

Attachments

NARAYANK991

Excel Ninja

mgao77

New Member

Attachments

NARAYANK991

Excel Ninja