Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Assign Variable To Worksheet Formulas

Split from Copy, Cut, Fill Relative Or Absolute Formulas

Originally Posted by AAE
Welcome to Ozgrid.

Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1

Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.

Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.

See this training page on how to toggle between relative/absolute cell references: [url]

Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.

Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Assign Variable For Next Available Row?
This code finds the second avaliable row from the top of the sheet.

View Replies!   View Related
Assign Array To Variable
My question is about assigning an entire array to a single variable. In this case, I want to assign an entire array to one element of another array.

View Replies!   View Related
How To Assign An Array To A Variable
how to do is the assignment of GoodArray1 to CurrentArrayToUse, i.e. "CurrentArrayToUse = GoodArray1" below:

Global NextArrayToUse()
Global CurrentArrayToUse()
Global PreviousArrayToErase()
Global GoodArray1(), GoodArray2(), [etc]
Global CurrentGuessNumber As Integer

[bunch of code, part of which assigns a number to CurrentGuessNumber, then the following...]

Select Case CurrentGuessNumber
Case 1
CurrentArrayToUse = GoodArray1
NextArrayToUse = GoodArray2
Case 2
CurrentArrayToUse = GoodArray2
NextArrayToUse = GoodArray3
PreviousArrayToErase = GoodArray1
ReDim PreviousArrayToErase(0, 0)

View Replies!   View Related
Assign Range To Variable
I am trying to assign a range of cells to a variable but it isn't working. My code is as follows:

Dim VarRateTable As Range

Private Sub CalcBudgets(rng As String)
' Dim VarRateTable As Range
Dim CurrentRng As Range
Set CurrentRng = Selection
Set VarRateTable = Range(rng)

Private Sub NEWCALC(datarow)
Dim vt As Double

vt = Application.WorksheetFunction.VLookup(Cells(datarow, 4).Value, VarRateTable, 3, 0)

but the Set statement gives me an error: <method 'range'of object '_global' failed>

View Replies!   View Related
Assign Value From Userorm Textbox To Variable?
I have created a userform with 3 textboxes and I want to return the values entered to variables for use in a procedure. I cannot figure out how to return the values.

Behind the command button I am unloading the form so that the calling procedure continues executing but my vars cannot see the controls.

I have tried making them public outside of the calling sub, but nothing. Can anyone point out to me what I'm doing wrong?

View Replies!   View Related
Open Workbook And Assign To Variable
Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:

Sub openfile(FilePath)
Dim myWBk As New Workbook
'Now open Form
End Sub

This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.

View Replies!   View Related
Assign Variable To Different Data Types
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:

1) if the user highlights multiple cells of the desired column
2) enters the letter of the column (up to "z", not case sensitive)
3) enters the number of the column

Sub Button5_Click()
Dim ColumnUsed As Variant
' find number of columns selected
For MyColNum = 1 To Selection.Columns.Count
Cols = Cols + 1
Next MyColNum
'find number of rows selected
For MyRowNum = 1 To Selection.Rows.Count
Rows = Rows + 1
Next MyRowNum..............................

View Replies!   View Related
Assign Variable To Selected Range
What I need is a macro that assigns manually selected cells/ranges (which may or may not be continious) to a Variables.

View Replies!   View Related
Assign Multiple Cell's Values To A Variable
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.

View Replies!   View Related
Assign External Cell Reference To Variable In VBA?
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?

View Replies!   View Related
Assign To A Variable, The Total Number Of Text Entries In Col
I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).

View Replies!   View Related
Assign A Number Value To Text Within Worksheet
I need to count the number of times a value appears in a row and assign a number that tells which occurence of the text it is.
For example

Name Address
Jim 123 Smith Street
Jane 123 Smith Street
Bob 543 Apple Street
Mary 543 Apple Street

I would like a way to insert a column that puts a 1 next to Jim and a 2 next to Jane, a 1 next to Bob and a 2 next to Mary and so on.
Ultimately, I want to sort the list and delete all the twos, thus deleting the duplicate address entry.

