Over 1 million tech questions and answers.

Excel Macro - Allow cells to update query before macro completes

Q: Excel Macro - Allow cells to update query before macro completes

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!

RELEVANCY SCORE 200
Preferred Solution: Excel Macro - Allow cells to update query before macro completes

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin

Read other 1 answers
RELEVANCY SCORE 113.2

Hi there,

may somebdoy please advise (I am pure newbie at Macros):
*I want to create button (I can dot that) and assign Macro to it:
*Copy selected cells (C3:N3)
*Paste values against respective product code (product code can be changed manually in A2)

It happens in the same working sheet (or not a problem if haapens in another sheet)

Many thanks in advance (this would save me some time and efforet)

SM
 

A:Solved: Copy selected cells to chosen cells in Excel with Macro

Read other 14 answers
RELEVANCY SCORE 112.8

Hi

I am wondering if there is a simple macro that would allow me to format cells throughout a workbook based on whether they contain an input (ie number/text etc) vs. a calculation (anything with a formula).

Basically I'm looking for an automated way to colour my inputs in blue font and calculations in blank font across all worksheets in a workbook.

Any ideas would be great.

Thanks
 

A:Excel macro to format input cells vs calculation cells

I wanted to point out that this can be done without using a macro. In Excel you can click EDIT >> GOTO >> SPECIAL and put a check in the option labeled "Formulas." After clicking OK all your formula cells should be automatically selected on the sheet. Once all cells have been selected you can change the font, background color, etc. for all the selected cells at once. If you prefer to use a macro instead you can try the code I provided below.

Code:
Sub FindFormulas()

For Each vcell In ActiveSheet.UsedRange

If vcell.HasFormula = False Then

vcell.Font.ColorIndex = 5

Else

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Replace with your code to execute if cell contains formula
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End If

Next vcell

End Sub

Regards,
Rollin
 

Read other 1 answers
RELEVANCY SCORE 104.8

two problems involving writing macros i would like to solve,

1st - want to write a macro to move to another worksheet, then edit a cell in that worksheet so that the value increases by one

2nd - would like write a macro - which again needs to move to another sheet, then copy a formula in one cell to another, to give two cells with same formula, and then fix one one of those cells (1st) so that it is no longer a formula - just a number
 

A:excel problem - macro to edit cells

You don't need to switch sheets in either case.

Sub Macro1()
Sheets("Sheet2").Range("A1") = Sheets("Sheet2").Range("A1") + 1
End Sub

Sub Macro2()
Sheets("Sheet2").Range("A1").Copy Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("A1").Value = Sheets("Sheet2").Range("A1").Value
End Sub
 

Read other 1 answers
RELEVANCY SCORE 104.8

I know this is probably very simple, and I am going to hit myself once the answer is found, but I cannot seem to think of the solution for the question below.

I need to highlight all the cells in a column that does not contain a zero. How would I go about doing this with code?
 

A:Solved: Excel Macro - Highlight Certain Cells

Read other 9 answers
RELEVANCY SCORE 104.8

Hello,
I am having some issues trying to figure out how to select multiple cells using macros. My goal is to be able to highlight a certain name in a column of names, and this name can appear multiple times and not always in a set range.

Example:
John
Larry
Shane
Larry
Larry
John
Larry

(The blue font indicates a highlighted cell)

Is their a way for a marco to look at all of the names in column A until "Do Until IsEmpty()" has been completed and have the specified name in the macro "Larry" stay highlighted to view individual rows of information. Also, these names will not have blank cells inbetween them.

Thanks for all the help!!!
 

A:Excel Macro Selecting Multiple Cells

Read other 6 answers
RELEVANCY SCORE 104.8

Does anyone have a macro that will delete row that have duplicate cells in same row other columns?

A:Excel Macro to delete row with duplicate cells

Hi

Are you looking to delete rows that have duplicate data but the duplicate data is in different columns?
Can you give a small example?

If the duplicate values are in the same column then you can use the 'remove duplicates' tool.
Select the rows/columns then go to DATA and you should see the REMOVE DUPLICATES tool.

James

Read other 5 answers
RELEVANCY SCORE 104.8

Hi there,
I know this question gets asked in various forms time and time again but I'm too much of a novice to make those solutions work for me.
I have a list of data but each item is spread over two rows making it unsortable.

ie
_ A B C D E F
1 a b _ _ _
2 _ c d e f g
3 a b _ _ _
4 _ c d e f g

b and c are both in colum B
whereas i would like to have

_ A B C D E F G
1 a b c d e f g
2
3 a b c d e f g
4

or even without the empty rows but even I can fix that.

If its not too much trouble I'd appreciate an explaination with any code so that I can try and change it for similar problems in the future.
I hope this is clear. I thank you very much for your time.
 

A:Solved: Macro to move excel cells

Read other 9 answers
RELEVANCY SCORE 104.8

This is just a curiosity post since I've been working a lot with excel lately. Is there anyway to create an IF formula to autofill cells based on a start date and then stop autofilling them on an end date? That probably seems really vague, so I'll attach an example.

