I have a small program, and in a particular cell, I ask a

user to enter the number of copies they want printed when

they push the button linked to the printpages macro I

made. In the macro I want to change the "Copies:=1" so

that rather than a 1, it will reference the value of the

cell they entered the number of copies desired. I tried

ActiveWindow.SelectedSheets.PrintOut Copies:=Cells

([g], [22]), Collate:=True Range("E4").Select

But that didnt work. I dont know much about VB or how to

use it in conjunction with Excel, but anyone who can help

me fix this is greatly appreciated!

Ray

user to enter the number of copies they want printed when

they push the button linked to the printpages macro I

made. In the macro I want to change the "Copies:=1" so

that rather than a 1, it will reference the value of the

cell they entered the number of copies desired. I tried

ActiveWindow.SelectedSheets.PrintOut Copies:=Cells

([g], [22]), Collate:=True Range("E4").Select

But that didnt work. I dont know much about VB or how to

use it in conjunction with Excel, but anyone who can help

me fix this is greatly appreciated!

Ray

- How can I get excel to apply values in a cell before or after date
- How can I get excel to apply values in a cell before or after date
- How can I convert a formula to a value in excel?
- "criteria" in a sumif refering to the value in another cell
- "criteria" in a sumif refering to the value in another cell
- Excel: Add/subtract to existing value in same cell
- Changing Formula References in Visual Basic
- Formatting number value to text value in excel
- How to change value in cell without broke the formula
- Reference value in another cell
- Vba To Join Values In 2 Cells Into Another Cell
- IF function to place value in another cell
- Lookup Function to Return Values in Adjacent Cell?
- Code to place value in another cell
- How to turn positive to negative values in excel
- Match value in one cell with values in multiple columns
- Visual basic to step through range and store a value based on text attribute value.
- Visual Basic Function works fine in Excell 2003 but not in Excel 2
- IF Function to test formula in a cell
- IF Function to test formula in a cell
- How to make excel to treat values in cell as a number?
- How to find if an excel sheet has any hidden values in a cell in VB.NET
- How to reference a value in a cells for a row number
- Using Visual Basic to Exit Windows From Within Excel

after dates displayed in another cell? It will allow me to use a

datevalue(...)= but not datevalue(...)> or datevalue(...)

after dates displayed in another cell? It will allow me to use a

datevalue(...)= but not datevalue(...)> or datevalue(...)<.

formula to a value. Can you change a formula to a value in excel?

rather than having the criteria hard keyed into the formula. For example, if

I have a list of purchases by date and I want to sum up those that occured

prior to 12/31/04, and 12/31/04 is the value in another cell. Can i have the

sumif refer to that cell for the "criteria"?

rather than having the criteria hard keyed into the formula. For example, if

I have a list of purchases by date and I want to sum up those that occured

prior to 12/31/04, and 12/31/04 is the value in another cell. Can i have the

sumif refer to that cell for the "criteria"?

subtracted or added to the exisitng value, a separate calculator needs to be

used. Can Excel perform this function and eliminate the need for the separate

calculator?

list with very particular spacing and formating for use in

another program.

I'm using a looping system to work between the different

cells in the sheet which will initially just contain the

formatted data. Anyway to use visual basic to insert

formulas in these cells where the referencing system is

tied to the number of the iteration in the looping

procedure? ie, inserting the formula = sheet1(B3) in the

new sheet, where the B3 part is generated automatically in

the looping process?

Cheers

I am trying to contain a number value in excel after changing the value

to text.

First, I calculate a cell, then change that cell from a formula to a

value. Then, I want to change the value to a number with 2 digits

after the decimal. My dilemma occurs when the number value shows as 3

digits after the decimal. When I try to reformat for 2 digits, the

number value is displayed with 3 digits. Does that make sense? Please

help!

First I must notice that I dont know very good english and may be hard to understand what is my problem. I'll post pictures to make the things more easy to understand.

Second, im totally noob in excel so explain like an idiot. ;]

So I have a spreadsheet in english. I just want to translate it to bulgarian(for users in my country). I have many cell with formulas and when I try to change a cell everything broke.

Picture 1 - THE SPREADSHEET