View Replies!   View Related
Assign Specific Cell Value Of Worksheet To A Label
I am struggling with working with specific cells from specific sheets. e.g.

I want to make a label display teh value of a specific cell.

(i is the row number of the cell i'm interested in)

'now make labellaptime.caption = the cell (i-1, 3) of sheet1(jules)

'LabelLaptime.Caption = i - 1 'works no problem
'LabelLaptime.Caption = Application.Worksheets(Sheet1).Cells(i, 3) 'laptime
'LabelLaptime.Caption = Application.Worksheets(1).Range("i-1,3").Value
'LabelLaptime.Caption = Application.WorksheetFunction.VLookup(
'LabelLaptime.Caption = Range("c3") 'works no problem
'LabelLaptime.Caption = ThisWorkbook.Worksheets.("sheet1").range("c1").value

I've based all this on the book i'm trying to learn from but it doesn't work and (obviously) i don't know why...

(plus any links to info on how to work with specific cells on specific sheets e.g making a variable = to the value of a cell(x,x) of sheet 3)

View Replies!   View Related
Code To Assign Predefined Number In Separate Worksheet
Excel 2003: I need code that, when an "x" is entered in a cell in the "Activity" worksheet to assign a temporary unit #, it will look for the next available Temporary Unit # in the "Assign" worksheet. Then mark that unit # as "assigned" (by placing an "X" in the column next to it) and copy it to a cell in the "Activity" sheet.

I will be doing the same thing with assigning different types of PO numbers. I figure if I have the code for the Unit #, I can use the same logic for the other assignments, with some modifications, of course.

I've attached a sample workbook.

If I am not considering the most effective way to accomplish what I am trying to do here, I have no ego at all about someone suggesting a better solution.

View Replies!   View Related
Automatically Assign Predefined Unit # In Separate Worksheet
I have a worksheet that logs equipment activities. Equipment may already have a standard unit # assigned to it or it may need a temporary unit # assigned for tracking. In a separate worksheet is a column of predefined temp unit #'s that can be assigned and next it is a column to place an "X" once a unit # has been assigned.

I need to write logic that, when an "X" is entered in Column F (Assign a Unit #) of the Activities worksheet, the Unit # (Column G) will automatically select the next available Temp Unit # in the Assign worksheet...and mark that Unit # as no longer available. Quite frankly, this may be more easily done with VBA, but I am not experienced in that at all. I have attached a sample worksheet showing the format of the unit # to be assigned, etc.

View Replies!   View Related
Create Button & Assign Macro When Worksheet Added
Is there a VBA code that will enable me to create a button and assign a certain macro to it everytime I insert a new worksheet?

how to insert a new worksheet with VBA, what I want is that when I insert that worksheet, there is already a button there with a specific macro(already made) assigned to it.

View Replies!   View Related
Using Vba Variable In Formulas
I am trying to write a macro that will cut the text in a given cell to 16 characters. My code works as long as the text is always in the same cell, but I eventially want to ask the user what cell to use. So I want to replace the “RC[-25]” with my WorkCell variable. I can’t seem to figure out how to put it in there.

Sub test()
Dim SizeOfRange As Integer
Dim WorkCell As String
SizeOfRange = 10
WorkCell = A1
For x = 1 To SizeOfRange
Range("z" & x).Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-25],16)"
Next x
End Sub

View Replies!   View Related
Variable Usage Inside Formulas
In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.

View Replies!   View Related
Count & Summing Formulas Containing More Than One Variable
I am trying to create a formula that will count the number of entries that contain either a name of 'A', 'B' or 'C' and fall within a set date parameter.

I am currently using this formula (which works perfectly well for 1 variable but not for multiple), where column B is my date and column R is my name field.