In my spreadsheet, I have the costs per home on the right and a schedule on the left. Can I make an IF statement in the schedule that will read something like =if(B6=R7, Q7, ""), but then have the cost per month fill out the cells until Month 4?

I want to make a cash flow schedule that can be updated from as few entries as possible since I'm usually manually updating larger worksheets that become very convoluted.
 

Read other answers
RELEVANCY SCORE 104.8

Scenario:
Excel spreadsheet 1 with 'X' number of lines in it.

Goal:
Create a macro in sheet 2 that will copy a formula from line 2 (in this same spreadsheet) to line 'X'. I am simply doing a reformat in sheet 1 using some simple formulas so I can get this into a format to import.

Example:
First, determine number of rows in spreadsheet 1. I think this can be done with the counta function. I am trying to set a variable up that holds this value. This is what I have in the macro currently to do this:
Dim x
x = "=COUNTA(CSEXPORT!C:C)-1"

I am hoping that this set x = 10.

Second, I need to copy line 2 in spreadsheet 2 down 'x' rows.

I have tried relative positioning thinking that would work, but the macro still specifies specific cells. I thought I could use EndDown, EndUp to do what I needed, but it still puts in specific cell values.

The only thing that I need to know is how to use the variable in the VBA script. I don't know how to tell it to go to cell A(x). I have attached a spreadsheet with a rough example. The macro within this sheet is not completed.

Thanks
 

A:Excel Macro that will copy formula 'X' cells

Read other 6 answers
RELEVANCY SCORE 104.8

Hi,

Im currently having trouble, i have some code to search for a value within 3 spreadsheets but what id like is for it to also return a value on the same row.

e.g
Dave 111111
Claire 112233

so instead of returning just dave, id like the number returned also.
 

A:Excel macro: Return values from 2 cells on same row

Read other 9 answers
RELEVANCY SCORE 104.8

Hello, I need to figure out a macro to select certain ranges of cells depending on what is in cel 'A1'.

I have a column of dates in column 'B' and two columns of numbers in 'H'. I want to be able to enter a date into cell 'A1' and have it find that date and then select all cells in column 'H' that correspond to all dates before and including the date entered in cell 'A1'.

eg.

A1 = 1/1/2010

B2 = 11/12/2009 H2 = 2.56
B3 = 15/12/2009 H3 = 30.99
B4 = 20/12/2009 H4 = 32.54
B5 = 25/12/2009 H5 = 5.65
B6 = 31/12/2009 H6 = 3.54
B7 = 1/1/2010 H7 = 6.87
B8 = 20/1/2010 H8 = 1.25

Since A1 = 1/1/2010 I want to select all cells from H2 to H7

I do not need this to be done automatically as I will push a button to activate the macro I just need the macro to select the correct cells when i push the button.

Can someone please help me figre this out I am a complete newbie when it comes to Macro coding, but I do understand the rest of Excel very well.

Thanks.
 

A:Excel Macro to select specific cells

Read other 7 answers
RELEVANCY SCORE 104

Hi

This is very frustrating for me but probably very easy for someone with the know!

I use excel for accounts and enter the relevant details along a row; 1 row for each transaction. I want to be able to select specific cells from that row and paste them into an Excel invoice template i have.

I have used the macro record button to do this and it works fine except for one thing; it always copies the cells from the row i created the macro from. I would like it to copy the cells from the row i have selected. Hope this makes sense.

It obviously selects the cell (c4 etc) in the code but i want it to select based on the row of the cell selected if you know what i mean.