http://img28.imageshack.us/img28/4081/thetable.jpg

Picture 2 - when i'm trying to change something

http://img535.imageshack.us/img535/9...celproblem.jpg

1. here is ok

2. is ok too

3. there is the problem!

In general i just wnat to change value in the cell, not the formula.

Please advise

VB:Thanks AndreaAddday() ActiveSheet.unprotect Password:="test" If Not Selection.Column = 1 Then Cells(Selection.Row, 7).Select If ActiveCell.Value = "2" Then MsgBox "No More Entries Allowed" End End If Dim Mystartsheet As Worksheet Set Mystartsheet = ActiveSheet Dim rngOut As Range Dim rngSpace As Range Sheets("Sched").Select Range("a1").Select Application.Run "'book1.xls'!showform3" Mystartsheet.Activate Set rngOut = ActiveCell.Offset(0, 4) If Len(rngOut) > 0 Then On Error Resume Next Set rngSpace = rngOut.Resize(1, 2) Set rngSpace = rngSpace.SpecialCells(xlCellTypeBlanks).Cells(1, 1) If rngSpace Is Nothing Then MsgBox "No More Entries Allowed" Exit Sub Else rngSpace = Sheets("Sheet3").Range("a1").Value & " " & Environ("username") End If Else rngOut = Sheets("Sheet3").Range("a1").Value & " " & Environ("username") End If If Not Selection.Column = 1 Then Cells(Selection.Row, 2).Select Sheets("Sched").Select Application.Run "'book1.xls'!FindDate" Mystartsheet.Activate Application.Run "'book1.xls'!Findnextmatch" Application.Run "'book1.xls'!moveit" ActiveCell.FormulaR1C1 = "A" Application.Run "'book1.xls'!Macro1" Range("A1").Select Mystartsheet.Activate If Not Selection.Column = 1 Then Cells(Selection.Row, 2).Select MsgBox ("Day Entered") ActiveSheet.protect Password:="test" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am trying to write some VBA to join the values in 2 cells into another cell for a list, however my knowledge at this level is a little lacking.

For each row in a list I would like to join the value of the cell of Column A and the value in the cell of Column B and write the output to Column C for that row, as shown in the attached example. Column C in the example shows the expected output.

Any help appreciated.

Trickster

=IF($N2="O",AS2="1",AS2=AS2)

This formula simply produces TRUE in the cell that it is entered in. Thanks for any help or suggestions.

Source:

COL1 COL2 ABC 1 32 23 14 DEF 9 55 36 91I'm trying to find a formula that would allow me to LOOKUP value in COLUMN1 (COL1 above) and then return the values in COLUMN2 (COL2 above). So in the above example Lookup ABC, returns 1, 32, 23, 14 (but in the same layout as above, vertically).

Sure I'm missing something simple here. Thanks in advance.

value in the cell next to them. I need to turn the D values to negatives in

order to sum the data

I have second set of data in one column and approximately 50 rows. Now for this second set of data, I would like to compare value in each cell to all the values in first data to return a true or false based on if there is a match or not.

Now I tried the following formula:

=SUMPRODUCT(--('[file1.xls]Sheet1'!$C$2:$F$55=LEFT(B6,8)))

This works just fine and returns 0 or 1 until there multiple matches and I get numbers like 5 or 7. I would like to get a 0 or 1 back as soon as there is one or more match. I prefer not to change my first data set to put all my unique values in one column because that data set changes frequently and I get a download plus if anyone needs to verify that those "rules" are correct then it can not be done in one column of data.

Any thoughts?

Thank you in advance,

Jay

many

> different colors by the color of the text.

To group by the color or sort by the names of the colors? I don't know that

the Font.ColorIndex implies a specific color, only an index into a palette.

The ColorIndex would be sufficient for sorting, it seems to me, although you

could assign names to the indicies.

This will put the ColorIndex of a target cell into a cell offset two to the

right of the target cell for each cell in the selection.

Sub Main()

Dim cell As Range

For Each cell In Selection

cell.Offset(0, 2).Value = cell.Font.ColorIndex

Next cell

End Sub

--

Bob Kilmer

"Ashleigh K." > wrote in message

...

> Help, my boss is making me do a time wasting excercise and I can't figure

