Referencing Defined Worksheet Variable In Formula In VBA?

Oct 19, 2012

I have created a userform that once the user clicks the okay button, the information is transferred to a worksheet template named 'PO Template', copied, and renamed. That portion works great. I then have code for a summary worksheet named 'PO Log' that finds the next empty row on the log and enters a formula referencing back to the newly created 'PO Form' (note - 'PO Form' is a variable as the actual worksheet name changes with the user input). The formula on the 'PO Log' references the 'PO Form' literally, which gives me a REF! error because there is no sheet actually named that. I've tried defining POForm in the code as a string so I can enter it into the formula code but I get an error because it's already defined as a variable. I've entered the sub routine for clicking the okay button below:

Sub OkayButton_Click()
'Make PO Template Active
Sheets("PO Template").Activate


View 2 Replies


Macro To Find Variable User Defined Cell In Worksheet

Nov 7, 2013

I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.

View 6 Replies View Related

Using A Defined Variable In A Formula

Mar 7, 2008

I have a macro which defines a workbook selected by the user as the name ServicingInfoFile.

The macro then proceeds to write vlookup formulas in another workbook to pull data from the ServicingInfoFile workbook.

Only problem is that the code errors out at the vlookup formulas every time.

Can I not use a Variable in a formula?

Is it just my syntax?

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'" & ServicingInfoBook & "'!C1:C2,2,FALSE)"

View 9 Replies View Related

Referencing Worksheet In Formula?

Dec 8, 2011

I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!$C:$O,10,0)

I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!$C:$O,10,0)

View 9 Replies View Related

Formula To Referencing Future Worksheet

Aug 19, 2009

Reference a worksheet that has not yet been created, avoiding the #REF! result?

The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.

View 9 Replies View Related

Generic Formula Referencing Prior Tab/worksheet

Jan 20, 2010

The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.

For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2

That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.

Is there a way to reference the prior worksheet without using it's specific name?
=C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.

View 7 Replies View Related

Copy/paste Formula Referencing Different Worksheet On Each Line

May 18, 2009

I have a workbook with ~80 sheets. One sheet is a summary sheet containing data from each of the other sheets. The formula to display the data is pretty easy, but I need a function to copy the formula but increment the worksheet reference in each cell.

For example:
the formula in one cell is ='17'!$AI$6
The next cell should be ='18'!$AI$6
and so on...

I have about 12 columns like this, so I really don't want to edit each of them individually.

View 5 Replies View Related

Excel 2007 :: Syntax For Referencing Worksheet In A Formula

Apr 19, 2012

I am using Excel 2007 in Windows XP. I am trying to change the worksheet referenced in a formula from the label of the worksheet to the index of the sheet. I want to use this macro in multiple workbooks, and the relevant sheets have different names but are all in the same position.

I used "record macro" to get the structure of my code. It gave me the line:

ActiveCell.FormulaR1C1 = "=SUM('8Nov11'!R[13]C[-4]:R[108]C[-4])"

I want to change that '8Nov11' to the 3rd worksheet from the left, so something like:

ActiveCell.FormulaR1C1 = "=SUM(ThisWorkbook.Sheets(3)!R[13]C[-4]:R[108]C[-4])"

I can't get this to work, and I can't find the syntax for this anywhere. I've tried with and without quotes, exclamation points, changing to A1 notation and using "Range," "Sheets," "Worksheets," "Item(3)", and many other combinations, but haven't gotten it right.

Most of the errors I get are "Runtime Error 1004: Application-defined or object-defined error."

View 3 Replies View Related

Set Bold To Font On Another Worksheet: Application Defined Or Object Defined Error

Aug 25, 2006

Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)

the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.

View 5 Replies View Related

String Variable To Call Defined Range Variable

Nov 10, 2006

Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?

I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.

View 5 Replies View Related

Application-defined Or Object-defined Error On Formula Entry

Aug 12, 2009

I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).