Here is the basic code i have
Sub CreateInvoice()
'
' CreateInvoice Macro
' Macro recorded 01/09/2007 by User
'
' Keyboard Shortcut: Ctrl+i
'
ChDir "X:\Accounting\Customer Accounts\Invoices"
Workbooks.Open Filename:= _
"X:\Accounting\Customer Accounts\Invoices\Blank Invoice.xls", UpdateLinks:=3
Windows("Accounts September 2007.xls").Activate
Range("B8").Select
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B4").Select
ActiveSheet.Paste
Windows("Accounts September 2007.xls").Activate
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Invoice.xls").Activate
Range("B2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection
.H... Read more

A:Excel copy & paste Macro for selected cells

Read other 16 answers
RELEVANCY SCORE 104

Hello, Can anyone help me. I need to add a macro that can iterate through a number of worksheets and delete all the data from cells that are unprotected.

I have tried a few things but being unable to find an diagram of the excel object model with properies and fields has slowed me down, if anyone can solve my query and point me to such a document that would be excellent.
 

A:Excel macro, change delete from unprotected cells.

You can use the cells "locked" property to determine if the cell is protected or not.

You can can use VBA to select the cells or you can manually select your range of cells and use a simple loop to take appropriate action. How many workbooks and/or sheets are we talking about running this code on? If you provide more details about your process we can give you more code showing how to loop through each sheet in the workbook or even how to loop through an entire directory of workbooks.

Code:

For each vCell in Selection.Cells
If vCell.Locked then
'Your code here
End If
If Not vCell.Locked then
'Your code here
End If
Next

Rollin
 

Read other 3 answers
RELEVANCY SCORE 104

Hello everyone,

I am wondering if anyone one would be able to help me. I have only just started getting into advanced excel use of macros and formulas for Excel 07. I have done ok so far except for the following macros formula needed tp change the background colour of a range of cells with more then the three options that conditional formatting allows.
I have a column of cells from C6:C80 which I have made all drop down selections from a list of four text items:
ItemA
ItemB
ItemC
ItemD
I was trying to get a macro that would colour the cells in C6:C80 automatically on selection of the drop down list based upon the four different selections available. And another macro to colour that whole row that has data in it based upon that same selection. The reason why two different ones being that I am unsure of what will work the best.

I know this is on the easy side of macros, but I am hoping someone would be able to throw me a bone none the less

Thanks for any help all.

Ads
 

A:Solved: Excel 07 macro to colour cells in a range

Ok turns out I didn't need macro and it can all be done with conditional formatting using the "Highlight Cell Rules". I was under the impression that you could only use three conditions but that was incorrect. Thanks for the help from Sweep at another forum.
 

Read other 1 answers
RELEVANCY SCORE 104

Hey all,

Here's my situation, any help is greatly appreciated:

I have a list of 13 items in one column. A couple of the cells will be hidden by the macro, but then what I need the macro to do is copy the first 10 unhidden items onto the clipboard.

What row(s) will be hidden will vary from week-to-week. If I were to simply have the macro copy a cell range, there would be a chance of it copying a hidden cell. I believe what I need is a way to have the macro do a count of how many unhidden cells it has selected, and stop when it reaches 10.

Forgive my ignorance but I am literally just picking up Visual Basic as of this week.

EDIT: Just to clarify, I have the hiding of the cells taken care of. I just need some help figuring how to copy the top 10 cells that aren't hidden.
EDIT 2: Found that I could delete the hidden cells. Worked perfectly.
 

Read other answers
RELEVANCY SCORE 102.8

Hi

I have a following macro:
Code:
Sub BondPrices()

Selection.AutoFilter Field:=16, Criteria1:="BONDS"

Range("S2:S10000").Select
For Each cell In Selection
If Not IsEmpty(cell) And IsNumeric(cell.Value) Then
cell.Value = CDbl(cell.Value)
cell.Value = cell.Value / 100
End If
Next cell

Selection.NumberFormat = "0.00%"
Selection.AutoFilter
End Sub
The problem is that every cell in the sheet gets divided by 100, not only the filtered cells (only BONDS).

Any help would be greatly appreciated.

Michael
 

A:Solved: Excel: Macro to divide specific cells by 100 with a filter

I've solved the problem myself by adding a line to the code that selects only visible cells:

Code:
Selection.SpecialCells(xlCellTypeVisible).Select

 

Read other 1 answers
RELEVANCY SCORE 102.8

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.
 

A:Solved: Simple Excel Macro to Fill empty cells

Read other 8 answers
RELEVANCY SCORE 102.8

I have a macro that I recorded that does the following and I just need to change one thing for it to work the way I need it to do.

I select a list of cells in a different worksheet, and then run the macro.

This macro copies what I selected, goes to the worksheet named: Contact Fields Formulas. And then it pastes to Column D, starting in Row D4, the list of the cells that were copied.

Then, thru another macros I created before, when I am recording this macro, I run two other macros which one adds to the beginging of the words in the cells pasted, the " and the other macro I run while I am recording, adds to the end of the words in the cells pastd, the ".

Then the macro recorded goes to D4 and moves to E4, and makes E4 into E4=C4&D4, and it moves to E5 and then goes back up to E4, copies E4, moves to D4 and then down to the last cell with words that were pasted from D4 down, and then moves over to the cell to the right of the last cell in Column D that had the words as pasted, and then selects all cells going up to the E4, and pastes what was copied from E4.

Then, the E4 to the bottom of the last cell is still selected and it copies all of that, and then pastes it as a Paste Special with the concept Value, so that the Cells form E4 down, are the words of the two cells that were merged from the left of the E column cell.

So the question is, when I select another list of cells to copy and move over and do what the macro does, when in the area of

"moves... Read more

A:Excel Macro to Select Bottom Cell of LIst of Cells

Read other 8 answers
RELEVANCY SCORE 102.8

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!
 

Read other answers
RELEVANCY SCORE 102.8

I'm writing an Excel macro to find cells based on format. The cells in questions are subtotals so the format is bold. I'm using the following code:

Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate

It works fine for most cells, eg it finds cells with values A1P-1MR Total, A1P-2RN Total, E1P-1MR Total

However it skips certain cells eg. E2C-2RN Total,
E3G-2RN, Total Grand Total
The format seems to be the same for all of these cells, so what's going on?
 

A:Solved: Excel macro selction.find skips cells

I just realized the problem. The number format was different.
 

Read other 1 answers
RELEVANCY SCORE 101.6

I have been given the task of updating our company price book, and am in desperate need of help with my macro.
Currently I have a macro that multiplies the numbers in the cell range by a percentage. However each page has prices in different cell ranges. I need help with an if statement to only select cells that have been formatted as currency (or at least cells that have a number in them), and to skip over blank cells, as well as cells with letters or letters and numbers in them.

Here's what I have currently

Sub Macro1()
'
' Macro1 Macro
'
[A1:H50] = Application.RoundUp([A1:H50*1.02], 2)

'
End Sub

Thanks
-SasukeSama21
 

A:Solved: Excel Macro needed to only select cells formatted as currency

Read other 16 answers
RELEVANCY SCORE 101.6

Hi all,

I need to know if is it possible that a macro in Excel sends an email containing the visible cells using Microsoft Outlook.
btw, I saw some code on the internet, that saves the visible cells in a new excel file and attach it in a new email using Microsoft Outlook, but I don't want to have attachments in the email.

thanks for any possible help.
 

Read other answers
RELEVANCY SCORE 101.6

Please help me to write a macro to perform a basic function. Sadly i am so thick and dont seem to be able to sort it out myself.

i have a spreadsheet with all information in column A
The information is in cells as follows

A1
A3
A5
down to 16800
every second row is blank

I need to move the information in the cells as follows

A1 static
A3 to B1
A5 to C1

Then it loops

A7 to A2
A9 to B2
A11 to C2

Loops again

A13 to A 3
A15 to B 3
A 17 to C3

and so on.

Really sorry to ask for something so basic.

Additionally please advise on best way to progress with a basic knowledge

 

A:Excel Macro query

Hi and welcome to TSG forums

I'm offering this macro for you to try:

Code:
Sub test()
Dim ws As Worksheet, rng1 As Range, rng2 As Range
Dim i As Long

Set ws = ActiveSheet
ws.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
While ws.Range("A" & i * 3 + 1) <> ""
Set rng1 = ws.Range("A" & i * 3 + 1).Resize(3)
Set rng2 = ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1)
rng1.Copy
rng2.PasteSpecial Paste:=xlPasteValues, Transpose:=True
i = i + 1
Wend
'ws.Range("A:A").Delete
End Sub
To utilize it, you need to do the following:
1) Open the spreadsheet with the data
2) Press ALT+F11. This takes you to Visual Basic Editor
3) From Menu, choose Insert / Module. A module is a space where macro codes are stored. The cursor will now blink at the right pane of the VB editor window.
4) Copy the above code into the module.
5) Return to the spreadsheet (ALT+TAB, or something)
6) Press ALT+F8 to run the macro.