View Replies!   View Related
Variable Cell Ranges In Formulas
I need to run a formula each month which calculates the ‘product’ for a range of cells in a column, where the cell range will change each month. The starting cell for the range is static, but the range end will always change - additionally, the last valid cell will always be followed by a null value cell. The attached example shows the basic format of my data – a range of values, by row, which will always end with a null value cell. The formula I’m using (to match my example) is: =(PRODUCT(A10:A13) –-> and I want to display the result in cell C10.

While I’ve been able to identify the ‘ending good row’ several ways, I haven’t been able to figure out what to do with this information – I seem be be lost in the translation of OFFSET values to CELL REFERENCE values. Has anyone encountered this specific scenario and found a solution?

View Replies!   View Related
Variable Amount For Global Functions/Formulas
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.

View Replies!   View Related
Assign Userform Name To Variable For Use On Another Userform
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).

The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,

customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text
customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list

way for me reference the specific "Customer" form that has been opened, without calling it by name?

View Replies!   View Related
Conditional Formulas-to Get A Spreadsheet To Automate Calculations Of Unit Costs Based On Variable Packaging Names
I am looking for a way to get a spreadsheet to automate calculations of unit costs based on variable packaging names.

I have a series of packages that are denoted by text phrases. Examples:


For our purposes let's say the package names above will always be in column A. Column B contains the frontline price of a case of product represented by the phrase in column A. Column C will contain the cost per unit of product - this is obtained by dividing column B by the number of each package arrangement that can be found in one case. Most of the time the number of package arrangements per case is denoted by the very first number in the package name (ie, 4/6/12 would be 4). This won't always be the case though (18/12 would be a package arrangement of 1).

I am looking for a way for the spreadsheet to do all of the following and return the results in column C: if the package name contains "4/6/12", divide column B by 4; if the package name contains "2/12/12", divide column B by 2; if the package name contains "6/4/12" divide column B by 6; if the package name contains "18/12", divide column B by 1; if the package name contains "24/12" divide column B by 24.

View Replies!   View Related
Copy Worksheet To New - No Formulas
starting to dabble in the VBA coding, now. I tried finding this solution...but nothing quite the same. I have a spreadsheet with a couple tabs. The main tab is a calculation worksheet that pulls in, and performs various calculations on, data from the other couple tabs. This main tab will be the only one that most of my users will see (query and other data tabs will be hidden.)

What this workbook does is calculate accruals for our consultants, at the end of every month (period.) I am trying to put in code that, when activated by a button, will copy the entire main ("Accrual Worksheet") tab as a new tab, at the end of the a period backup to the calculations on the main tab.

I created the below
Private Sub CommandButton2_Click()
Dim LastSheet As Integer
Dim aSht As Worksheet
Dim Test4 As String
Dim NameXists As Long

If MsgBox("Are You Sure You Wish to Finalize? This will copy sheet, without formulas, as backup.", vbYesNo, "Update Confirm") = vbNo Then
Exit Sub


This seems to work great......EXCEPT (you knew one was coming), I only really want to copy the sheet as data/format only. I don't want the copy tab to have any VBA code, or formulas, on it. I just want a picture image of the "Accrual Worksheet", as reference to the activity for that period. In the following period, the same "Accrual Worksheet" will be used to work on the next month's activities.....and the data on that tab will change. So we need to store the final calculation, for further reference.

View Replies!   View Related
Deleting Worksheet Causes #REF! In Formulas
I have a macro that imports a text file. I then have summary sheets that reference to the sheets with the imported text file details. This is a task I complete every month.

The next month I delete the sheets with the imported text file details before importing current month's details. But when I do that it screws up my references.

Here's an example of a reference with previous months' imported text file details:

= SUM('X'!$I$1:$I$3000)/2+SUM('Y'!$I$1:$I$3000)/2

When I delete the sheets with the imported text file details and import current month's details, my reference suddenly looks like this:


What can I do to prevent the summary sheet from messing up my original references?

View Replies!   View Related
Clone Worksheet Without Formulas
I have a workbook with multiple sheets that contain formulas that I would like to copy to another workbook or new workbook with the same sheet names but without the formulas.

View Replies!   View Related
Insert Worksheet With Formulas
I have a workbook with a worksheet called Summary that has formulas that refer to other worksheets (say A, B, and C) in the same workbook.

What I want to do (with VBA because I have to do this many times) is copy this Summary worksheet into several other workbooks, all of which also have their corresponding worksheets A, B, and C.

