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


Advertisements:










Add 2 Cell Values To Variable


I run a large macro which combines and compares an Excel file with a download out of our SAP system. One of the selection criteria I have to put in is called 'Plant' and it is a single value which I select from a drop down list. (Values are 1010 1020 1030 1040). In the Macro the value is defined as:

Plant = Range("I21").Value

where I21 is the cell in which the Plant is selected. My question: How can I make the macro take two values into account at the same time, so 1010 AND 1020 in one go? I tried changing the list used in the drop down list but that does not work. Or is it impossible as the plant has been restricted to only one value?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Define A Cell Range From Variable Values?
I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?

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
VARIABLE SUM Of Values
Using only basic formulas (no VBA then), I need to solve the problem of a VARIABLE SUM of values:

Starting always from the value 1:

- if “control” is “x”: the formula has to write in its “memory” the value 2 and in the next row the new value will be the sum of the two values (1+2), so 3;

- if “control” is “y”: in the next row we repeat 1.

From this point:

- if “x”: we add to the last value of our series another value that will be the last used value and in the next row and in the next row the new value will be the sum of the left value with the right value of the series (in this moment 1+3), so 4;

- if “y”: the formula has to delete from its “memory” the two values that formed the previous sum (in other words the external values of the series) and in the next row the new value will be the sum of the left remained value with the right remained value of the series maintained in “memory”. If remains only a value in the next row we’ll write that single value.

N.B.: if “control” is “z”: the formula must hold in “memory” the sum of values and write that sum in the next row but without considering because all the operation are “suspended”! When “control” will return to “x” or “y” operations of summing or cancelling will start again.

At the end: when everything will restart from the beginning of a new session with the value 1 and “forgetting” entirely what happened above??

There’re 3 cases:

- when all the values are deleted, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above;

- when the result of the sum is >= $A$2, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above;

- when the value of the column “heart” is >= $B$2, in the next row will restart a new session with the value 1 “forgetting” entirely what happened above.

Excuse my english and if you need any clarification… just ask!
No need for this in only a column, you can use all the intermediate columns you may need.
After spending 3 weeks on this I really hope that someone could help me solving this VARIABLE SUM of values.


View Replies!   View Related
Variable Values To Cells
Is it possible to have a cell populated by the value of a Variables obtained (or defined) using VBA? A message box confirms that the variable is indeed defined as desired but I would like its value used in a spreadsheet cell.

View Replies!   View Related
Keep Variable Values AFTER Procedure Ends
I have a User Form with 2 groups of 3 radio buttons let's say. The user must choose one button from each group, then press "GO" which runs specific code depending on what buttons they pressed.

How do I make the variables public or static so that after both radio buttons were selected, another seperate procedure in the same module (The GO procedure) can know what selections were made so it can run code based on those selections? In this example, there are only 2 groups of three radio buttons, but in reality I have 30 buttons and it keeps growing so I need the most direct/simple way to solve this.

For Example:

UserForm1
Radio Button Group 1:

Button 1
Button 2
Button 3

Same UserForm1
but now here is Radio Button Group 2:

Button 4
Button 5
Button 6

I need to know which buttons the user clicked from Group 1 and Group 2 after they finished both their selections from each group, and then run code depending on what combination of buttons have been selected. I'm good to go with the If/Then code to decide which code to run, I just cant keep the variables from reading as 'Empty' when I try to see what the user selected when pressing GO and running that procedure.


View Replies!   View Related
Determining Variable On The Basis Of Given Values
I'm working on this sheet and I can't figure out how to do it.

I'm trying to determine the variable "selling price" on the basis of given values, buying price, Net profit and Expenses.

View Replies!   View Related
Variable To Store Multiple Values
I need a bit of help with the below macro which I am trying to create. I recorded the below vlookup, which works perfectly. It checks a list on sheet “Map” and returns a value depending on whether the reference is one of the 6 or not. These 6 are likely to change over time so I would prefer to declare them as variables rather than build them directly into the macro

View Replies!   View Related
Show Values Of Variable In Rows
I have a list of part numbers in a column and a list of suppliers in another column. Parts may have anywhere from 1 to 15 suppliers.

Is there an easy way to show each part number once down one column, with the suppliers going across the one row for each part number?