The macro does the following:
1) Deletes all rows where the cell in column A is empty.
2) In a loop, it copies all 3-cells set, and pastes the values, transposed, into the next available cell in column B.
3) Deletes column A, which is not needed anymore. This last function was commented out in the above code, so it won't be executed. I thought you may want to check whether the macro did the right sort... Read more

Read other 2 answers
RELEVANCY SCORE 101.6

Good morning,

I'm sorry if the title doesn't tell you what I'm after - I'm not sure I know what I'm after

I have a list of orders with different booking references, some lines can have the same booking ref.

the booking references will change each time we receive a new file, what I want to know is ..... is it possible to split the list over several worksheets (each sheet with its own booking ref). and then print them using a macro?

I have attached an example of how the file looks now - I would appreciate any help at all on this.

My thanks as always

Moll
 

A:Excel macro query

Good afternoon Moll. How about

Sub Macro1()

'Set up pages title
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With

'insert pagebreak after each change in Booking Ref
Range("K3").Select
Do Until ActiveCell = ""
If ActiveCell = ActiveCell.Offset(-1, 0) Then
ActiveCell.Offset(1, 0).Select
Else
ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=ActiveCell
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

?

HTH,
Andy
 

Read other 3 answers
RELEVANCY SCORE 100.8

Hi all,

I really need help with this, as I have tried means to get it right, but I have no experience in VBA and it seem difficult. Any help will be greatly appreciated.

I need to copy specific cells in a form (in Excel format) that is input by users and paste them into the summary workbook.

The form is fixed, but every time someone sends in a new form, I'll need to update the data in a summary workbook. So it'll be constant updating and I need to ensure that a new row in the Summary folder is used for each form that is sent in.

I'm trying to write a macro that is able to automate the data transfer.

Eg. I need to:

copy the data from D6 in the file Form to the celll A2 Summary file,
D7 in Form to B2 etc.
Attached is the form and my summary sheet.

Thanks in advance!
 

A:Help Needed for Excel Macro - Copy specific cells and paste into a Summary workbook

Hi all,

Below is the vba code that I have written.
But the problem is I am not sure how to define the destination workbook as a file, which is also where this macro will be stored.

It does not seem to work when I tried to put ThisWorkbook or Activeworkbook. I did not want to add a new workbook either.

Sub copyWorkbooks()
Dim MyPath As String
Dim SourceRcount As Long, FNum As Long
Dim mybook As Workbook, DestWks As Workbook
Dim sourceRange As Range, destrange As Range
Dim rnum As Long, CalcMode As Long
Dim SaveDriveDir As String
Dim FName As Variant

' Set application properties.
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With
SaveDriveDir = CurDir
' Change this to the path\folder location of the files.
ChDirNet "C:\Documents and Settings\chinba\Desktop\ASL Exception\"
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
MultiSelect:=True)
On Error Resume Next

