Macro question #8
I want a macro to select a row (or cell in a particular row) on a worksheet
that corresponds with a value in a cell eg.
Cell shows value 5. Macro selects row 5 on another sheet
Cell shows value 5. Macro selects cell at intersection of row 5 and a
specific column (say column 2 ?) on another sheet
What do I need to include in my macro to do this?
Can anyone help ?
One way to select the row:
Dim myRng As Range
Dim otherWks As Worksheet
Set otherWks = Worksheets("sheet2")
Set myRng = Nothing
On ...Message box warning of missing data
When a user inputs data (numbers) into cells in column D, they should
include a three digit prefix before the numbers. I would like a message box
to pop up if they fail to do this and delete what they have put in the cell
thus forcing them to try again. For example 09/123 would be rejected but
SUM09/123 would be ok.
Thanks in advance.
Traa Dy Liooar
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the
right blank portion. Get back to to workbook and tr...Active cell
this is my VBA Macro:
I want to select "clastrow" as my active cell. is that possible ?
Dim clastrow As Long
Dim olast As Range
clastrow = Cells(Rows.Count, "I").End(xlUp).Row
Set olast = Range("I" & clastrow)
isum = Application.WorksheetFunction.Sum(Range("I4:I" & olast))
ActiveCell.Value = isum
Not quite Jeff,
You get the last row, cLastRow, but then use that cell rather than the row
in the formula. Try
Dim clastrow As Long
Dim olast As Range
clastrow = Cells(Rows.Co...How can I update the data listed in the combo box to show the most
I have a Database where tables are linked back to the master data tables.
The main entry/edit form has a section (as shown) the new Carer record is
entered using a separate form then the detail is called from the main form
using a combo box.
My problem is that the newly entered Carer data does not appear in the list
of carers. F9 does not work, closing and re-opening the main form does not
work. When I check the item is listed in the Carer table.
My question is how can I update the data listed in the combo box to show the
most recent record.
I would be really grate...How do I include cell contents from a form in generic statements/strings within the form?
I have a form that contains NAME in cell A:1, and GENDER in cell B:2.
I want to build a library of generic statements along the lines of:
NAME tries hard, but if HE/SHE tried harder HIS/HER results would be much
How can I make these statements get NAME from A:1, and derive HE or SHE and
HIS or HER from GENDER in B:2?
=A1&" tries hard, but if "&IF(B1="M","he","she")&" tried harder
"&IF(B1="M","his","her")&" results would be much better"
(there's no ...Displaying the Sigma Symbol as a Column heading
Excel ver 2000
I would like to use the Sigma symbol as a character of a Column heading.
I have two questions:
Does Excel have the ability to display these symbols as text? What is the
process for doing this? I tried looking at the WingDing fonts but could not
find the symbol. Where can I obtain a keyboard mapping of the WingDing
You can see it in Internet Explorer at
you can make Firefox show same if you add fonts, and you will
have to redo each time a new version of Firefox is installed.
http://www.mvps.or...Sharing user data on home, office and Powerbook computers
I have Office X on several different computers and would like to keep
all my user data the same on each of them. Is there an easy way to
keep them all in sync? Most important to me is the info in Entourage
(addresses, calandar and to do lists) but, it would be great to keep
the documents in sync too. If anyone can help, I would surely be
On 9/13/03 10:18 AM, in article
firstname.lastname@example.org, "Clayton Townsend"
> I have Office X on several different computers and would like to keep
> all my user d...Importing Data From Excel
I have been trying to import transactions I entered into excel, but I have
been unsuccessful. Can anyone provide me with some suggestions on how I can
import the transactions from excel into Money 05 Small Business
"Lou" <Lou@discussions.microsoft.com> wrote in message
>I have been trying to import transactions I entered into excel, but I have
> been unsuccessful. Can anyone provide me with some suggestions on how I
> import the transactions from excel int...chart type affecting data order
When I change the chart type from bar to line (for example), it changes the
order that the data is displayed in the data table. How do I keep data in
the data table in the same order that I entered it?
You would have to use an alternative to a data table. Make a table in the
worksheet, where you have much finer control over what appears, where it
appears, and how it appears (formats). Then either situate the chart close
to this table, or use the camera tool to paste a dynamic picture of the
table one or near the chart.
Jon Peltier, Microsoft Excel MVP
Tutorials and Custo...Chart Data Won't Display
Hello, I've inherited a line chart. I successfully
changed the data range last month by dragging the outline
box. This month, though, I can drag the outline box, but
in the chart, the value won't show up. Does this make
sense to anyone? Thanks!
...I can't find the INVOICE TOOLBAR
I'm using the Invoice template which has an "autonumber" button. It requires
me to open the INVOICE TOOLBAR which I can't find. It does not show in the
list of toolbars.
i've not used the invoice template provided by MS .. but i have written an
alternative system to give people an some ideas if they'ld like to create
their own invoice / ordering system ... if you'ld like a copy of it, email
julied @ hctsdotnet dotau
"AhSee" <AhSee@discussions.microsoft.com> wrote in message
news:6CBF5420-5EFD-42FD-8A22-B...lookup multiple values in single cell
Hello there -
Im looking for some help with a lookup.
I have two spreadsheets.
One with a simple list of numeric values:
The other has multiple values in a single cell:
7785 1008 26885
The delimiter does vary, but it is always a non numeric.
Id like to be able to find the row number in spreadsheet B where m
value from Spreadsheet A appears.
Thanks very much for your time.
--------------------------------------------------...how to view changes to data by user?
How do I view changes/updates made to a database by various data-entrier by
their names and time of the day?
Any info is appreciated!
code: AddFieldToTable, AddDateUserToTables
Add these fields to every table:
UserIDc, long integer -- userID who created record
UserIDm, long integer -- userID who modified record
DateCreated, date/time -- date/time record was created
DateModified, date/time -- date/time record was modified
DateCreated can have a default valut --> =Now() and it will get filled
automatically each time a record is created
UserIDc can ber filled o...Column Size
I've been playing around with placing objects in specified
cells. However, I've noticed that the standard column
width is shown as 8.43 (64 pixels) - can you tell me what
this measurement is?
I think the standard row height is in points - why the
Width is dependant on the font.
8.43 denotes that many characters can be displayed in the default Arial
With the display set at 100%, note how many columns are visible on your
Now, <Tools> <Options> <General> tab,
And change the default font to Algerian (right ab...Convert Data to columns heading
Product | Date | Sales
A | Jan94 | 200
A | Feb94 | 300
A | Mar94 | 400
A | Apr94 | 500
A | May94 | 600
A | Jun94 | 700
A | Jul94 | 800
A | Aug94 | 200
A | Sep94 | 300
A | Oct94 | 400
A | Nov94 | 500
A | Dec94 | 600
B | Jan94 | 200
B | Feb94 | 300
B | Mar94 | 400
B | Apr94 | 500
B | May94 | 600
B | Jun94 | 700
B | Jul94 | 800
B | Aug94 | 200
B | Sep94 | 300
B | Oct94 | 400
B | Nov94 | 500
B | Dec94 | 600
How can I convert it to:
Product | Jan94 | Feb94 | Mar94 | Apr94 | May94| Jun94 | Jul94......
----------------------------------------------------------------...Link Chart with data cell (cl
This was your questio
I want to link all the points in my xychart with the rows of the data that are in the chart. i.e. after a mouseclick on the chart data the user will be redirected to the data row
The idea is to create a link to a text cell, situated in the same row as the data, which explains the details to each point
Thanks in advance for your help
...column widths and row heights
based on 100% view, when u set the column width to say 12 units and the row
height to, for example, 15...
what is the actual dimensions in reality in mm.
ie, how can i get the required number to ensure that row and colms are
exactly 2 mm in width and height
Column width and row heights defaults are not in inches or mm.
The number that appears in the Standard column width box is the average number
of digits 0-9 of the standard font that fit in a cell.
Row heights are measured in points or pixels. There are 72 points to an inch
and "maybe" 96 pixels to the inch.
For ...Importing data from a text file
I have to import raw data from a text file that Excel cannot break into
columns, and the import is unsuccessful. would VBA be a better choice,
having it specifically search for data? there are 3 specific types of
files and all the files (of the same type) are formatted exactly the
thanks in advance
jbaranski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36862
View this thread: http://www.excelforum.com/showthread.php?threadid=566746
Why can't Excel break the ...How to find duplicates within same column
I am trying to find if there are any duplicate values within the same
column, without removing them. Example
In this example 1121, 3343, 6785 are duplicates. I have a ~20000 rows
spreadsheet and would like to speed up the process.
Thank you all,
"Frank Beltre" <email@example.com> wrote in message
> Hello all,
> I am trying to find if there are any duplicate values withi...Deleting Blank Characters in a Cell
I have a list of names with email addresses of attendees of a seminar
series that I am trying to compile into a list that can be copied and
pasted into an email (using BCC as the methodology for emailing).
The challenge that I have is that when these files were exported from
the database to excel (not sure how), all of the cells contain exactly
100 characters even though the email address may only contain 13-20
characters. Is there a way to "trim" the blank characters after the
last character that is used by a letter instead of blank character
Thanks in advance!
--...Unmask the first digit of credit card number
The first digit of the credit card number needs to be unmasked, so that a
person call see what type of card was used.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=dc90...Comparison of Columns
I have three columns thast represent Set numbers, I want to compare the
values in column "B" with those in column "A" find where they match and then
calculate how many rows they have moved either up or down and put the answer
in column "C"
Col A Col B Col C
1127 1129 +1
1129 1130 +1
1130 1132 +3
1102 1102 0
1131 1131 0
1132 1133 +1
1133 1135 +1
1135 1137 +2
1062 1138 +2
1137 1062 -1
You need to provide much more detail. For example, what exactly do you
mean by "where they match&quo...Hyperlink Macro to 2,500 PDF files?
Fellow Forum Members,
I have 2,500 PDF ebooks on a variety of subjects all contained in one
folder named "Ebooks". I already have all the 2,500 ebook titles
inside Excel in Column A, Sheet 1. Now I want to hyperlink each title
to the actual PDF file and doing this hyperlink operation 2,500 times
is something I want to avoid like the plague. Can anyone out there
please help me in developing a macro that would do this hyperlink
operation automatically 2,500 times?
I'm not a macro expert, but is it not possible to make Excel
automically go to the same Ebook folder 2,500 times a...splitting text from one cell
I need to remove specified data from a single cell and place on the same row
in another column. Not all data will be the same eg 100mg, 200mg etc
Not too clear; what data?
Do you want to extract the number from the text?
This will extract 100 from 100mg and 5 from 5mg:
Bernard V Liengme
remove caps from email
"rogera" <firstname.lastname@example.org> wrote in message
>I need to remove specified data from a single cell and place on th...Locked hidden cells
Is there any way of stopping someone getting the values of locked hidden
cells on a protected worksheet by linking to them on another worksheet?
"John Bundy" <email@example.com(remove)> wrote
> The right person can get into anything they want regardless of protection,
> the more secure you make it the more people you stop but there are always
> some. As far as linking them to another sheet, if they will break
> on one it is just as easy to do on two so I wouldn't bother
Thanks. It seems ridiculous that you can hide data by protecting the...