Example:
PN#Supplier
123ABC
123DEF
123GHI
123JKL
123MNO
123PQR
456ABC
456DEF
456GHI
456JKL
456MNO
456PQR
456STU

Desired Output:

PN#Sup1Sup2Sup3Sup4Sup5Sup6Sup7
123ABCDEFGHIJKLMNOPQR
456ABCDEFGHIJKLMNOPQRSTU

I tried a pivot table,

View Replies!   View Related
Pass Variable Values Between Workbooks
I'm just starting to experiment with passing values between workbooks and between modules and so far I've managed to get it working. However, I now can not run the receiving module independently because of the passed value ? Let me explain ...

I use start/end dates within my modules to create date sensitive reports. I have several files (for different departments) and each file has a module called "AbsenceChecker", I have been working on a way to create a Report Master workbook to run and amalgamate the "AbsenceChecker" module from each department.

Sub RunAllLoaders()
Dim AbsenceStart As Date, AbsenceEnd As Date
Dim PassVar1 As Date, PassVar2 As Date
Dim DateStart As Date, DateEnd As Date
On Error Resume Next
'start of data validation script continued in private sub.
GetValidDates FromDate:=PassVar1, _
ToDate:=PassVar2, _
MinDate:=DateSerial(2007, 1, 1), _
MaxDate:=DateSerial(2007, 12, 31)
Excel.Application.EnableEvents = False
Workbooks.Open Filename:="R:RostersRosterALPHA.xls"
Application.Run "RosterALPHA.xls!AbsenceChecker", PassVar1, PassVar2
End Sub...................

View Replies!   View Related
Counting Unique Values Based On A Variable
I'm having difficulty trying to write a formula for the following type of data:

[data] ....

This is a data set with 10k+ lines; I have about 150 different items in the "Criteria" column. What I want to do is count how many unique items occur in "Column to be counted" by each vairable in the "Criteria" column. The output should look like this: ...

View Replies!   View Related
Sum Values If Multiple Variable Criteria Are True
I'm trying to find the sum of a range of values based on multiple criteria, and the criteria is that the fields all have to be identical, then sum them. I've attached a brief example spreadsheet that has the fields

A=City
B=State
C=Values

What I want the formula to do is first find the range of all the matching states, then find the range of all the matching Cities within the states, and then sum the values based on them having matching city values.

I've been able to do that with one criteria using SumIf, I'm not sure if this will help paint an image of what I want to do:

=ROUND(SUMIF($B$3:$B$11,$B$3:$B$11,$C$3:$C$11),0)

But I can't figure out the way to do multiple criteria against itself. Most of the results I get from Google using multiple criteria are using a set few values, and I can't seem to figure out how to alter those methods to work with my situation.

View Replies!   View Related
Formula Only Look At Last 3 Values In The Column? (variable Rows)
For example I have a data set that will grow over time say 13 rows, I want to write a formula that will only use the last 3 rows with data entered and get the Median of those rows. I know that the Median Formula for the entire 13 rows would be =MEDIAN(A3:A16). Anyone know a way to make a Median formula only look at the last 3 in any given time without manually updating the formula's range?

View Replies!   View Related
Calculation Error After Passing Values To Variable
I currently have a userform which inputs data into two cells, the third then multiplies these two cells together, which is all honky dory. It works, 2 x 2 = 4.
But when i put 0.6 for example it really doesnt work. 0.6 x 25 = 25. WRONG. My code in the macro is fairly long winded but will paste anyway.

If ComboBox2.Text = "Pounds (£)" Then
Sheets("Claims").Range("G16") = TextBox2.Text
Else
Sheets("Claims").Range("F16") = TextBox2.Text
End If

Sheets("Claims").Range("B16") = ComboBox1.Text
Sheets("Claims").Range("C16") = TextBox1.Text
Sheets("Claims").Range("B16") = TextBox3.Text
Sheets("Claims").Range("D16") = TextBox4.Text
Sheets("Claims").Range("E16") = TextBox5.Text

Dim a As Long
Dim b As Long
Dim answer As Long
a = Sheets("Claims").[E16]
b = Sheets("Claims").[F16]
answer = (a * b)
Sheets("Claims").[G16] = answer

View Replies!   View Related
Recognize Text Values As Variable Names
I want VBA to take an equation that has been entered into an worksheet cell as text and evaluate it. Here's a very simplified example that shows the concept:

