|
Replace
I need a macro that can search several workbooks with alerts disabled find a
number say (5-510) in a several cells in several worksheets in each workbook
replace it with a new number say (5-511). Save and close. Also I need to
know which workbooks it changed values in. The name is also in cell c2 on a
Data Entry worksheet.
Thanks for the help.
I've tried several things but could...
|
5 |
12/30/2008 4:24:23 PM |
|
|
UDF to copy formatting as well as text?
Hi All.......
Someone, (I don't remember who), sometime, (I don't remember when) was kind
enough to post this fine UDF.
Function copycomment(r As Range)
Application.Volatile
Dim whereami As Range
Set whereami = Application.Caller
If whereami.Comment Is Nothing Then
Else
whereami.Comment.Delete
End If
whereami.AddComment Text:=r.Value
copycomment = ""
End Function
It works gre...
|
2 |
12/30/2008 4:09:08 PM |
|
|
How to create "permanent" toolbars
I was just upgraded from Office 2000 to Office 2003 here at work. (I
know, this is the state!) In XL2000, I could create tools and put them
on the standard toolbar permanently, but I can't seem to do this with
XL2003. I think it's because it now longer calls for the "PERSONAL"
workbook where all the macros behind the buttons lived. Is there a
workaround for this? If I can't get the tools in t...
|
5 |
12/30/2008 4:04:17 PM |
|
|
first column in row
Hello,
I want to colour the first column in the active row to black, to highlight
to the user which row is being edited (just to assist in end user comfort!).
I have the follwoing code which doesnt work, can anyone help?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(Cells(ActiveCell.Row, 1)).Interior.colourindex = 1
End Sub
Thanks in advance. Sorry it's basic,...
|
3 |
12/30/2008 3:44:01 PM |
|
|
what is wrong with this code? (when used in the excel_addin)
hey friends,
public int addme(params object[] list)
{
// sum all the integers included in list
int sum = 0;
foreach (object o in list)
if (o.GetType() == typeof(int))
sum += (int)o;
return sum;
}
this code works fine as a stand alone code but when am trying to invoke the
"addme" function from excel by...
|
3 |
12/30/2008 2:56:43 PM |
|
|
How to Strip Leading Spaces and Underscores from Object
I have a string that captures text from a mainframe and would like to know
how to get rid of the underscores and spaces.
Here's an example of what I have
Dim SMPL as String
SMPL = CurrentScreenObject.GetString (3, 30, 25)
Range ( "A14") = SMPL 'text is put on the spreadsheet
Here's an example of what would show up on the spreadsheet .....
___ TEXT
...
|
7 |
12/30/2008 2:12:05 PM |
|
|
moving within forms
Hi All.
I have a user form that acts as an inputbox, and there are 6 fields withing
the form.
None of the fields require any input - they can be left blank, but if a
value is input, it must be numeric. However, if there is a value entered into
say field3 then a value IS required for field1. So if field1 is blank, I give
an error message. What I need to do, once the error message is cl...
|
6 |
12/30/2008 1:59:01 PM |
|
|
AddDiagram Not woring in Excel 2007
Hi,
I am trying to insert a diagram using adddiagram it is showing the
error in excel 2007 same code works in excel 2003
My code is below
Dim wksActiveSheet As Worksheet
Dim shDiagram As Shape
Set wksActiveSheet = ActiveSheet
Set shDiagram = wksActiveSheet.Shapes.AddDiagram( _
Type:=msoDiagramRadial, _
Left:=20, Top:=40, _
Width:=400, Heig...
|
3 |
12/30/2008 1:25:21 PM |
|
|
Offset
Hello,
I am trying to write some code that will select row32 of the active cell.
This always needs to be row 32 becuase I want to get the interior colour
value of that cell and apply that to the active cell. Here is my code so far:
ActiveCell.Offset(Right(ActiveCell.Address, 2) - Right(ActiveCell.Address,
2) + 32, 0).Select
If I put this into a message box then it always returns 3...
|
5 |
12/30/2008 12:04:01 PM |
|
|
Postion view
Hi,
I have the following code which works perfectly. It finds the users log in
name from a range of cells and highlights the cells, in this case 30 cells in
a single row.
The problem is I want to position the first cell (column 118) just left of
the first column not frozen (column I on my sheet). At the moment it selects
the cells but part of them are off the screen and you need to...
|
3 |
12/30/2008 9:49:01 AM |
|
|
code to change font color of row
I've got this code written to change the font color of a row based on the
value entered into column A of that row, but the color does not change
this code fires from within the worksheet -like an extended conditional
format
interesting note... I have a similar code written to update the color of all
the rows on the worksheet when the workbook itself opens, and that code
works fine
a...
|
2 |
12/30/2008 8:57:35 AM |
|
|
code for dynamic range
I am having trouble with "offset" commands in macros.Can someone help me with
code to do the following:
I wish to copy a range two cells to the right of a given cell (after
Selection.End(xlDown).Select) through to the end of the data to the right.
(anything from 1 to 50 columns)...
|
13 |
12/30/2008 8:57:01 AM |
|
|
How to make active cell border prominent and keep entire active row selected for better visibility
Hi everybody!
For better visibility in Excel, I want to highlight the borders of
activecell only as long as the cell is active. Plus I also want to keep
the entire active row selected in some of my workbooks/sheets.
I can achieve the result by using Worksheet.SelectionChange but I dont
know how to switch off the border shading once I move to some other cell
and also keeping entire row se...
|
3 |
12/30/2008 8:51:00 AM |
|
|
(ADO)Getting data from access database Memo field
Tax-82.54 Disc-0 Online-206.50
Instant-503.00‡CC-146‡‡Visa-139.14‡MC-‡Debit-187.26‡‡PM-3.00‡RJR-‡Lor-‡S&M-‡USST-7.00‡Other-‡Total Coupons-10.00‡Safe Fund- 300.00
I need to take the string above and break it up to look like this below
Im trying to put this into a multiline textbox. Im guessing the cross
looking things are the enterkey. Some single and some double...
|
2 |
12/30/2008 8:37:51 AM |
|
|
excel_addin
hi all, am trying to create an excel addin using c#.net and when i try to
build the project and open a fresh excel document there is a pop up coming
which says: "Value does not fall within the expected range."
and following that another pop up is coming up which says: "Object reference
not set to an instance of object"
can any one here please help me out with this? what is the problem???...
|
2 |
12/30/2008 7:56:27 AM |
|
|
excel addin
hi friends, I am trying to populate a data set retrieved from the database on
to the active cell of an excel sheet. I got it by making use of a button and
handling the event of its click, but how to return an entire dataset directly
onto the active cell when for example, the user simply says =get_table(...)
in an excel cell?...
|
3 |
12/30/2008 6:22:01 AM |
|
|
On Error GoTo stops working
Hi
I have on On Error Goto inside a loop and the first time it has an error it
goes to the section which performs are task but the second time it has an
error it crashs and I get the error 9 message.
Here is my code
Dim stName As String
Dim stThe As String
Dim stLetter As String
Dim dbLong As Double
Sheets("Temp").Select
Range("A2").Select
Do
stName = ActiveCell.V...
|
5 |
12/30/2008 5:54:29 AM |
|
|
Select range between column and row
In a large field of data I have definded a row range.
colAdd is a string = $H$1,$I$1,$J$1,$K$1,$L$1,$M$1
I want to define all the cells down for all columns using something
like Selection.End(xlDown)
Ive tried severa manipulations
Set preSrcRng = Range(colAdd).End(xlDown)...
|
5 |
12/30/2008 4:26:21 AM |
|
|
Copy value from worksheets
Dear gentlemen
I have about 250 worksheets in a workbook, each worksheet has customer name
which appears on cell "F2" of each worksheet, what I need is to be able to
make a summary in a new worksheet in such a way that in cell "A2" I would get
"F2" of first customer and in cell "B2" I would get cell "J3" of that
customer.
Then on cell "A3" I would get "F2" of second worksheet and in "B3"...
|
6 |
12/30/2008 4:02:01 AM |
|
|
New Menu tab in Excel 2007
Hello
I have migrated to excel 2007 from excel 2003.I have few macros which
when executed create a new tab in excel menu . Now when i run the
same
in excel 2007 the macros are seen under Add-In menu in ribbon. Is it
possible to get it on the main menu.
Thanks in advance
Shantanu
...
|
8 |
12/30/2008 3:01:42 AM |
|
|
Collect Info from Wkbks in a Folder with Criteria to 1 sheet. CHAL
Hi Everyone,
Here is what want to do:
I need to collection information with X amounts of sheet in one particular
folder that meet a particular criteria (let's say find all those line/records
that are December in a certain column range) and then store all of those
information in one Sheet name Summary).
Basically what I want is a summary sheet of all the workbooks in on folder.
I d...
|
21 |
12/30/2008 3:01:00 AM |
|
|
control toolbox vs forms toolbars
Hi,
I have BIG issue!
On Sheet1 I have 2 OptionButton's (OptionButton1 and OptionButton2, as name
box shows me).
Problem is in the fact that they are from Control toolbox and they are not
linked to any cell or variable.
I need to check which one is selected!
I do now the procedure when working with Forms toolbar but how to work with
OptionButton from control toolbox which is placed on s...
|
4 |
12/30/2008 1:51:43 AM |
|
|
Extracting numbers from a single sell
Hi, there!
The situation goes like that : I have a messy cell that contains both
adresses and a couple of phone numbers per client. The problem is that
i need only the phone numbers. Is there a way that i can exctract each
one of them in a different cell - i mean if this client has 3 numbers
specified in the messy cell, they should be distributed in 3 separated
cells. Also as you can guess...
|
14 |
12/29/2008 11:37:47 PM |
|
|
Format Total but not GRAND Total rows
Once a sheet is SubTotalled, need to put some formulas underneath each row
where Total occurs, but not under the Grand Total riow.
Note: The text in every total row contains a dash, ie "-" .
Tried the following, but it doesn't work ....
For Each c In Range("a2:a500")
If InStr((c.Value), "*-*Total") Then
c.Offset(1, 0).EntireRow.Insert Shift:=xlDown
Thanx in advance for a...
|
1 |
12/29/2008 11:04:01 PM |
|
|
Format Total but not Grand Total rows
...
|
1 |
12/29/2008 11:01:01 PM |