'find the last row
RDB_Last = rng.Find(What:="*", _
after:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
If IsArray(FName) Then
'Loop through all files in the myFiles array.
For FNum = LBound(FName) To UBound(FName)
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(FName(FNum))
On Error GoTo 0
If Not mybook Is Nothing Then
On Error Resu... Read more

Read other 1 answers
RELEVANCY SCORE 100.4

I sometimes want to convert a cell from a direct relationship to another cell (i.e. =E54) to having its own value NOT related to the original cell (which might change in value weekly).
I do this by clicking the copied cell, Edit copy, Edit Paste Special, Values and finally clicking Esc to remove the dotted lines. The cell now has the number without reference to the original.
You would think turning this into a macro would be pretty simple but whenever I try it and run the macro it always wants to do the job from the same cell, whereas I want to use this feature on any cell in any workbook. I stored the macro, named values, in the Personal.XLS workbook.
Can anyone explain what I am doing wrong?

Also, I have a backup program and I would like to backup Personal.XLS periodocally but I cannot find where this file is located and so I cannot tell my backup prrogram where to find it. Any ideas?

johnni
 

A:Excel 2000 macro query

Read other 14 answers
RELEVANCY SCORE 100.4

Usually when I construct a new Macro a small box appears where I can click to stop recording. Now this box no longer appears and I have to go to the Tools menu to stop it recording. Can I get that small box to appear again, please?

Also, in the list of available Macros there is the option to edit a Macro. I have unhidden the Personal.xls sheet but editing anything at all never seems to work. When I click on edit a new sheet appears showing the existing Macro commands. I then might want to edit a cell reference, and after doing this I press Alt-Q, but the Macro remains unchanged. Is there some special technique involved?

johnni
 

A:Excel 2000 macro query

Read other 7 answers
RELEVANCY SCORE 99.6

Old newbie back again!

Questions regarding Excel Queries, Macros that use the downloaded data for other processing and timing:

Set the Stage:
I have 5 web Queries (from 3 different sites) that are refreshed each time I select a particular cell (C6 on Sheet1), press a macro button which takes me to a detail sheet (Sheet3). This action places that information from (C6) to cell (B2) on Sheet3. This cell change event activates the queries, refreshes the tables, and, in turn, activate a couple of macros and links that go to other sheets. One of my macros massages a table to make it acceptable for a link to another sheet.

One of the Problems:
It appears that this macro does the "massage" before the query is completed and the information from the web is not cleaned prior to linking to a chart. (As a matter of fact, I can see the change to the table and then the web data fill).

The Questions:
1) Is there a good method to "time" each of the queries sequentially and then "time" the separate macros so that the tables are "set" before the "real" activity takes place? (Charts, hyperlinks, etc.).

2) Also, there are several modules involved (9). Should the RELATED queries/macros all be in one module, place them in the order I want them to run and then set some sort of delay for each sub?

3) Another real problem right now is that it does take a bit for these guys to run.

All the queries work really well and all the macros work (except... Read more

A:Excel Query and Macro Timing ... Slow

Hi Dick (again). The name's Guru ... Excel (not VBA) Guru.

If I'm being consistent, I really should tick you off for starting the thread over.

That aside, I must say that you appear to be devouring this subject at an ASTOUNDING rate ; congrats.

I doubt I can help you very much at all ...

1) You can go into the properties of a query and turn off "enable background refresh". The query should then hold back other code until it's thru.

2) In broad terms I don't think it makes a lot of difference. By the same token, I don't think so many mods is necessary and could bloat the file. I try to use as few as poss.

#?) I don't know the reason (if any,) for "Call FormulaM". Plain "FormulaM" as a separate line has always worked for me.

#??) "it does take a bit for these guys to run". If you do formatting with code and you've taken a lead from the Macro Recorder, you can end up with a lot of junk. To add a bottom border, the MR'll give you

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone

when you could probably get away with just

Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous

Loo... Read more

Read other 2 answers
RELEVANCY SCORE 99.6

Is there a way of exporting a query directly query directly into Mircrosoft excel? I have a query that I am trying to distribute to people in excel format but i would like a button that triggers the export of the query to excel so i can send it out via email.
 

A:Export Query Directly to excel using a macro

Yes, in the Access Visual Basic Editor's Help (not Access's ordinary help) look up Transfer Spreadsheet and Transfer Sreadsheet Action.
 

Read other 1 answers
RELEVANCY SCORE 98.4

Hello all,

I don't know if I'm posting in the right forum. This is an Excel problem I'm having at work.

I'm not overly Excel savvy, but I know enough to get by at work, and fix most problems myself.

I may just have some post maternity baby brain, but I can't fix this problem.

My manager has a spreadsheet with about 5 tables in, each with monthly totals for different areas, and each cell pulls data from half a dozen different spreadsheets and work books. Pulling cell refs from so many things makes it slow on opening, and management asked me to find a way to have individual months formulas calculate on the click of a button, rather than on opening the spreadsheet.

I did a little test sheet which worked fine, but when I did the same on managements spreadsheet it went a bit pete tong. I recorded a macro to input the formula, which when I recorded it, is fine - all totals are correct, but when I run the macro only half the formula appears.

