Reading a cell based on a vairable
I am trying to do something really simple, but getting a bit stuck.
I have sheet1 with a unique list of names, and sheet2 with a list of
the same names (not unique) and a number next to then. I would like to
go down the list in sheet1, and put a total next to the name, from all
the entries in sheet2 which match.
The first thing I want to do, is read the contents of cell A1 into a
variable. I need to do it in a loop though, so I need to reference the
cell via this.
How can I do something like:
variable = cell( a, rowNum)
(where "variable" and "rowNum" are variabl...Can a cell be turned on or off?
I would like to be able the have the total in any one of the A cells to add
up only if the cell is click on. In other words, can a cell be turned off or
on as needed?
Right click sheet tab>view code>insert this
Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Then Exit Sub
tr = Target.Row
Target.Value = Cells(tr, 2) * _
(Cells(tr, 3) + Cells(tr, 4))
Microsoft MVP Excel
dguillett@g...Total PC Defender
Would anyone know of a surefire method of removing Total PC Defender &
I think I've been able to remove Total PC Defender (partially), but not the
Trojan. AVG free still isn't allowed to update, Malewarebytes wont run, and
my administrative rights have changed... wont allow me to install Windows
I'm hoping someone has written a stand alone DOS utility or removal tool...
would be nice.
Kindof pulling my hair out over here!
Thanks for any help.
Richard in VA
Richard In Va. wrote:
> Would anyone know o...MS Excel Cells
can i split a single cell in excel diagonally and put type in each side ?
Short answer is no.
But you can fake a lot of things
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Ddman" <Ddman@discussions.microsoft.com> wrote in message news:B6E85364-9...Posting 60 messages per second to CView locks up UI (animation continues...)
As recommended by Jerry and Scott in this newsgroups, I experimented with
CreateTimerQueueTimer and PostMessage(WM_APP_...) to drive animations in a
CView derived window. I have 2 types of animations: GDI based and Direct3D
based. I noticed that Direct3D based animations were really smooth when I
ran them at a frequency which was close to the adapter's refresh rate: 60hz.
I was quite pleased to see the smoothness of the animations and a very low
CPU usage (alternating between 3-4%).
Unfortunately the app's UI (e.g. closing/sizing/moving the frame, menus,
etc.)...Setting recordsource property in VBA Access 2007
I'm trying to create a report in Access 2007. I cannot figure out how to set
the recordsource propery in code.
I either get #Name? if the property is not set at design or I get the
original dataset base on the value of recordsource at designed, not the value
that I set in the report_Open event.
Does anyone know what I need to do to be able to set the recordsource
property in code?
>I'm trying to create a report in Access 2007. I cannot figure out how to set
>the recordsource propery in code.
>I either get #Name?...Set up form fields to that Tab can be used to move from one to ano
Hi! I'm creating a form in Excel 2003 with a variety of text boxes, check
boxes, and drop-down fields, all created using the Control Toolbox. I'm
having trouble figuring out how to set these form elements up so that the
user can press the Tab key to move from one field to the next. The Properties
dialog box for the text boxes has something called TabKeyBehavior, but when I
set it to True, all it does is allow you to insert a tab space within the
text string in the field. I want to use the Tab Key to move the cursor to the
next field in the form. I have been unable to find...Setting global variable with result from SQL statement
I am trying to obtain the status of a sales order, and store that status in
a global variable.
There is only ever 1 record that is returned from my SQL statement which
obtains the status, but how do I put the result into the global variable ?
I can amend the SQL to put the result in a table, but that just pretty much
puts me in the same position.
The only thing I can think of, is to open a form with the results of my SQL,
and set the variable to the value of the linked text box on the form, then
close the form, but this would be very messy, and I'm sure its not the
correct way to...Using vlookup against blank cell
I am using vlookup to return values (lets say values A-I) based on other
values (Values 1-10) in column A.
Sometimes there is no value in column A and I'd like vlookup to return a
value "blank cell" or whatever I define.
Right now blank cells return N/A.
I am guessing there is something simply I can do there.
Thanks in advance
In article <#2YO5vFFIHA.4752@TK2MSFTNGP04.phx.gbl>,
"bam" <email@example.com> wrote:
> I am using vlookup to return values (...Comparing two sets of data
I need to compare sets of data in two different
spreadsheets in two different columns labeled the same,
Grant Number. We are trying to track if both sets have
the same grant numbers or if there are duplicates.
Did you adjust the ranges from column Z to whatever column held your data? (and
did you remove the extra trailing close parenthesis?)
Another formula that I use is this:
I insert a helper column to the right of the Key data.
Then I use a formula like this in Grant1 (assumes data is in column B of both
I have 4 cells: H8 I8 J8 K8
If change H8, I8 J8 K8 cells have variable value.
I want to change values of H8 from 10 to 150 and collect all 4 cells value
in columns AF AG AH AI.
Please help me with same VBA code. Thanks.
This routine will do the data entry for you and capture the results. To put
the code into your workbook, open it and press [Alt]+[F11] to open the VB
Editor. Use Insert --> Module in it to open a new code module, then copy and
paste the code into it. Select the proper worksheet and then run the Macro.
Dim homeCell As Range
Dim ...Pivot Table (blank) Cells
Can I remove the '(blank)' entry that appears in pivot table cells that have
no contents? Thanks
Select one of the cells with (blank) in it.
Hit the space bar and hit enter.
This is really replacing the (blank) with a single space character.
koala824 at Comcast wrote:
> Can I remove the '(blank)' entry that appears in pivot table cells that have
> no contents? Thanks
Pretty slick. Works great, even if I cannot follow the programming logic of
it all. My mindset never considered actually editing the contents of a pivot ...How can I add more than 2 text box values?
I am working on an accounting database with differening exchange rates. I
have been attempting to create one text box with a balance value after using
the correct exchange rate.
For instance I have Box1 = 2345.00 pesos and Box2 =2,100.00 (exchange rate)
and I have Box3 with a dollar ammount. How do I divide Box1 by Box2 and add
Box3 for a Balance, that I can use in a report or in an other table.
I've tried using =sum([Box1]/[Box2]+[Box3]) on the form side, and on the
query side but it gives me an error. and I still don't know how to label it
to be used by a report or to be used wit...Global settings size limitation on Exchange Server 2003
I set send and received mails as 1MB max. from global settings on Exchnage
2003 server. Then some users need more over 1MB sized mail. I set user
settings of this users to 5MB. But they cannot send or receive any mail over
1MB from outside of organisation. I searched and found that user settings
cannot override global settings for internet mails. How can I increase
limitations for special users. By the way, I try to ommit 1MB limit for all
as temporarily. I changed global settings as "no limit" but still I cannot
send or receive any mail over 1MB. How can I solve these problem...Clearing the Contents of Merged Cells
How would I implement VBA to clear the contents of a range that contains
merged cells? The following code results in a runtime error '1004' :
"Cannot change part of a merged cell."
If Union(ActiveCell, Range("ProbArea")).Address =
Ans = MsgBox("Do you want to CLEAR the contents of CURRENTLY
SELECTED CELL'S ROW?", vbYesNo)
If Ans = vbNo Then
If Ans = vbYes Then
Application....Fill drop list
i want the user to be able to open a document like word or excel by selecting
it from a combo drop list. how can i fill the drop list with the files of a
On Mon, 7 Dec 2009 09:00:01 -0800, Pastor Del wrote:
> i want the user to be able to open a document like word or excel by selecting
> it from a combo drop list. how can i fill the drop list with the files of a
> particular folder?
Copy and Paste the below code into a Module.
Public Sub GetlistFiles()
' fill a List Box with the names of files found in folder
Dim MyName As String
Dim FS As...Setting of @@FETCH_STATUS
I have a snippet of code from a trigger:
DECLARE @Saved_ID int
SET @Saved_ID = (SELECT max(ID) FROM Table
WHERE Name = @UserName)
IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL
Turns out my code ALWAYS runs the INSERT. If I add a temporary variable:
DECLARE @Saved_ID int
DECLARE @SavedStatus int
SET @Saved_ID = (SELECT max(ID) FROM Table
WHERE Name = @UserName)
SET @SaveStatus = @@FETCH_STATUS
IF @SavedStatus = 0 AND @Saved_ID IS NOT NULL
This code works.... The only thing I can figure is the "IS NOT NULL&qu...fill list box with file names
how to fill list box with file names sorted by date - newest first.
On Tue, 20 Apr 2010 09:04:46 -0700 (PDT), mcnews <firstname.lastname@example.org>
>how to fill list box with file names sorted by date - newest first.
Look at the VBA help for the Dir() function, and use a loop to fill a Table.
Use that table (or a query based on the table) as the Row Source for the
John W. Vinson [MVP]
1. What is the make of the table holding these names?
2. What is the name of the date field?
3. What is the name of ...How to set sales target for each salesperson?
1. How can I set sales target for each salesperson for a specific fiscal
Can I use the "salespeople with quota" section? My machine has disabled it
due to wrong fiscal year date setting.
2. Can I change the fiscal year settings to enable the section again?
3. Can this section set different sales targets for different sales people?
Please advice me.
Tnx in advance
...Setting archive rules on Multiple folders??
I have about 100 or so folders and sub-folders etc in my PST file... Into
which I organise all the emails I handle.
I want to set up archiving rules on them.
As far as I can tell the only two ways I can do this is 1) Set the DEFAULT
rule and apply it to ALL FOLDERS in one swoop.
2) Set the Archive setting on each individual folder.
Option 1 is not much use to me because I need different settings on various
folders... many I don't even want archived.
Option 2 is going to take a really really long time... because I am not able
to select more than one folder at a time nor can I...I have opened an Excel file and I can't edit any cell
Please post any questions in the body of the message
this is considered rude
Are the sheets protected?
Tools > Protection > unprotect worksheet But you will need a password
...Use percentages instead of values
I have a series of numbers that show how a hard drives space is being used.
The table shows the figures in GB values. How do I get a pie chart based on
the table to show the % per section and not the GB value?
In the data labels tab of the format series or chart options dialogs, you
can select percentage instead of value for display in the labels of a pie
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
"Keith" <Keith@discussions.microsoft.com> wrote in message
news:06...Is there some kind of color reference table for values?
Im using a flexgrid that lets me set fillcolors etc...
i saw some code that defined some colors but i wanted to know if there was a
chart so i can figure out my own colour schemes..
the code was...
Const conCream As Long = 13303807
Const conNavy As Long = 8404992
Const conPaleGreen As Long = 13434828
Const conLightBlue As Long = 16760445
On Tue, 23 Oct 2007 21:49:15 +0100, "Craig Armitage" <email@example.com>
>Im using a flexgrid that lets me set fillcolors etc...
>i saw some code that defined some colors but i wanted to...alphabetical order within a cell
I have lists of postcodes [zip codes] in cells - many codes together in the
same cell - , but only the first two characters. For example: CT, TN, BN,
RH, etc etc.
There are a lot of them. Could someone tell me how to get Excel to order
these alphabetically in each cell that contains them please?
Note..this is not about a single code in its own cell but multiple codes in
a single cell.
Take the code below and put it into a code module. Choose the cell to sort
contents on (one at a time) and run this code/macro. I've tried to break
long lines so that it c...during set up tells me that the ost file is not a offline folder #2
when I went to set up outlook when it attempts to create a default folderit
tells me that the outlook.ost file is not an offline folder - when I click OK
it closes outlook - so I am in a loop