Cell A1 may contain the text: "A * B + C"

I would like to do something on the order of this in VBA:

Sub Test()
Dim A As Integer, B As Integer, C As Integer

A = 2
B = 4
C = 6

MsgBox Evaluate(Workbooks("Book1").Sheets("Sheet1").Range("A1").Value)

End Sub

Obviously, this doesn't work, but I want it to show a result of 14. The actual situation is much more complex, but it all comes down to being able to evaluate the text in a worksheet and recognize the variable names that are in it.

View Replies!   View Related
Assigning Variable Values To Letters In A Table
I have a formula which assigns a points score to letters in a range and adds them up. In the example below F=0, P=6, M=12 & D=18. =IF( COUNTIF(AT5:BE5,"="""),"",SUM(COUNTIF(AT5:BE5,"=F")*0,COUNTIF(AT5:BE5,"=P")*6,COUNTIF(AT5:BE5,"=M")*12,COUNTIF(AT5:BE5,"=D")*18)). Thus if F, P, M & D were in cells A1:D1 the result would be 0+6+12+18=36.

My aim is to be able to customise the values of F, P, M & D, using a table and a cell value. See the table below, where the first number in each row represents a cell value which the user enters into BH1, the second, third, fourth and fifth numbers represents the values assigned to the letters F, P, M & D.


10 0 1 2 3
30 0 3 6 9
60 0 6 12 18
90 0 9 18 27
120 0 12 24 36

Some examples of expected output: user enters 10 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+1+2+3=6. user enters 90 into BH1 and then enters F, P, M & D in cells A1:D1 the result would be 0+9+18+27=54.

View Replies!   View Related
Store Range Values In Variable Array
I have a list of names from cell A1:A10 in sheet "Input." Each of these names has its own corresponding sheet in the workbook. I want to be able to run the same exact VBA code for each sheet. In other words, I am trying to get my name variable to automatically change to the next value on sheet "Input." I'm sure this is pretty simple to do, but I can't seem to find anything that works!

View Replies!   View Related
Countif Function For Counting Values Based On More Then 1 Variable
I'm trying to make a spreadsheet that will count the number of times a certain incident occurs, for a particular person, for a particular month. The attached spreadsheet is an example of what I need done.

For the attached spreadsheet, I need to find out how many times x employee has been late for x month, and how many times they've been late overall.

You can see one of the many tries I've attempted in the second sheet, but it doesn't seem to want to work. I have to be able to do this without VBA, because of signature issues.

View Replies!   View Related
Setting Values In A Variable Range Occurence Query
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;

1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for

Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................

View Replies!   View Related
Macro To Extract Variable Info From Data Set And Calculate Corresponding Values
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............

View Replies!   View Related
Updating Values In Variable Ranges Based On Repeating Patterns In A Column
I have a list of two columns. Here’s an example. The left most column provides the row number.

_ A B
1 1 0
2 2 1
3 2 1
4 3 2
5 4 2.5
6 5 4
7 1 0
8 1 0
9 2 2

Whenever there are two repeating numbers in column one, I want to reduce certain numbers in column 2 by a certain amount. The amount is determined by half the difference between the number in column B corresponding to the second repeated number and the number in column B corresponding to the row after the second repeating number. The range of numbers that are to be reduced begins with the row after the second repeating number and ends with the last row before number one appears in column A. The values in column A are integers, always starting with one. For example, 1, 2, 3, 4, 1, 2, 3, 1, 2, 3, 4, 5.

For the above example, after finding the repeating number two’s at A2 and A3, it would reduce B4 through B6 by half the difference between B3 and B4 (1/2). The values for B4 through B6 would be updated in column B. The same for the next repeated numbers, which is one at A7 and A8. Half the difference between B8 and B9 is 1.

Here’s what the updated list would look like:

1 1 0
2 2 1
3 2 1
4 3 1.5
5 4 2
6 5 3.5
7 1 0
8 1 0
9 2 1