I even tried a different method; taking out =sum, so the formula doesnt work on opening and having a macro that simply pops that bit back in, but half the formula disappears when I run it, even though the macro doesn't touch the rest of the formula.

Any clue as to why this happens, or am I just being a ditz and over complicating things?

Sorry if this is a bit confusing - I'm not great with excel, and I'm now at home without the spreadsheet so can't go into much detail.

Ta muchly.
 

A:Solved: Excel Spreadsheet macro/formula query

Read other 6 answers
RELEVANCY SCORE 97.6

I need a macro to update a web query in excel every morning at a cetain time. Can anybody help?
 

A:macro to update web query

Please don't cross post in multiple ssections of the forum.
 

Read other 1 answers
RELEVANCY SCORE 97.6

I need a macro to update a web query in excel every morning at a cetain time. Can anybody help?
 

A:macro to update web query

Not sure what you mean by "update a web query"
 

Read other 3 answers
RELEVANCY SCORE 97.2

OK, I am trying to pass a variable to either a web query or link. So for example, let's say I am trying to pass a variable to a web address that runs on a variable. For example, if you replace the last 3 letters in the following line with the one corresponding with a particular team, then you go to that teams clubhouse:

http://sports.espn.go.com/nfl/clubhouse?team=nyg

replace "nyg" with "phi" and you go to the Eagles clubhouse rather than the Giants.

So what I want to do is lets say someone has their cursor on a cell that has the teams symbol--like "phi" is in cell "B3". I want to be able to have someone execute a macro or a web query that passes that "value" to the query or macro. Now the value in "B3" may be relative to another cel. In othe words, "B3" may actually be "+D24", which returns what's in D24 so I want it to use the actual value in the cell, not the formula.

Here is a macro that I found that kind of does what I want, but its for saving a spreadsheet. I only use it as an example for reference.

Sub SaveSheet()
'error trap
On Error GoTo Etrap

Dim MyCell
MyCell = ActiveCell.Value

'ask user to save
If MsgBox("Save new workbook as " & CurDir & "\" & MyCell & ".xls?", vbYesNo) = vbNo Then
Exit Sub
End If

'check value of activecell
If MyCell = "" Then
MsgBox "Please check the Cell Value", vbIn... Read more

A:Need Excel Macro to pass active cell value to web Query or Link

Hi Chris.

Re: "pass a variable to either a web query or link" -- "query" in Excel is something different from what I think you mean, so I'll stick will "link".

See the attached. The formula in B3 links to D23; D24 is the "generic" URL.

Clicking the link in D25 should take you to http://sports.espn.go.com/nfl/clubhouse?team=phi. Now change D23 to nyg and run the macro.

(note: B3 doesn't have to be selected for the code to run -- not sure about the "if the person accidentally is on the wrong cell" bit, but we can come back to that)
 

Read other 2 answers
RELEVANCY SCORE 93.2

Hi all,
This is a great board-- I have received a lot of help from here!
This is my next project.
Open the attached file and, on the right of the form, there are six cells with text.
I need to concatenate these with one space in between each one, then copy the group of them and use the clipboard to paste so I can open the "save as" dialog box and paste it as the filename. We need this in our environment to be able to create the file name quickly, paste it and save the file.
I want to be able to do this with a button that activates a macro.
You can see the button on the right side when you open the file. The macro assigned to it called "createfilename". The code is in module 1.
I got it started, but I know it needs help.
Thanks!
 

A:Solved: Excel macro to concatenate and copy cells for pasting to "save as" dialog box

Read other 7 answers
RELEVANCY SCORE 93.2

Hi, I have a column in Excel that consists with time (minutes) from 0 to 80. I need a macro that will take each cell within a range (let say a column A1 to A6000) and do F2 and enter to each of the cells. I have too many cells to do it manualy..F2=>Enter. My lookup won’t recognize the numbers unless I do so. Can you help?

Thanks
 

A:Excel Macro that does "F2 and Enter" for a range of cells

Read other 6 answers
RELEVANCY SCORE 92.4

I have a macro that marks correct answers based on the correct value in B2 - B17 and AI2 - AI17 being the wrong answer.
Also if B2 - B17 is entered incorrectly that cell color turns red. If the answer is correct the cell color turns yellow and is added up at the bottom.

What my macro does not do is change the cell color to red if C2 - AI2 does not match B2 or AI17. Also if AI2 - AI17 is not entered correctly the cell color should change. Here is the macro. Also a file is attached with the macros. When the changes are made to the macro it should find mistakes in the attached file, some of the cells that are wrong are C2, D2, C3, D3, C15 there are others that are wrong.

Code:

Sub Update()
Dim Rng1 As Range
Dim Rng2 As Range
Dim bMatch As Boolean
Dim origRng As Range
Dim compRng As Range

On Error Resume Next

Set origRng = Range("B2:B17")
If origRng Is Nothing Then Exit Sub

For Each Rng1 In origRng
'Check contents of Rng1
If Rng1.Text = "" Then
'Rng1 is blank, skip this row
Range("B" & Rng1.Row & ":AI" & Rng1.Row).Interior.ColorIndex = 0
Else
'Rng1 is not blank, run match code
Set compRng = Range("C" & Rng1.Row & ":AI" & Rng1.Row)
bMatch = False
For Each Rng2 In compRng
If InStr(UCase(Rng2), UCase(Rng1)) = 1 And Rng2 > "" Then
... Read more

A:Highlight cells if they don't match one of two cells (macro)

Any ideas?
 

Read other 3 answers
RELEVANCY SCORE 89.6

Hi,
I have a large excel spreadsheet with over 30 different graphs. Instead of manually updating the charts to include the last 2 years of data I am trying to have all the charts automatically update upon opening. I run into problems when I try to change the formula of the charts' series-I get an error message saying "Unable to set the formula property of the Series class." I have been stuck on this problem for several days and need to figure it out for my internship. Any help would be greatly appreciated. Here are the first two procedures (I didn't think I should include all 30)

Sub SoybeanUpdate()

ChDir "Q:\Research\CSIDATA\allcsicont"
Workbooks.OpenText Filename:="Q:\Research\CSIDATA\allcsicont\S__0361N.TXT", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 5), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))