I do *not* want the copied Summary worksheet's formulas to refer to the old workbook. If I copy the worksheet by hand, this is what happens.

Instead I want the copied worksheet's formulas to reference the worksheets of the workbook they are now in.

View Replies!   View Related
Entering Duplicate Formulas From Different Worksheet
Basically I have a template from two different worksheets and I am making one sheet the "master sheet" and the other sheet is filled in automatically from the master sheet. I understand how to get this basic function to work so here is my real problem...

I would like my sheet to look like this repeated 2000 times...


Every time I try to drag the formulas down to go 2000 rows it starts doing this...


How do I get this formula to repeat like I have shown in my desired example?

View Replies!   View Related
Macros & Formulas Paste Into A Worksheet
I have a formula which i need to paste into a worksheet, doing this is tedious and boring to say the least, problem is i am not sure how to get the macro to skip blank lines and only insert the formula where there are prices. Each week the worksheet changes so there is no consistent layout.

View Replies!   View Related
Auditing Formulas That Reference Another Worksheet
On choosing Auditing Funtion, Trace Dependants, a small icon representing a spreadsheet ? appears at the end of a dashed line. What does this refer to?

View Replies!   View Related
Forcing Excel To Recaluculate Formulas In Another Worksheet
i would like to put a command button on one Excel sheet that will force the formulas in another Sheet within the same file to be recalculated. I know how to put a command button, and i know some VBA. So can anyone give me a hint how this might work?

View Replies!   View Related
Worksheet Link Search And Fill Formulas
I want to create a spreadsheet table of values that are linked other spreadsheets, a condesnsed version... I am not a total amateur and I understand the world of excel links....

However the problem lies in the fact that the source files change format every day, what i need is a formula for each cell (i know...) that can:

1. Firstly find column and row heading titles by looking for part of the title i.e. for a title like 'Occupation' i need a non case sensitive search for 'Occu'.

2. Once colum and row are found, return the intesecting value.

The formulas are needed so all I will have to do, after this is finished, is update the source files and the relevant numbers will be found automatically.

Im sure this is something that can be done with a clever array formula, setup to recognise column and row intersections based on positve returns on search variables, however this one is just outside my capabilities....

View Replies!   View Related
SUMPRODUCT Formulas Slowing Worksheet Significantly
I have had a workbook that I have been using to track inventory. I use a userform to enter the data for inventory in and out of the system. I recently added two more worksheets to the workbook, each of which has several SUMPRODUCT formulas on them that reference the inventory data.

Since adding these two sheets, when entering data via the userform, it takes a long time (relatively speaking) for the sheet to update with the relevant new data. I have attached a copy of the file, in hopes that someone can show me exactly what is causing the slow-down, and if there is a way that I can get the same results using different programming or formulas that will not be so slow.

The part that is slow is on the "Data" sheet. Click on the green or orange buttons to open the userform. Make a selection in each section, and enter an amount. Clicking on "Enter" will transfer the appropriate information to the worksheet. This is the part that is slow.

View Replies!   View Related
Ranking Program: Edit Formulas Under The Mon-D15 Worksheet In This File
I am trying to edit some formulas Under the Mon-D15 worksheet in this file. The # NUM is where I am having trouble. Basically, what I want to do is only rank the names in C15:C18. I was successful in doing this with C10:C14. But, for some reason it will not work with the lower values.

Question #2
Is there a way to make Columns I:O stay static through Rows 1:24 (width=3)?
I want Rows 25:40 to be different widths (width=2) in Columns I:O.

View Replies!   View Related
Worksheet Name To Variable
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".

Sitenameshort = "_Bmth"

ShtName1 = "Weekly" & Sitenameshort

shtName = ShtName1. Name 'This line doesnt work

Set Nws = Sheets(shtName)

It Errors on the marked line.

View Replies!   View Related
Use Of Worksheet Variable
I'm having difficulty using a worksheet variable with control objects on worksheets.
The following code is successful at getting the value of the text in textbox txtDCN.

