Cell Values Not There ???
I'm using VBA code in Excel 2007. I am having a strange problem. The
cells have content. Some of them, but not all of them, are selected
from Data Validation lists (drawn from a named range). However, when
I try to access their values in VBA, it says they are empty!''
For example, the simple code:
Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value
results in the output:
Cell value is:
I even tried the following code, to make sure it was not a problem
with the ActiveSheet reference:
ThisWorkbook.Sheets("Form").Cells(2, 7)....Displaying Counts when Filtering
Usually when using the filter function, the total records
in the spreadsheet and the number of records that match
the filter criteria are displayed on the lower left of
the window (I guess it's the status bar). However, for
certain files that I have, no counts are displayed when I
filter. Does anyone know if there is a setting in the
file that causes this or something else that needs to be
changed? I do notice this happens frequently with files
created by exporting from Access, but not always. It
also happens with other files, but I can't find a common
Hi To...range of chart
If I am trying to chart information related to
individual's payroll in comparison to total pay roll, how
do I select the correct range to have the names show
along with the value ranges?
Do you want the names ass data labels, linked to the points? If so, use
one of these free utilities to add text from cells to the chart:
Rob Bovey's Chart Labeler, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com
Jon Peltier, Microsoft Excel MVP
> If I am trying to chart...How do I format a field based on the value
I have a form that is populated from a query using several tables. In the
detail section I have the following fields:
Category Code qty PO number ... .... ...
The qty is populated in the query based on whether or not a PO number is
available. If the PO number is not available then it comes from table a, if
it is available then it comes from table b.
On the form, I have my fields color-coded for easy reading. I would like to
be able to color the border and column heading based on where this value
comes from. If table A, then it is blue, if table b then it...Linking Range
If I have a source sheet1 and this same range is linked
to sheet 2, why if I insert a line on my source data no
line is inserted on the link data. How can link two
ranges so that if I insert a row on the one a row will
also be inserted on the orter with links to the inserted
I'm getting dizzy with that. Don't know why you would
think linking sheets together should handle inserting rows
Group your sheets. Then when you insert a row on the
one you see the other sheets in the group will receive the
same treatment as if you had done the same keyboard
instruct...Count of Unique Items?
I have a worksheet (Sheet1) where column A consists of dates (in date
order). I need to look at this column from another sheet (Sheet2) in the
same workbook and return the number of unique dates in this column. For
example, on Sheet1 cell A1 has the heading Date, A2 is 10/01/04, A3 is
10/01/04, A4 is 10/01/04 and A5 is 10/05/04. From cell A1 on Sheet2 I need
to look at the range A2:A5 on Sheet1 and return the number 2 (the number of
unique dates in the range).
Any help would be greatly appreciated.
Try the following...
I am trying to use Conditional Formatting to find the Max value in a Row
using =Max(B6:AC6), having that cell Highlight to a different color but the
=Max is highlighting them all.
>I am trying to use Conditional Formatting to find the Max value in a Row
>using =Max(B6:AC6), having that cell Highlight to a different color but the
>=Max is highlighting them all.
There may be other ways, but what I do is create a column, or in
your case cell, (can be hidden, or way out of sight) having the
formula =Max(B6:AC6). Then use the value of that cell as the
co...Xpath with multiple values..
Is is possible to use XPath with multiple values like in SQL Queries (AND
If I have xml:
Now I want only to select rows 1,2,3 an 100
XPath("rows/row[@code='1' and @code='2' and @code='3' or @code='100']").
I tryed like this above, but I got nothing.
Try using ORs all the way. An AND situation is impossible here...counting seconds
=SECOND(NOW()) and holding down the F9 key starts at
whatever the second hand is pointing at and counts up to
59, then starts back at 0, 1, 2, to 59, 0, 1, 2 and so
on. How can I write it so it starts at 0, and counts 1,
2, 3,...,59, 60,61,...forever?
have a look at
(at the bottom of this site): There are some example counters / stop
timer functions. This may what you're looking for
"scott" <email@example.com> schrieb im Newsbeitrag
news:143e601c3f7f9$b63ac...How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box
values and a toggle value (Y/N) behind each other in one cell.
...Count with two criteria
I am working in Excel 2003, I am trying to count with two criteria and
I am using this formula: =SUMPRODUCT(('Sum 1Q10'!A2:A4="BKD")*('Sum
1Q10'!F2:F4="Open")) my result is 0. It should be 1.
Is there another way to do this? I really need help.
Lisak- There are a couple of approaches to do this; I'll give you the one I
use out of habit.
The sumproduct conditions evaluate to true or false. You have to force them
back to a numeric format. Some folks do that with a leading double negative
on each condition. I tend to ...How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl
transformation on it due to what I call bad characters in the data. I
have as an example:
str = Replace(str, "", ".")
str = Replace(str, "", ".")
In there as filters to strip out characters XSL doesn't like and
replace it with periods. I am now getting an exception of a hex value
of 0x0E however can't figure out what the string replace value should
be for this one. From what I can tell, it's some kind of "shift out"
character whatever that is. A...How do I assign a set of values to a selection from a drop list?
Hi. I am trying to assign a set of values, in separate fields ,to a each
selection from a drop-down list in Excel (using data validation). What I am
trying to do is very smilar to, say, to selecting a SKU from a list and
having the product description, unit price etc. fill into their corresponding
fields automatically. For example, I pick SKU "11111" from a drop-down list
to fill the SKU field, then, automatically, "5-inch widget" comes up in the
product description field and "$5.00" shows up in the unit price field.
Along with the d...Can I remove blanks from a range without using sort?
I have a range of cells A1:a10, say, which obtain data from another source.
Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
How do I reorder this range such that the cells containing information are
listed together, removing the blanks? I want to do this using a formula,
rather than filter or sort, as the data, and hence the blank cells, will
change, and I want to perform analysis on the cells containing data.
from one of the newsgroup correspondents
use this code statement
Range("a1:a10").SpecialCells(xlCellTypeBlanks).EntireRow.Del...How to copy a filtered range ?
In a range of cells that i have filtered by
edit>go>special>formulas>ctrl+9, therefore remained only rows with
constant values, now that i need to copy these rows on a new book to
upgrade their values and repaste them on the original book in the same
column but without overwriting the hidden rows which contains formulas,
is that possible ?
Thank you very much .
gaftalik's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6450
View this thread: http://www.excelfor...change range for multiple charts
I need to change the range for multiple charts, i.e. i have to go chart by
chart and modify, either with the mouse or by typing, the new range, is there
a quicker way to do this?
"Yossi evenzur" wrote:
> I need to change the range for multiple charts, i.e. i have to go chart by
> chart and modify, either with the mouse or by typing, the new range, is there
> a quicker way to do this?
I've written a little Excel add-in that performs mass edits of series
How to Edit Series Formulas
http://peltiertech.com/WordPress/how-to-edit...Count over group
I have a view that I want to count the occurrance of a FedIDNo only once in a
group of linked SSNs (using ROW_NUMBER() OVER ?). For instance, if I have
the following I want to count only 1:
Below is my SQL.
SELECT dbo.ClientInfo.FedIDNo, dbo.People.SSN
FROM dbo.Timesheets INNER JOIN
dbo.ClientInfo ON dbo.Timesheets.ClientLinkID =
dbo.ClientInfo.PeopleLinkID INNER JOIN
dbo.People INNER JOIN
dbo.PeopleLink ON dbo.People.PersonID =
dbo....Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can
have the pivot table return the number of rides, but what I need is the
number vans that participated for a certain day. Example, it is possible to
have 27 trips done by 18 vans, how can I count the number of vans and not
addup the van number?
Any help appreciated.
We need a bit more information on how your source data table is set up.
What information do you have in each column?
sacredarms <firstname.lastname@example.org> wrote:
> I have a pi...How do you serialize a blank value in XML to GP using eConnect?
Hi, I'm trying to save a customer address with a blank ADDRESS2 field to our
Great Plains database using eConnect 10 for GP 10.
ADDRESS2 has a value in the database, but the user wants to set it to blank
(delete it). I update the object, then serialize it, and pass it eConnect.
The problem is that the XmlSerializer class serializes only non-empty fields
object to XML. And fields not present in the XML are not overwritten in the
database (explained here
http://msdn.microsoft.com/en-us/library/bb625...Displaying values vs formulas
I'm having another brain-dead moment and I hope someone can resuscitate!
I'm putting formulas in various cells on a worksheet and some of them are
displaying the results and some are just showing in the cell as the formula.
How can I get them all to display results. I swear that all the cells are
formatted the same but the displays are different. What am I doing wrong?
Thanks for any help.
The cells displaying the formula itself and not the results are probably
formatted as Text.
A quick way to find out if this is so, is to select a cell that displays the
formula, the...Accumulating Values in a Pivot table
Does anybody of you know, how I can show in a Pivot table
accumulated values (example)in each row:
Day Amount Accum. Value
1 50 50
3 75 125
8 105 230
.. .. ..
.. .. ..
and so on.
Maybe someone give me a tip.
Thanx a lot
That is one of the big advantages of using Pivot Tables
One place to start would be
Debra Dalgleish's Contextures.com/techtip.html
http://www.contex...Count number of instances
Need a formaula to look at an entire column and count the number of
different times (say date of product)
Do you mean a unique count..=COUNT() will give you a total count of numeric
values (including date, time, numbers etc; ).
Why dont you post few samples...
> Need a formaula to look at an entire column and count the number of
> different times (say date of product)
Yes sorry unique count.
If I have Products in column b
I want to count t...Date dependant values.
Up to 10 times a year, I want to add a value of one to cell V4 starting with
I have come up with the following formula
The problem is that I can only use the IF formula seven times.
I would very much appreciate it if you could write a new formula for me as I
am quite new to the whole thing and it took me weeks to work mine out!
(I have been advised to use the VLOOKUP formula but cannot get my head round
Thanking you in anticipation.
Can you tell us when you wa...VBA Make calculation on range
Is there a way i can make calculations on a range of cells without using
using a for each.. next loop. I want to add 10 to the values in a range. You
can do sheet1.range("A:A") = 10 but you can't do = sheet1.range("A:A") + 10.
Talk to you later
No loop is needed:
Set r1 = Range("B1")
Set r2 = Range("A1:A100")
r1.Value = 10
Gary''s Student - gsnu2007L
> Is there a way i can make calculations on a range of...filter form on value from linked table
Is it possible to filter a form (based on table A) on a value in a table B
where the tables are linked?
I would like to filter the form records (Table A) by using as input the
company name (or part of it) from a textbox.
Message posted via AccessMonster.com
Sorry for this but I forgot
I would like to use like
as I already use it to filter the form based on values from another txtbox on