Windows("All Commodity Macro Test.xls").Activate
ActiveWorkbook.Names.Add Name:="SoybeanDate", RefersToR1C1:= _
"=OFFSET(S__0361N.TXT!R1C1,COUNTA(S__0361N.TXT!C1)-5475,0,5475,1)"
ActiveWorkbook.Names.Add Name:="SoybeanPrice", RefersToR1C1:= _
"=OFFSET(S__0361N.TXT!R1C5,COUNTA(S__0361N.TXT!C5)-5475,0,5475,1)"

Windows("S__0361N.TXT").Activate
ActiveWorkbook.Names.Add Name:... Read more

Read other answers
RELEVANCY SCORE 88

Hi Guys,

I have an issue in excel - I have a graph whose X-Intercept needs to update when I refresh the data in the sheet it works from.

I have tried to run a macro where I copy/paste the required value into the 'Format Axis' --> 'Scale' --> 'Category X-Axis Crosses At' field, but it wont let me do so.

Dopes anyone know how I can get this field to link to a cell in my data? Or some VB code I can slap in which will update the X-Axis Crosses At field from a cell?

Much Appreciated.
 

A:Macro to Update 'X-Axis Crosses At' field in Excel

Oh and am using Excel 2003
 

Read other 2 answers
RELEVANCY SCORE 88

Hi,

I have been putting a couple of macros together to export a series of jobs in an excel spreadsheet into either a task list or calendar in outlook.

I require that the entry is updated if already present. The task macro works but the calendar macro is not. It seems to stick on a count function. I was wondering whether this was due to the amount of entries I already have in my outlook calendar? The codes are listed below. If anyone can offer advice/help in how to overcome the issue I would be very greatful. If the code can be simplified, that would also be a bonus.

When checking to update, the macro looks at subject field

TASK CREATE/UPDATE/DELETE MACRO :-

Private Sub cmdTasks_Click()
Dim OL As Outlook.Application
Dim OL_TK As Outlook.TaskItem
Dim OL_NS As Outlook.Namespace
Dim OL_FL As Outlook.MAPIFolder
Dim OL_TK_Crit As String
Dim OL_TK_i, UP_i, NE_i, DE_i As Integer
Dim w As Workbook
Dim s As Worksheet
Set s = Worksheets("Task Assigner")
Dim c As Range
UP_i = 0
NE_i = 0
DE_i = 0
For i = 3 To s.Range("A2").CurrentRegion.Rows.Count
Set c = s.Cells(i, 1)
OL_TK_Crit = c.Offset(0, 3) & ": " & c.Offset(0, 1) & " - " & "OUK ID - " & c.Offset(0, 6).Value
OL_TK_i = 0
Set OL = New Outlook.Application
Set OL_NS = Outlook.GetNamespace("MAPI")
Set OL_FL = OL_NS.GetDefaultFolder(olFolderTasks)

If c.Offset.Value = "Y" Then
For Each OL_TK In OL_FL.Items
Select Case UCase(OL_TK.Subject) = UCas... Read more

A:Excel Appointment Task Schedule Update Macro

Read other 9 answers
RELEVANCY SCORE 86.8

Hi,

I am in the F&B industry and have been tasked with setting up a somewhat sophisticated workbook.

From the the worksheet that is to be used as a "data entry form", I need to copy a variety of cells, eg B2:B8, B10:B20, G5,G6,F6 etc etc. to a raw data page where everytime the "Submit" button/marcro is run the data is appended to the end of Data worksheet.

The cells copied would then go along the rows, instead of the colums... if this makes sense?
 

Read other answers
RELEVANCY SCORE 86

Hello

Yesterday I created a code that hides/unhides rows when checkbox is clickedand goes something like this:

Rows("22:24").EntireRow.Hidden = Not Rows("22:24").EntireRow.Hidden

But now i only want to hide/unhide specified cells. So how can i modify the code above to do this or do I need some new code.

Thanks for help
 

A:macro to hide/unhide cells

"hide/unhide specified cells" - I take it, you mean rows, not cells.

What's your criteria for hiding/unhiding the row? All you need to do is loop through each row in your criteria column, and then program this in as your hide/unhide criteria.