View 2 Replies View Related

Copy Worksheet In Workbook With All Formulas On New Worksheet Referencing Previous Worksheet

Apr 21, 2012

I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.

How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?

View 1 Replies View Related

Variable Not Defined

Jun 19, 2008

I have this structure:

Sub macro()

Dim columnSheet2 As range

With ThisWorkbook
Set valueCell = .Sheets("sheet1").range("B2")
Set timeCell = .Sheets("sheet1").range("J2")
End With

If timeCell.Text "#N/A" Then

myHour = Hour(timeCell)
myMinute = Minute(timeCell)

If (myHour = 5) Then
If (myMinute = 0) Then
Set columnSheet2 = A65536


if I run it it gives me an error here

Set columnSheet2 = A65536

saying variable not defined, why?

View 10 Replies View Related

Referencing Range With Variable

Sep 11, 2009

need to solve the problems in this

View 2 Replies View Related

Variable Data Referencing

Apr 23, 2009

I have a word table(s) which I need to paste into excel, its a quotation sheet and this document always has the same basic format. I then need to copy across specification items to another sheet between 2 points in column A ie between points TEXT1 (A25) and TEXT2(A40) so cells A26:A39 in this instance but this can be between 1 or 100 items.

Text 2 is the start of a costing section and wouldn't be transferred.

What i am looking to do is write a formula which will look in sheet1 at column A and find "TEXT 1" at A25 then copy items across until it comes to "TEXT2" at A40 but is flexible enough to cope with various numbers of items.

View 9 Replies View Related

Excel 2010 :: Use Variable Instead Of Range In Worksheet Formula

Dec 3, 2013


I am using the above formula in my code with two Named Ranges

Set Rng3 = Range("_NamedRng1").Offset(1, 0)


I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.

I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49

View 2 Replies View Related

Getting Variable Not Defined Error

Sep 23, 2013

I'm getting a Variable Not Defined error

in my workbook_open I have the following code

Dim workround As Integer
workround = 0
in one of my sheets worksheet_change I have

If workround = 0 then

when I get to this I get the Variable not defined error. I am using this to bypass some code that does not always need to be run (it slows the workbook down). So I also have another piece of code that toggles workround from 0 to 1.

I have a feeling this error has something to do with Option Explicit. But My limited understanding is obviously insufficient to know what is happening or how to fix it. As I believe that I have defined the variable within the workbook_open code.

View 3 Replies View Related

Check If A Variable Has Been Defined

Oct 20, 2008

I have some code which defines a variable 'F'

View 9 Replies View Related

Variable Not Defined In Sub Heading

Jun 13, 2008

Sub addInterestingDataPoints(myChart As chart)
'code goes here
End Sub

The above code is causing a Compile Error: variable not defined (highlights the sub heading line).

View 2 Replies View Related

Referencing A Value In Variable Length Columns.

Jan 22, 2006

I have a SS I would like to reference a cell in a column which changes in
length. In this case, I would like to reference the most current date in a

Is there a simple method for doing this, as the data will routinely be
different lengths in that column.

Further, there is Data on the Horizontal that I would like to reference to
another cell in another sheet that would correspond to this date. Can anyone
give any suggestions as to the best way to accomdate this?

View 11 Replies View Related

Referencing Variable Workbook Tab In VLookup?

Nov 29, 2012

I have a challenge with Vlookups!

I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02

I then have another workbook which has employees in column B and the client code in C3.

The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.

I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.

View 3 Replies View Related

Conditionally Hiding / Unhiding Rows In One Worksheet By Referencing Cell In Different Worksheet

May 3, 2013

coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.

As a simplified example:

Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!

So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.

I was thinking of using something like this:

Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Rows("20:30").EntireRow.Hidden = False
End If
End If

I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.

View 5 Replies View Related

Compile Error - Variable Not Defined

Aug 12, 2014

