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)....Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it.
I can't find a Function which will do this. I did discover ActiveSheet.Name but
I have been unsuccessful in getting it work in a function.
Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c"
and need to get these names into cell C3 of each sheet. The sheets may not be
in order and there may be missing sheets.
Thanks in advance for any help.
You could put somthing like this in a macro or attatch ...conditional formatting of row based on cell
I want to apply conditional formatting to a whole row
based on the contents of one cell in that row.
For example, if cell L2 contains the word 'sport', i want
the whole of row 2 to be shaded.
I'm guessing this is pretty simple, but seem to be having
a bit of a mental blockage.
Any suggestions would be appreciated. Thanks :)
formula is =$l$2="sport"
"Katherine" <firstname.lastname@example.org> wrote in message
> I want to apply conditional formatting to a whole row
> based on the contents of one...Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and
deposits. Current balance is determined by subtracting withdrawals and
adding deposits to the balance in the line above.
Suddenly I have started getting a cell entry
in the current balance cell.
A typical entry in the cell is for G43 would be:
=G42-E43+F43 and the sudden new result is #VALUE.
All three columns have cells formatted as numbers.
On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote:
> I have a spreadsheet with three columns: Current balance, withdrawals, and
> deposit...Re: Is there a way to HIDE a row based on a value of a cell ?
Dim rng As Range
Dim i As Long
If LCase(Sheets("Ctrl").Range("A1").Value) _
<> "yes" Then
For i = 5 To .UsedRange.Rows.Count Step 2
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i, "A"))
Set rng = .Cells(i, "A")
If Not rng Is Nothing Then
rng.EntireRow.Hidden = True
...Row Not Found at the Subscriber
I was wondering if you could help me with my problem.
If you have data in your worksheet and you say delete all the data in the
last row, when you say F5 (Goto) "Special" and select "Last Cell" then it
will always go to the data row that you have just deleted. There seems no
way to get the "Last Cell" to update to where the last data is. Does anyone
know to resolve this issue please.
Thanks in advance.
Deleting the data won't impact the "Last Cell". The "Last Cell" doesn't
refer to the current data. It refers to the la...Extend text to next cell
I am trying to get my text to run over to the next cell only in display not
actual value of that cell. So if A1 says, "How now brown cow.", it should
show over cells A2 and A3. However, I also need the carriage returns to be
picked up. If I use the "Wrap Text" property it only shows the sentence in
that cell. If I don't use it I get the  (brackets) in place of the carriage
Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?)
But be aware that merging cells causes trouble with sorting, pasting, and lots
of other stuff.
Format|cells|al...Selecting rows from various sheets #4
shav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1198
View this thread: http://www.excelforum.com/showthread.php?threadid=26665
...Creating a list of info in A1 cell in multiple worksheets
How can I create a list of the info/contents in the A1 cell in multiple
worksheets? I am trying to create a summary worksheet of the data from 90
worksheets and would like to be able to list the headers in the A1 cell
'Right click on the summary sheet tab, and paste in this macro.
'Edit where appropriate:
'Starting row for summary:
i = 1
For Each Sheet In ThisWorkbook.Sheets
If Sheet.Name <> Me.Name Then
'Control What column to place data in
Me.Cells(i, "A").Value = Sheet.Range("A1").Value
i = i + 1...Inserting rows slow on large report
I have a large report I maintain with 3 data tabs and 2 report tabs, 1 for
top 10 customers, about 400 rows, and another for all other customers, about
4000 rows. Every month I have to update it for any new customer/product
combinations. I have optimized the formulas to the best of my abilities and
gotten a full recalc time down from 3 min to 30 sec, which is acceptable. My
biggest problem now is that every time I need to add a row (or delete one for
that matter) it takes about 15 seconds (on the 4k row report, adding to the
top 10 customers report takes 3-5 seconds), per row....Data from non-adjacent cells as input for Linest
I would like to use data as input for Linest that is in the following
known y: A1 to A3, A10 to A12
known x: B1 to B3, B10 to B12
I have the feeling that this is almost answered in the following
posts, but I do not understand it:
...delay in displaying cell contents
Is there a simple function that can delay the display of the contents
of a cell by a few seconds. I know that it can be done as VBA code
but I dont know how to do it.
Thanks in advance for any help.
have a look at the Wait method in the VBA help
"Dave" <email@example.com> schrieb im Newsbeitrag
> Is there a simple function that can delay the display of the contents
> of a cell by a few seconds. I know that it can be done as VBA code
> but I dont know how...Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot
of fiddling to make sure the formatting is correct, calculated fields
get added and chart series ranges collect all the data every time I add
a new row.
The insert (row) command seems to take care of all of this but it
inserts the empty row above the current row. As I'm always appending
data to the sheet, I would prefer this to be below the current row.
I've tried adding a dummy row under all the data containing the
formulas and formatting I need but unfortunately I get formula errors
and it screws up my chart....Automatcally Hiding Rows in Excel 2000 #2
Is there a way of automatically hiding rows in Excel 2000 based on a rule
(ie if there is no data in a row, we want to hide that row without doing it
manually)? We have a couple of ideas which involve VB code which reacts to
data within that row but if anyone has done this before or knows of a way to
do it, that would be great.
Thanks in advance!
Was a reply you got to your first post.
Tom Starr wrote:
> Is there a way of automatically hiding rows in Excel 2000 based on a rule
> (ie if there is no...Updating 20k+ rows from Excel to access. vbscript? VBA? SQL?
Some of these rows only need a few columns updated, and others need
all of them updated. If possible if say row 800 column 'S' is filled
in the access DB but is blank on the excel spreadsheet, if possible i
like to keep the existing data in access and over right it with a
The spreadsheet is a export of the access DB, changes were made/merged
from other spreadsheets so the Unique ID is the same and in column A.
A script suggestions to complete this?
set dbe = createobj("dao.dbengine.36")
set db = dbe.openda...Edit specific range in protected worksheet
I have a workbook with multiple worksheets. I protected a sheet but some
menu does not work when it is protectd. e.g. i want to go edit > fill >
How it can be possible.
Thanks in advance
Rao Ratan Singh
If it's protected, you simply can't. You'd have to unprotect it first. Or
write a VBA procedure to unprotect, do the fill series, reprotect.
"Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message
> Dear Sir/Mam,
> I have a w...grouping cells to sort by only one column
New to this so please bear with me.
I have a spread sheet where I am listing names in column A and othe
info relating to that name in columns B and C in the two rows below th
I then start a different name in the next row down with the info in t
two rows below that, and so forth.
I want to be able to add more names in the future and be able to sor
all of this by column A only. The problem I am having is the info i
the other columns moves. I need it to somehow attatch and stay put wit
it's corisponding name without moving.
In other words how do I make everything from A1 to ...2 existing spreadsheets show 1st line rather than header row to sort ...
This is odd, I haven't seen this ever before. I was working on
spreadsheets yesterday for a mail merge. Two had headers rows. I
cleaned up both documents, put print area and then tried to sort. In
both cases, what came up in the sort was the first row below the
header where one always sees the actual titles of the header row when
one selects the Header Row radio button under "My List Has" in the
I fiddled and fiddled a bit and managed to get one of the workbooks to
display the sort properly by the header row titles - without figuring
out what ...how do i search for a specific name in an excel file
i have a large file, (49,000+ names) is it possible to search for a specific
name somehow without scrolling up and down the list repeatedly looking for it?
Hit CTRL+F to open the find window, and in Look in: select Values.
pinmaster's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6261
View this thread: http://www.excelforum.com/showthread.php?threadid=496546
Did you try Edit > Find ?
> i have a large file, (49,000+ names) is it...How do I link many cells to one particular cell? #2
I am trying to link multiple cells to one particular cell and can't seem to
do it. I want the multiple cells not just to have the same information but
be connected to the same cell. I want multiple cells to take the information
from ex. cell D4. So everytime i change cell D4 every cell that is linked to
it changes. Do I have to input =D4 into every single cell that I want to
link or is there an easy way to mass link?
enter =$D$4 in one cell and copy it to the rest. The $ keeps the address
from changing relatively.
Are the cells on the same worksheet?
If yes, you could select...Unlocking Cells
Hi, I'd like to know if there's a simple way to unlock or lock cells.
I'm not sure if "Lock" is the correct term here. For example, if you
have a formula that has C$14. Is there an easy way to unlock (remove
the "$") from the formula or to ad it without manually doing it?
Thanks for your help!
You can use a macro to change the reference style (absolute vs relative), but
you can also do it manually.
Select the portion of the formula (just a single cell reference or the entire
formula if you want) and hit the F4 key to cycle through all 4 options.
lj ...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.
...How do I restore the cell content window on my toobar?
tools options view ........formula bar
Greetings from New Zealand
"hutchc" <firstname.lastname@example.org> wrote in message
From the View menu, choose Formula Bar
Excel FAQ, Tips & Book List
...more than 65, 500 rows
Is there a product that allows more than 65k rows?