Pseudo Code:

Code:


Sub hideAndUnhide()
[INDENT]For Each Cell In Range("A1",Range("A1").End(xlDown))
[INDENT]If(Cell.Value = 1) Then
[INDENT]Rows(Cell.Row).EntireRow.Hidden = True [/INDENT]
Else
[INDENT]Rows(Cell.Row).EntireRow.Hidden = False[/INDENT]
End If[/INDENT]
Next Cell
[/INDENT]
End Sub

If you let me know your data layout, and your criteria, I should be able to make a proper version for you. Even better would be to post your excel sheet with dummy data (remove any sensitive data).
 

Read other 2 answers
RELEVANCY SCORE 86

hi guys!

I've been looking for a solution to my problem. I have an Excel workbook that contains about 100 sheets. It has a master sheet; i created it using a Table of Contents macro.

The master sheet contains a list of serial numbers that i would wanna link to other worksheets i.e: Cell E4 to Cell F4 of sheet 1, Cell E5 to Cell F4 of sheet 2, Cell E6 to Cell F4 of sheet 3 and so on.

The cell to be copied to, from master sheet, is always the same: Cell F4. But the sheet number will increase. I'll include a sample code here for you guys. I used the macro recorder to record these codes.
Code:
Sub Macro1()
'
' Macro1 Macro



Sheets("Sheet1").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "=TOC!RC[-1]"

Sheets("Sheet2").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "=TOC!R[1]C[-1]"

Sheets("Sheet3").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "=TOC!R[2]C[-1]"

Sheets("Sheet4").Select
Range("F4").Select
ActiveCell.FormulaR1C1 = "=TOC!R[3]C[-1]"


End Sub

i only want to update the value of the cell from the Master sheet and not its border formatting.

thanks alot guys!
 

A:Macro help for updating cells on other worksheets

Sub test()
Dim sht As Worksheet
Dim rng As Range
Set rng = Sheets("TOC").Range("E4")

For Each sht In Worksheets
If sht.Name <> "TOC" Then
sht.Range("F4").Formula = "=" & rng.Address
Set rng = rng.Offset(1)
End If
Next
End Sub
 

Read other 1 answers
RELEVANCY SCORE 86

Ok, I've seen some example to do this with VBA, but the conditions are based on the particular cell whereas I need it based on another cell. I have the following conditions for Cell A1 through whatever--let's say A100. The conditions are

CA1 = 7 - Green, Bold, Underline

AND(CA1<>7,CC1=5,CE1=3) - Green, Bold,

AND(CA1<>7,CC1<>5,CE1=3) - Green

AND(CA1<>7,CC1<>5,CE1<>3,BX1>25) - Light Green

AND(CA1<>7,CC1<>5,CE1<>3,BX1<10) - Red

In each case above, the formatting would be applied to the Cell in Column "A", not the cells where the conditions are tested. I set the first 3 conditions no problem of course using Conditional Formatting, but am limited to 3. Can someone show me a macro that would solve the above. I would like it to evaluate each cell in Column "A" and apply the formats based on the conditions listed in the corresponding Cells in Columns CA, CC, CE, and BX until it reaches the end of the range (all cells are filled in so end-down would work to set the range). I would appreciate it.
 

A:Need Macro for formating cells on more than 3 conditions

Read other 16 answers
RELEVANCY SCORE 86

Hello! I am new to Macros and have absolutely zero experience in Visual Basic. I am trying to record a Macro that will loop through an Excel 2007 document and highlight all empty cells. I found this online....

Sub HighlightEmptyCells()
' Select cell A2, *first line of data*.
Range("A2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Insert your code here.
' Step down 1 row from present location.
ActiveCell.Offset(1, 0).Select
Loop
End Sub

But do not know what code will highlight the empty cells. Or, if this code will loop through the entire spreadsheet. Any suggestions?

Thanks!
 

A:Macro to Highlight Empty Cells

you could just use conditional formatting across the spreadsheet
In Cell A1
Format Cell
Conditional Format
change to formula is
enter
=A1=""
The change the format ot a fill pattern or what ever you want for true
and then copy
paste special
and copy to all the active cells you want to test
for the macro this should work

Code:

ActiveCell.Interior.ColorIndex = 36

- theres 56 preset colours
 

Read other 1 answers
RELEVANCY SCORE 86

EXCEL Question:

I am needing to copy cells from one tab to another (and only copy cells with data) using a Macro.

Tab "Checkout" has data starting in cell A3, B3 and C3. This data could have one or more rows/columns.

I am wanting to copy this data into the "Database" tab. If there is something already entered on row, I need it to go to the next to insert the copied data.

Any and all help is greatly appreciated!
 

A:Copy only cells with data into another tab using a Macro

Hi, welcome to the forum.
Do you have some knowledge of VBA? If so it's quite simple.
Record a macro to copy one row from one sheet to the other.
Stop recording after that.
Open the vba editor Alt+F11 and take a look at the macro code.
If you do not have vba knowledge, then it's another matter.
Question: what happens with duplicate numbers in column B?
I don't think you just want to copy everything from sheet 1 to sheet 2?
Maybe a little more background information?
 

Read other 1 answers