New to VBA and just trying to make some edits to some existing code. I have basically copied a pre-existing form and module and changed names from "Appendix" to "Drawing" as I am trying to replicate what the piece of code already produces for a table of appendices, for my drawings.

However, when I try and run the form I get Compile error: Variable not defined with lstDrawings highlighted.

[Code] ...........

Is this something that should be defined in my global module which I am missing?

View 3 Replies View Related

Compile Error - Variable Not Defined

Jan 21, 2012

The following code has been used previously to enter data from a userform to a worksheet without a problem. However, since I added some new bits of code I am getting a compile error with the message variable not defined.

Here is part of what I have so far and the bit that is highlighted after the error comes up is the 'Set ws' line;

Private Sub CommandButton1_Click()
Worksheets("Duties").Range("C5") = txtdate
Worksheets("Duties").Range("H5") = txtarea
Worksheets("Duties").Range("N5") = txttea
Set ws = Worksheets("Duties")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

The code I have recently added:

Private Sub UserForm_Initialize()
Set wsRes = Worksheets("Resources")
With wsRes
.Range("B1", .Range("B" & Rows.Count).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("L1"), True
With .Range("L2", .Range("L" & Rows.Count).End(xlUp))
cboTeam.List = .Value

[Code] .......

View 8 Replies View Related

UserForm (Variable Not Defined) Error

Jul 15, 2013

I am creating a UserForm to get macro running options from the user. The Workbook__Open calls the macro which shows the UserForm right after the variables are defined. Is there a way to avoid the compile error?

07.15.2013-14.15.54 - Bermex's library

View 1 Replies View Related

VBA With Compile Error: Variable Not Defined

May 27, 2008

I've used Excel for many years, but finally am learning VBA, using Excel Progamming for Dummies by John Walkenbach. Excel is version 2000.

I've searched extensively on Google and on the MrExcel site for the solution to my problem, but still haven't found it . Any direction would be most appreciated.

I'm trying to execute the examples in the book, and have gotten the error message "Compile error: Variable not defined" with "MyString =" highlighted with several of the examples. Could someone please tell me where I'm going astray?

Following is the latest subroutine I typed into the module, from page 124:

Option Explicit

Sub GetLength()
MyString = "Hello World"
StringLength = Len(MyString)
MsgBox StringLength
End Sub

View 9 Replies View Related

Compile Error - Variable Not Defined

May 6, 2009

I keep getting 'compile error - variable not defined' in the following

Sub LockIt()
TextBox1 = ""
End Sub

View 9 Replies View Related

Getting A Compile Error Variable Not Defined

Jan 13, 2010

It works as a regular Module but when I put it into a "Private Sub" for a button its not working.

Basically I have a master sheet that I create Tests from. I push a button and it creates a Test for others to take. I want the macro to transfer from the Master to these so when they are done I can press a button and upload the answers to a tracking sheet.

It errors on the strSecondFile

Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim NAME As String

'strFirstFile = ActiveWorkbook
strSecondFile = Application.GetOpenFilename
Set wbk = ActiveWorkbook 'Workbooks.Open(strFirstFile)
NAME = Range("A6").Value

View 9 Replies View Related

Compile Error: Variable Not Defined

Jul 18, 2006

I came across a weird situation using a standard print macro. One of my users could not use the print macro due to a compile error. The row that was highlighted was

.PrintErrors = xlPrintErrorsDisplayed

the error states that the variable is not defined. I removed that line of code and everything prints fine.

I tested this code out on excel 97 and it worked; is this sensitive to OS version? Will leaving this line of code effect anything big?

View 9 Replies View Related

Complie Error: Variable Not Defined

Oct 10, 2006

I keep getting a "complie error: variable not defined" and I am not sure why (the error highlights the first instance of "sh"). Any feedback? I am trying to test a value of a cell for each row and if the row meets the test then it increases the value in another cell by 1.

This is what I have so far...

View 5 Replies View Related

Copyrights 2005-15, All rights reserved