Lookup Formulas in macros
I am trying to create a macro with a simple lookup function and copy. This
part is fine, however the file I will be looking up with be changing every
week - so I have added some variables.
I was thinking the spreadsheet directory and name could be typed into A10
and B10 of the sheet the data with be imported into "book2.xls" but am
having difficultly with related this to the formula...any help appreciated.
See attempt below:
Dim Importdir, Importfile
Importdir = Range("a10")
Importfile = Range("b10")
...Run a macro when cell value changes
I have a macro that I would like to run, onlly when a particular cel
value reaches a pre-defined value.
I really do not know how to do this - I have looked on help features
but to no avail. any ideas
Message posted from http://www.ExcelForum.com
right click sheet tab>view code>copy/paste this>modify to suit>save
Now when cell c1 calculates to >32 your macro will fire
Private Sub Worksheet_Calculate()
If Range("c1") > 32 Then call yourmacro ' MsgBox "Hi"
"Emea training >&quo...make function wait
I have a Macro that:
runs a function that:
runs a bat file that:
runs an ftp script that:
creates a txt file of the ftp sites directory
This runs fine except the next step in the macro is to import the text
file using TransferText. Access attempts to import the txt file
before the ftp script finishes writing it and I get:
"Microsoft Jet database engine could not find..."
I have tried:
putting a pause in the bat file - Access keeps going not wating for
the bat file to finish
putting the TransferText as the next step in the function - same error
"could not find"
having the...Run macro from shape when runnig macro in Excel 2007
I=B4m recording a macro that tries to run a macro from a shape, but it
doesn=B4t work in Excel 2007. I don=B4t know if this is a problem of this
version because I could do it in 2003.
Make sure the macro you have assigned to the shape is a public sub in a
' Picture1_Click Macro
' Keyboard Shortcut: Ctrl+e
ActiveCell.FormulaR1C1 = "1"
Gary''s Student ...Macro with Vlookup
I am trying to write a simple macro with a vlookup contained in it. The
trick is that the vlookup in the macro must be flexible to look in another
excel file for data. The user will decide the name of the file to go into
the macro. My problem is that when I recored the marco the name of the file
I use as an example gets hard coded in, and this defeats the purpose.
Always copy/paste your code here for comments.
"bruinsinsix" <email@example.com> wrote in message
news:9B6C2B36-A197-498C-...Need help with correct function or input for this problem/scenario
I have a situation where I am creating a borrowing base for a company. (I am
going to shorten numbers to make it easier.)
There is a $300 loan.
Type A loan can borrow up to $300.
Type B loan can borrow up to $150.
Type C loan can borrow up to $50.
I need a formula to input in excel that will give me a borrowing base for
type B and C loans. For example, to borrow in type B loans, I have to check
1-what's outstanding on the entire loan.
2-what's outstanding on type B loans.
3-Take those two numbers and figure out what can be advanced under the
answer to those questions. (I.E. $2...macro not running in XP
My macro is working in NT but not working in XP.compilation error saying
dll not found is coming.actually the applciation contains 3 buttons. 2
buttons out of which are used to open the common dialog box (for
selecting the files destination.)and another button behind which the
entire macro is placed. now the problem is in XP we are unable to even
click the button (to select the path ,error message saying compilation
error dll not found is arising) In tools references
i have checked the vba project and few others but could'nt solve the
Private Sub CommandButton1_Click()
Nam...waiting waiting waiting for 1.2
Got all the way in on a 1.0 install only to have it hit a brick wall because
I have 2003 network.
Called MS to get 1.2, advised that they had already mailed it out. 2 weeks
later no CD's. Call back and reorder, then get an email about how its
Is there anyway I can download these CD's? I already have my keys...
If you have an MSDN subscription, you can download the Microsoft V1.2 code.
Otherwise you would need to wait for the CD's. If you are awaiting an...Uppercase Input
I want to format a cell so that any characters inputted into that cell are
automatically saved as uppercase in that cell. How do I do that?
you need an event procedure. See:
"L.A. Lawyer" <firstname.lastname@example.org> schrieb im Newsbeitrag
> I want to format a cell so that any characters inputted into that
> automatically saved as uppercase in that cell. How do I do that?
One way, right click the sheet tabe and select view ...Excel 97 numeral input (eg) 6 goes 0,06
In one of our IT rooms (edu enviroment) we still use
On one of the clients, when u input a numeral value in
any of the cells in a new document, the value is divided
1 -> 0,01
60 -> 0,6
300 -> 3
cell formating says nothing special (identical to other
workstations without this problem)
I tried creating a template and place it in C:\Program
Files\Microsoft Office\Office\XLStrart and such, no
thx in advance,
1070 Anderlecht - Belgium
goto 'Tools - Options - Edit' and uncheck &...Macros for adjusting graphs every day
I have a number of reporting graphs linked into a spreadsheet which I update
on a daily basis. I then need to adjust where the graphs read to include the
days data. This is tedious and time consuming with 16 graphs to adjust. Is
there a macro or something I could write that would adjust the data range
automatically in excel97?Adjustment could be based on a date easily enough.
=?Utf-8?B?SmltbXkgRA==?= <email@example.com> wrote in
> I have a number of reporting graphs linked into a spreadsheet
> which I ...Help making this macro dynamic :)
Hey, Im a beginner in excel.
So, I've made a macro that opens up multiple workbooks, copies the data from
the first sheet in each workbook opened and then pastes this data back into the
first sheet of the master excel book. Each time the data is copied from each
workbook it opens, the data needs to be pasted underneath the data that was
Anywho, I made a macro that works but it is hardcoded - i need it to be dynamic
so i can add more data later without it been overwritten by the previous
Could someone make my macro dynamic by adding the vars i nee...Using functions in macros
Can I use the IsNumber fuction in my macro's
>Can I use the IsNumber fuction in my macro's
>Yes, try something like this
For each c in Selection
use something like this
For Each c in selection
If isnumeric(c) then
Not isnumeric(c) then
c.value= c & c
See also IsArray, IsText, IsEmpty
>Can I use the IsNumber fuction in my macro's
This will select all cells on the worksheet with a number.
With ActiveSheet.Use...Macro kills formula
I have a macro which searches a sheet for rows within a certain date range,
sorts these rows to the top and then deletes those rows. After running the
macro, a cell which contains a formula not related to the macro, has one of
the cell references changed to "#REF!".
This is the formula:
The cell reference "C6" is the one that changes.
This is the macro line which deletes the range -
I can see that C6 is in the range being deleted but the cell is still there,
only the contents are d...Insert text in a cell with a macro
I have a series of numbers (SSN) that I need to turn into text, so I want a
macro that will put an apostrophe and a zero at the beginning of the numbers.
I know that there are other ways to change a number to text (formatting,
find/replace to add the apostrophe) but for what we are doing, manually
editing to add the apostrophe and zero is the only thing that works. SO.. to
my question. When I make the macro to do this, of course it simply adds the
apostrophe and zero and repeats the number from the first cell over and over
again. How can I make it select each cell's new data, mo...how field name output same as input
Thanks for any help.
I have a query from a couple tables, and one of the field names in the table
is 'IGP' and I have a field in the query that was
and now I changed to
IGP: iif(blah IGP blah)
which gives me a circular error.
The table is used by a bunch of other things, so I don't want to change the
field name there, and the field name for the output is used by a bunch of
things, so nor do I want to change the name there. I am using Access 2007. I
thought about changing the output field to IGP_, and I could change the
embedded queries in Excel that use it, but...what is macro?
Pls tell me what macros are, and what benefit(s) do they serve?
I use office 2003
"Afolabi" <Afolabi@discussions.microsoft.com> wrote in message
> Pls tell me what macros are, and what benefit(s) do they serve?
> I use office 2003
Good question, Bob. <grin>
I imagine that a Google web search would turn up some answers:
inurl:excel "what is a macro"
You could start at the VBA Tutorials and read the first page
of each tutorial (Introductions) to get an idea of what macros...Importing CSV file with Dates using Macros
I am using macros to import CSV files which have a column of dates that is
formatted consistantly down the column. They data to be imported is in format
,"dd/mm/yyyy", . If I open the file manually the data is imported
consistantly and the entire column is converted to dates.
When I use a macro to open the same file some cells are treated as text and
some as number. I cannot detect a patten to which is imported as text and
which is number.
"AckDate","Date Avail","Diff"...Macro not saving as
The following is code that I am using for a project. I want it to save as
based on a value that is in the source spreadsheet. It is supposed to open up
another spreadsheet and then save it as that value in the source spreadsheet.
the macro is stopping at the line where it needs to name the file, then save
it. can someone help?
Application.EnableCancelKey = xlDisabled
lastRow = Range("M10000").End(xlUp).Row
Range("M" & lastRow).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "END"
Cal...have to wait 15mins before mounting new IS
Single Domain Windows 2003 AD (Upgraded from 2000 to 2003 Forest & domain
3 DC's in one AD site, 3 physical sites (100Mb WAN Links)
All DC's are GC's
2 Exchange 2003 Enterprise Back end servers (not clustered)
1 Exchange 2003 Std front-end server
All exchange servers on single site (main site) - same site as PDC Emulator DC
When I create a new Mailbox Store (or public folder) I get error stating
store can't be mounted, wait for AD replication. If I select 'Continue
Trying' mount will fail with error
"The Microso...Please Wait While Visio Updates Its Directory Cache?
I installed Visio 2003 from a CD my boss gave to me; the CD is in
working order since he was able to install the application from the
I have so far tried:
(3) uninstalling and reinstalling
(1) and (2) lead to the application stalling at 13%, (3) leads to the
application stalling at 38%... My boss attempted to start the
application and the application stalled with the "Please wait while
Visio updates its directory cache" message at 100%.
We together installed the service pack and re-registered the
application into my machine's Windows...does Excel Viewer support macro?
I have try but Excel Viewer 97 and Excel Viewer 2003 have not process a
Is it done by design, or there are some registry to enable this processing?
My task is to activate correct worksheet after file is open in Excel Viewer.
The Excel viewer is just that, a viewer to see the end product, the results
of the spreadsheet. It has no functionality at all as far as I'm aware,
"Volodia" <GroundSoft@hotmail.com> wrote in message
>I have try but Excel Viewer 97 and Excel Viewer 2003 have ...macro to color top 10% in range
I'm using Excel X (VBA5) and trying write a macro which will change the
interior cell color of those cells whose value is in the top ten percent of
the selected range of cells. I was able to do this quite easily with
conditional formatting using the percentile formula, but I want to be able to
do it with a macro and explicit formatting. The below macro returns a "sub or
function not defined" error when it gets to PERCENTILE:
' having selected the cells before running,
' first create range from selection
Selection.Nam...eConnect Invoice Error during Input
I'm trying to make a simple Invoice entry into GP via eConnect using the
eConnect SalesOrder sample but I get the following error:
Sql procedure error codes returned:\r\nError Number = 308 Stored Procedure
taPMTransactionInsert Error Description = Document Amount is incorrect -
DOCAMNT <> MSCCHAMT + PRCHAMNT + TAXAMNT + FRTAMNT - TRDISAMT\r\nNode
Identifier Parameters: taPMTransactionInsert
I have filled the following
taPMTransactionInsert transactionins = new taPMTransactionInsert();
transactionins.BACHNUMB = "OracleFin" ;
changement de mois en automatique