View Replies!   View Related
Conditional Summing: Find Those Combinations Of Variable Values Which Generate Highest Total Gain
The aim is to find those combinations of variable values which generate highest total gain. I attached the spreadsheet which shows the variables (A through K) and a Gain column. I created 5 additional tabs which show all possible 2,3,4 and 5-member combinations of the variables. These tabs are like coordinates of which variable combinations should be examined. As an example I used the first combination from the second tab = A and B. If you look at these two columns on the EXAMPLE CALCULATION tab you will see 7,7 in the Number combination which is the first number pair for these two variables. The headings of the red and the yellow columns calculate the total count for this number pair and the total gain. These were recorded on a separate EXAMPLE RESULTS tab along with some other pairs which appear afterwards (these were recorded only from the first 39 rows of the AB data). I need a macro which will cycle through each variable pair (only using the combinations from the tab 2 for now, annd later from 3,4 and 5 tabs) collecting statistics for each unique number combination it encounters (printing to a separate sheet one after one), such as shown on the EXAMPLE RESULTS.

View Replies!   View Related
Adding Cell Values That Are Dependent On Adjacent Cell Values
Here's an example of what i'm working with,...

Range is C40:D48

View Replies!   View Related
Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.

Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.

View Replies!   View Related
Access A Cell's Contents Via A Variable Containing Cell Address??
How can you access a cell's value via a vb variable that contains the cell's address. I have a vb variable named cellAddress (string) that contains "$A$1" and I want to assign the contents of what cellAddress points to another VB variable - how do i Do this?

e.g. if cellAddress = "$a$1" and A1 contains "xyz", I would like to assigne "xyz" to a new vaiable by referencing just cellAddress??? Also - Can anyone reccomend a good Excel VB book? A book that maybe stresses the VB language rather then a cook-book approach.

View Replies!   View Related
Set Variable: Object Variable Or With Block Variable Not Set
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"

Sub test()
Dim StartRng As Range
Dim Buffer As Range

Set StartRng = WorkSheets("Sheet1"),Cells(1,1)

StartRng.Activate
ActiveCell. CurrentRegion.Select

Buffer = rngStart.CurrentRegion.Copy

' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub

View Replies!   View Related
Copy And Paste Special Values But Getting Error 'Compile Error - Expected Function Or Variable'
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:

Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

For which I get 'Compile Error - Expected Function or Variable'

View Replies!   View Related
Find Same Values In Different And Unequal Cell Ranges And Refer To Values
I have data similar as shown in the following:

A1A11
B2AA1122
C3B22
D4C33
D44

The idea is to add compare the cells of the first column with the third column. Where same letters/words exist, the corresponding value of the first column should be added to the second column (where no letter exists equally, the space remains empty), so it will look like this

A111
AA1122
B222
C333
D444

the third column always will have at least the same letters as the first column, but new letters/entries can occur.

View Replies!   View Related
Lookup Multiple Values In A Single Cell (separated By Commas) And Then Return The Values To A Single Cell (also Comma Separated)
If I have, in one cell (call it D1):

EH,DR,HU

and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E

I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...

eg...

F,G,H



View Replies!   View Related
Can't Get My Variable To Take Cell Value
I have three worksheets that I'm working with (2,3,and 4) and I would like the last one to contain values from the other two. In the first sheet I have a formula to convert some Degree minutes and second (DMS) data to Decimal degrees (DD). In the second sheet I'm converting degree minutes (DMM) to decimal degrees. In the last sheet I want it to take all the DDs from the first sheet then the DDs from the second sheet and put them into the appropriate column. So I want it to copy from the first sheet until it gets to the bottom, then copy from the second sheet until it gets to the bottom.

Here is the
Dim FDMSX As Range
Dim FDMSY As Range
Dim FDMMX As Range
Dim FDMMY As Range
Dim EX As Range
Dim EY As Range
Dim counter As Integer

Set FDMSX = Worksheets(2).Range("X2:X101").Cells(counter, 1).Value
Set FDMSY = Worksheets(2).Range("W2:W101").Cells(counter, 1).Value
Set FDMMX = Worksheets(3).Range("C2:C101").Cells(counter, 1).Value
Set FDMMY = Worksheets(3).Range("B2:B101").Cells(counter, 1).Value
Set EX = Worksheets(4).Range("C2:C101").Cells(counter, 1).Value
Set EY = Worksheets(4).Range("B2:B101").Cells(counter, 1).Value........................



View Replies!   View Related
Use Cell As Variable Name
Is it possible to define a variable name using the value in a cell? for example
The value in cell A1 was "Fish" then some code created a variable called Fish