it

> out.

>

> I'm trying to sort a HUGE excel database consisting of roughly 22,000

> records. What I'd like to do is to create a visual basic routine that

will

> step through each record in the database and store a value in a free cell

> indicating the text attributes of the value in column A. The database

looks

> something like this:

>

> Column A = First Names

> Column B = Last Names

> Column C = Currently blank, but I want it to store the value of the visual

> basic routine.

>

>

> So, a pseudocode example would do something like this.

>

> Start

> For 1 to x (x=total number of records 21,000)

> If font color attribute for Cell A(x) = Red

> Then Store the word "Red" in cell C(x)

> endif

> next x

> end

>

> I know from a macro that I tried to record that it will use something like

> Selection.Font.ColorIndex = 3

> but I don't know how to make it step through each cell and then store the

> new value in the corresponding row in column C.

>

> Does this make sense? Am I approaching this the right way. Does anyone

> have a suggestion on how I might write the visual basic to achieve this.

My

> end objective is to be able to sort the database that has records in many

> different colors by the color of the text. I think this approach (if I

can

> figure it out) will get me there.

>

> Thanks for all of your help!

>

> A.

>

>

was created in Excel 2003 and works fine (everything functions). However,

when I moved the file to Excel 2007, only the Visual Basic Function no longer

works (the macros and buttons operate fine).

When I use "Evaluate Formula" for the Visual Basic Function, it provides the

correct answer. However, when I press Calculate (F9) or when I press Enter

with the cursor at the end of the function's formula on the "Fx" line, I get

"#VALUE!" and don't understand why. Any ideas? Thanks.

there any way to get the IF() function (or some other function) to

test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact

true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when

a formula has been modified by some spreadsheet editing process and I

don't want the formula to change. Even "absolute" references seem to

change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1

Reference Style (because the formulae I'm interested in are literally,

character for character, identical in R1C1 reference style, and it's

easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a

question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

there any way to get the IF() function (or some other function) to

test the formula in a cell?

For example, the formula in Cell E1 might be:

IF((The formula in Range("A1")="=+B1+C1"),"OK","Error")

Everything I've tried produces "Error" when the condition is in fact

true, i.e., the formula in Cell A1 is correct.

The purpose of this is to produce cells that are flags that alert when

a formula has been modified by some spreadsheet editing process and I

don't want the formula to change. Even "absolute" references seem to

change under some edits such as the insertion of a row.

And, oh by the way, I'm actually running the workbook with R1C1

Reference Style (because the formulae I'm interested in are literally,

character for character, identical in R1C1 reference style, and it's

easier to determine that they haven't changed by "visual" inspection.

Yes, I could just re-propagate the formulae any time there's a

question, but that's a "chore."

Excel 2000

Thanks for any help.

Fred Holmes

very new to VBA programming. I came across these---

Excel.XlCellType.xlCellTypeBlanks and

Excel.XlCellType.xlCellTypeVisible

But I am not sure if this is what would serve my purpose.

Thanks

the weekly data (Week 1 Week 2 Week 3) for the specific data in the rows.

Each week I add new data to the next row and need to be able to sum or total

the data at the bottom (Year to Date information). Currently, I highlight the

area containing the year to date formulas and do a find/replace to advance

the row to the next one down with the new data for the current week.

Problem: Can I put a value in cell and have the sum or average formula

reference a cell the contains the row number that I would like to end on? I

have tried to use name function for the row, and the r1c1 format but can not

use a cell reference for the # in the r1c1 format. here is an example:

=sum(a1:a+currentweek) Current week is a named cell for the row with the

current weeks data.

I also tried in the r1c1 format =sum(R1C1:R'currentweek'C1) In this

example current week would a cell holding the value 10 next week it would

hold the value 11 and so on.

I have several formula that very with the week. So if there is any easy way

to reference a cell containing the a vaule to be place in the cell reference

for a row would be a big help. Otherwise, find repace of the hightlighted

area is the quickest way I know to do this.

Thanks,

David

The Declare, Call and Register Functions listed in the following macro are very sensitive. ... You can type lines that contain this character as one logical line or you can divide the ...

No luck finding an answer? You could always try Google.