Public Sub test()
Dim text As String
text = Worksheets("Master Table").txtDCN.Value
End Sub

The following code gives compile error "Method or data member not found" on the last line of code. I've searched around a bit all over the web but cannot find any examples of this same error. It's obvious that the compiler cannot figure out the reference to the text box but I cannot figure out why for the life of me.

Public Sub test()
Dim ws As Worksheet
Dim text As String
Set ws = Worksheets("Master Table")
text = ws.txtDCN.Value
End Sub

View Replies!   View Related
Copy Variable Range & Copy Formulas From Destination
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.

I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)

What I’m trying to do:
part 1:
I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13
(column I is blank).

The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?

part 2:
As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet

View Replies!   View Related
Using Variable Cell Value As Worksheet Name
I have a macro on a button click that copies and pastes the template worksheet into the same workbook on a dialy basis, i would like to rename the "template" copied file to a unique worksheet name based on a date ie 31_3 (31st march) and so on, the cell that provides the date changes on a daily basis to the next one, however when the script runs it uses the original macro recording date rather than the new one!, the changable cell is AS2.

View Replies!   View Related
Replacing Worksheet;with Variable Name
I'm replacing a specific sheet on a workbook with another one, but I want the users to be able to browse to a file, replace the sheet with the new one and re-establish the links on the replaced sheet with the target workbook. This macro is running out of the workbook that supplies the replacement sheet. The idea is that the user opens this workbook, runs the macro, browses to the file they need to fix and hey presto! I'm doing something stupid with my variable name, I'm sure it's syntax but I don't know what I'm doing wrong. The code is posted below.

Sub Macro1()
Dim ToBeFixed
ToBeFixed = Application.GetOpenFilename
Workbooks.Open (ToBeFixed)
Sheets("!").Visible = True

View Replies!   View Related
Set Variable To Worksheet In Another Workbook
In conjuction with a previous request I've tried to create my own vba code (actually amend existing code i have) but its failing........ whats wrong??

Sub Retail_1_Xfer()
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application. ScreenUpdating = False
Set Isht = Worksheets("Retail Team 1")
Set Lsht = Worksheets("Raw Data")
NextRow = Lsht. Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
Lsht.Range("A" & NextRow).Value = Isht.Range("A9").Value
Lsht.Range("B" & NextRow).Value = Isht.Range("A4").Value
Lsht.Range("C" & NextRow).Value = Isht.Range("B9").Value
Lsht.Range("D" & NextRow).Value = Isht.Range("N3").Value
Lsht.Range("E" & NextRow).Value = Isht.Range("N2").Value
Lsht.Range("F" & NextRow).Value = Isht.Range("BJ9").Value
End Sub

I think its got something to do with the fact that the worksheet " Retail Team 1 " is in another workbook

View Replies!   View Related
Adding A Variable Rows Worksheet
Due DateInvDocument numberDebitCreditBalance

The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.

Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Formula = "=sum(E14+F14)"
On Error Resume Next

End With
End With
End Sub

View Replies!   View Related
Variable To Refer To A Worksheet.CodeName
This is a simplified version of an earlier, long winded post that clouded my actual question.
Can I use a variable to refer to a worksheet by its CodeName? For example, let's assume I have several worksheets, with CodeNames of mySheet1, mySheet2, mySheet3, and so on. I understand that I can refer to them directly,

mySheet1.Range(myRangeName).value = someValue
mySheet2.Range(myRangeName).value = someValue
mySheet3.Range(myRangeName).value = someValue
but what if I want to do this using a loop? Is it possible to preload an array of CodeNames and do it that way? I'm thinking along the lines of something like...

Dim CodeNames
CodeNames = Array( mySheet1, mySheet2, mySheet3 ...)

For x = 1 to HoweverMany
CodeNames(x).Range(myRangeName).value = someValue
Next x

View Replies!   View Related
Linking Data When One Variable Changes In Src Worksheet
I'm using MS Excel 2003 Pro. I need to link data from one source worksheet to a target worksheet in the same workbook when only one variable changes in the source worksheet e.g.:

A. From source worksheet:

When the data under the "Expected Date" column is set (it normally is blank)

B. Populate target worksheet with:

1. Name
6. expected date (the actual date that is set from the source worksheet)

View Replies!   View Related
A Variable Which Is Used By Several Subs In A Worksheet But Is Not Global?
I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?

View Replies!   View Related
Converting A String To A Worksheet Variable
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.

I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.

How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example

Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value

*NoOfROws is the variable to find a blank row

View Replies!   View Related
Run-Time Error Setting Worksheet Variable
The user is asked for one piece of information "Enter the ID Number.

What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

Set Wks2 = Worksheets(strWks)

7 rows from the bottom.

My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

I get a Time Run Error 9.

It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................

View Replies!   View Related
Check Worksheet Exist, If Not Add Variable Sheet Name
I need to allow users to add up to seven new worksheets as required and the new sheets must be named Image1, Image2, etc. I do not want to add seven worksheets at once, only one at a time as the need arises and no new ws can be allowed after Image7 ws is added.

I have a macro to test for the existence of ws Image1, but need help on further development to add and test for the extence of the remaining sheets.

Dim ws As Worksheet
Dim bTest As Boolean
For Each ws In Worksheets
If ws.Name = "Image1" Then
bTest = True
Exit For
End If
Next ws

If bTest = True Then
'Need some code here, not sure what I need

View Replies!   View Related
Worksheet Change Event :: Trying To Set The IntRMACol Variable
This is the code on the Worsheet Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

'Define the RMAs range and count the RMA ships. Do the rest of the math based on this calculation.
Dim intRMACol As Integer, EndRow As Integer, intRMAShips As Integer, LastCol As Integer
Dim rngRMAs As Range, rngHome As Range, sht As Worksheet
Set sht = ActiveWorkbook.ActiveSheet
Set rngHome = Cells(1, 1)
EndRow = Range("A" & Rows.Count).End(xlUp).Row
intRMACol = Cells.Find(What:="RMA#", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
Set rngRMAs = rngHome.Offset(1, intRMACol - 1).Resize(EndRow, 1)
rngRMAs.Name = "RMAs"
rngHome.Offset(1, LastCol + 1).Formula = "=COUNTA(RMAs)"
intRMAShips = rngHome.Offset(1, LastCol + 1).Value
rngHome.Offset(1, LastCol).Formula = "=SUM((" & EndRow & " - 1) - " & intRMAShips & ")"
rngHome.Offset(1, LastCol + 2).FormulaR1C1 = "=SUM(RC[-2] : RC[-1])"
End Sub

I keep getting stuck when trying to set the intRMACol variable. I have used the code all over the place, but only in Modules, never on a worksheet event. EndRow, the variable right above, sets correctly so I don't get it.

Also, do I have to define the Target variable? Am I going about this the right way? I basically want three formulas to calculate when any change is made on the worksheet.

View Replies!   View Related
Error Settting Variable To Range On Another Worksheet
I am trying to run a script from a command button on another sheet in the same workbook. all the variables have been defined. I get a "select method of range class failed" message when I run the code. I believe that i am not getting "closed trades" to be the active worksheet.

excerpt of code

'Clear out existing data
iRow = 2
Set wks = ThisWorkbook.Worksheets("Closed Trades")

lrow = Cells(65536, 1).End(xlUp).Row
Set Rng = wks.Range(Cells(iRow, 1), Cells(lrow, 17)) ' this row errors out
Set Rng = Nothing

View Replies!   View Related
Unhide Sheets/Worksheet With Variable CodeNames
I want to make a hidden sheet visible. Except that i want to use the name of the sheet (the thing that stays the same even when you change the sheet tab name). I also want to select the name based on a variable called Year which the user adds elsewhere (eg as 2006).

wf = "WF_Edin_" + Year 'So for 2006 this would read WF_Edin_2008
wf.Visible = True

The above doesnt work as wf becomes a string which i dont think i want do i?

View Replies!   View Related
Copyright © 2005-08, All rights reserved