View Replies!   View Related
Set Variable To Value Of Cell
how to set a named variable to a value from a cell without using .select on that cell? This is what I tried without success.

Set peaktime = activecell.formulaR1C1 = "=RC[-1]"

While I get an error when this line attempts to execute, the variable actually has the correct value assigned to it when I mouse over it in debug mode. If I use that exact same line without the 'set', peaktime equals 'false' instead of the value in the corresponding cell.

View Replies!   View Related
2007 Macro: Run-time Error 91:Object Variable Or With Block Variable Not Set
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...

The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.


View Replies!   View Related
Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View Replies!   View Related
Use Cell Variable In Formula
I am having an issue using vlookup with named ranges.

I have a work book with 3 sheets on, the 3rd sheet has lists defined as named ranges and is named as lists.

The first and second sheet has references to the lists on this sheet, using the validation e.g
=(named range)

I was using the following if statement =IF($E$7="web policy",web_policy,IF($E$8="NUD",BU,IF($E$8="RAC",BU,IF($E$8="IB",IB,IF($E$8="BAU",BAU,not_known)))))

But I now need to add some more to this and have decieded to use Vlookup instead.

I have the vlookup in the 3rd sheet (lists sheet) and when the vlookup result is returned it returns the list name that I need to select, the problem I am facing is using this result to produce the list on sheet 1 or sheet 2.

The vlookup I am using is =VLOOKUP('Test Conditions'!E8,AD1:AE7,2,FALSE), but I can't use this on the first sheet as validation can't look at other sheets.

I have now changed formula slightly to;=VLOOKUP(ad32,AD1:AE7,2,FALSE), AD32 contains the formula that references the first sheet, but when I paste this into the validation I get the following error.

The list source must be a delimited list, or a reference to a single row or column


View Replies!   View Related
Make A Cell A Variable
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.

View Replies!   View Related
Variable In Cell Reference
I have counted the number of rows using i. Now I need to pass that variable to the ActiveCell referances shown.

Do While Not Selection. Offset(i, -6) = ""
ActiveCell.FormulaR1C1 = "= round(R[i]C[-8],2)&""±""&round(R[i]C[-7],2)"
ActiveCell.Offset(0, 1) = "=round(R[i]C[-7],2)&""±""&round(R[i]C[-6],2)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub

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
Sumif With Variable In Cell
I have a large range which contains serial numbers (8 characters long) for a product and the quantity sold for each. Range is B2:C5000. Beside it on the worksheet I have the serial numbers for each product family (the first 4 characters of the serial number). Range is F2:F100. Now I'm trying to sum the quantity sold for each product family. I.E. trying to sum the quantities for the serial numbers in column B which contain the first 4 characters from column F

Here's what I tried: Sumif(B2:B5000, "*1234*",C2:C5000) This works but requires me to manually enter the serial numbers from column F (here I just randomly wrote 1234). Therefore, is there a way to take the Sumif and it the criteria part to tell it to look at the value in F2,F3,F4... and sum the quantities for the serial numbers which contain the value in that cell.

View Replies!   View Related
Increment Cell By One Row In Variable
I am working on a Loop, I have most of it working except incrementing the cell.

I have this,

PartN = Worksheets("Parts").Range("A3").Value

then the loop. what I can't figure out is I need for A3 to increment by one row until the loop is done. i.e. loop runs as A3, then A4, then A5 etc.. until the specified number of the loop is reached (which is basically the number of rows I have)

I tired something like

Dim CellN As Variable
CellN = "A3"
Do Until xxx
PartN = Worksheets("Parts").Range(CellN).Value

then before the Loop statement at the bottom I put

CellN = CellN + 1

but I get a type mismatch error

View Replies!   View Related
Macro Error Message (object Variable Or With Block Variable Not Set)
When i try to run the code below i get the error message - object variable or with block variable not set-

Sub REFRESHXX()

'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing

With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With

'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar

'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1

End Sub

View Replies!   View Related
Find Dates Macro: Object Variable With Block Variable Not Set
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select

End Sub

View Replies!   View Related
Inputbox Value Can Be Compared To A String Variable Or A Numeric Variable At The Same Time
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.

View Replies!   View Related
Find Method Code: Object Variable Or With Block Variable Not Set
I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View Replies!   View Related
Use A Cell/variable As A Sheet Reference?
I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.

For example... instead of LEMONS!a1 and LIMES!B2

I would like to use REF1!a1 and REF2!B2

where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'

How can I do this?

View Replies!   View Related
Vba - Cell Copy And Time Variable
Into Sheet1 there is in A1 (or should i call it better R1C1) a value which is update every sec from i timestamp cell.
If the value of this cell change i want to put the previous value into A1(can be another) cell of Sheet2 and keep the new value into A1 of the Sheet1

When the value change again i'd like to put it underneath the cell A1in Sheet2, so that i can take a history of the values.
So there will be a column A with different rows.

Every 3 mins i want change the column into Sheet2 so from A now the values will be update on column B

Everything will start when i open my excel file, because the timestamp will start to run.
What about if i want to align the change of the column with a particulary hour?
for instance
if i open my excel file at 9.33am everything will start since that time, but what if i want make it start from (a multiple of) 9.00 am (so every 3 min from 9am?

View Replies!   View Related
How Do I Collect And Use The Text In A A Cell As A Variable
I want to select a cell that contains a job number, use the text from that cell as the new file save as name (got the file creation part sorted out) and then continue from there to go back and create a hyperlink in the original cell to the newly generated spreadsheet. I know it involves variables, have tried things like currentcell.value but can't get what I want.

View Replies!   View Related
Value Of A Cell In An External And Variable Workbook
I need to create a formula or code which will return the value of cell d16 from an external and unopened workbook, where that external workbook's file name, directory and sheet names are provided within cells (able to be updated) of the current workbook. I have downloaded the add-in for INDIRECT.EXT but either being unfamiliar with this function or that I'm barking up the wrong tree all I'm getting is #REF!

And to add to my problem, I was hoping to use this formula / code within an excel table embedded in a word document.


View Replies!   View Related
Store Cell Address In Variable
I would like to record the address of the last set of cells that data was input into to a variable so that a user can choose to delete the last entry. An 'Undo' button really.

What I have is a user form that writes different materials to thier respective sheets in the database. (Material1, Material2, etc.) Some materials have a different number of variables (some have a width and some don't, but all have a quantity.)

This is the code I have for adding the material to the database (each material has it's own button with material specific code.)

Private Sub AddToMaterial1_Click()
Set c = Worksheets("Material1").Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False

c.Value = Me.Material1Quantity.Value
c.Offset(0, 1).Value = Me.Material1Description.Value
c.Offset(0, 2).Value = Me.Material1Length.Value

Dim lastenty1
lastentry1 = c.Address
Dim lastentry2
lastentry2 = c.Offset(0, 1).Address
Dim lastentry3
lastentry3 = c.Offset(0, 2).Address
Dim lastentry4
lastentry4 = c.Offset(0, 3).Address
Dim lastentry5
lastentry5 = vbNullString
Dim lastentry6
lastentry6 = vbNullString

Application.ScreenUpdating = True
End Sub

The following code is what I am trying to do for a single button to clear the last entry to the database.

Private Sub RemoveLastEntry_Click()
Range(lastentry1).ClearContents
Range(lastentry2).ClearContents
Range(lastentry3).ClearContents '(There is always at least 3 cells to clear)
If lastentry4 = nullstring Then Exit Sub
Range(lastentry4).ClearContents
If lastentry5 = nullstring Then Exit Sub
Range(lastentry5).ClearContents
If lastentry6 = nullstring Then Exit Sub
Range(lastentry6).ClearContents
End Sub

View Replies!   View Related
Sum Across Sheets With Variable Cell Reference
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.

However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?

View Replies!   View Related
Pass Cell Value Using Variable Row Number
Following statement works for me:

bdcTerm1 = ThisWorkbook. Sheets("ws2"). Range("A1").Value

But instead I want to parse through 50 rows and dynamically get the value instead of using a static Range("A1"). So I am trying to do the following:


For Row = 1 To bdc_rows
bdcTerm1 = ThisWorkbook.Sheets("ws2").Cell(Row, 1).Value
bdcTerm2 = ThisWorkbook.Sheets("ws2").Cell(Row, 2).Value
Next Row

But I get errror.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved