Over 1 million tech questions and answers.

Excel macro not working after upgrade to 2003

Q: Excel macro not working after upgrade to 2003

My clients have a macro that automatically generates an email .. however after our recent upgrade to Office 2003 the macro is producing an error on the send mail portion.

Here is the code:

Sub Send_Email()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim Email1 As String
Dim Email2 As String
Dim Email3 As String
Dim Email4 As String
Dim Email5 As String
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & Range("trade_date") & vbCrLf & "Counterparty: " _
& Range("counterparty") & vbCrLf & "Deal Description: " & Range("description")
.Send
End With
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
Set OutMail = Nothing
cleanup:
Set OutApp = Nothing
End Sub

I am hoping it is a simple thing ... really appreciate any assistance.

RELEVANCY SCORE 200
Preferred Solution: Excel macro not working after upgrade to 2003

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 not working after upgrade to 2003

Hi there, welcome to the board!

A couple reasons which may be of issue to you:

1) You will probably need another reference to Outlook 11.0 Object Model (version 2000 was 9.0), or use Late Binding (you are using Early)
2) Your specified ranges are not referenced via worksheet or workbook

So, update the workbook and worksheet in this code and see if it works for you...
Code:
Sub Send_Email()

Dim OutApp As Object ' Outlook.Application
Dim OutMail As Object ' Outlook.MailItem
Dim wb As Workbook, ws As Worksheet
Dim Email1$, Email2$, Email3$, Email4$, Email5$
'// Alter these lines to suit...
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")
'//
Email1 = Range("emailto1")
Email2 = Range("emailto2")
Email3 = Range("emailto3")
Email4 = Range("emailto4")
Email5 = Range("emailto5")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0) '(olMailItem)
With OutMail
.To = Email1 & ";" & Email2 & ";" & Email3 & ";" & Email4 & ";" & Email5
.Subject = "Deal List Update"
.Body = "A transaction requiring special approvals has been entered in the deal list." & _
vbCrLf & vbCrLf & "Trade Date: " & ws.Range("trade_date") & vbCrLf & "Counterparty: " _
& ws.Range("counterparty") & vbCrLf & "Deal Description: " & ws.Range("description")
.send 'You do know you get an outlook security message with this, right?
End With

End Sub
One more note about your code. I left a comment in one of the last lines, the '.send' line. This will trigger an outlook security warning. There are ways around it though, either using CDO, Outlook Redemption (my personal favorite) or ClickYes. This is for truly automated mail though.

HTH

Read other 2 answers
RELEVANCY SCORE 72.4

I’m using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 72

I am looking for help in writing a macro that will do the following.

With Excel 2003 open and the current workbook open. The macro should be able to save 3 variables from the current spreadsheet (some text, a value and a date ) The macro should open up Outlook 2003 as a task, attach the current workbook. In the Subject field of the task it should add the user's name, then the attached spreadsheet's filename then the text variable, then the value variable and finally the date.

Also it would be great if the Priority of the task could be set to High and the Start and Due dates set to 3 calendar days before the date variable.

Finally the macro would set a list of people the task is assigned to. The macro should NOT save the task as a check would need to be done manually.

I have tried unsuccessfully to write a macro in Outlook as I cannot seem to access the attachment filename or any data from the file. I have tried a macro in Excel which I can create a task and add some text to the Subject of the task but with no real progress on the attachment.

I have tried searching the net for help but a lot of code for Outlook is for the mail side of things rather than tasks and the little code snippets I have found have been of limited use. I have limited skills in programming Visual Basic and C so quite prepared to get my use code which does similar things
 

Read other answers
RELEVANCY SCORE 71.6

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

A:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

Read other 1 answers
RELEVANCY SCORE 70

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

Read other answers
RELEVANCY SCORE 69.2

In MS Excell 2003 under Tools>Macro>Record New Macro, before when I clicked on "Record New Macro" a dialog box would appear giving me the option for "Relative Reference", that box giving me the option has now disappeared.

How do I get it back?
 

A:MS Excel 2003 Macro

If you mean the Stop Recording toolbar doesn't show up at all, then while recording ; View -- Toolbars -- Stop Recording.

If you mean the button's gone from the Stop Recording toolbar, rightclick any toolbar & select Customize. On the Toolbars tab, highlight Stop Recording and click Reset...

Rgds,
Andy
 

Read other 1 answers
RELEVANCY SCORE 69.2

HI I'm currently looking for assistance with the following.
I have one workbook with two sheets.

I would like a macro that can search text in sheet 1 cell ‘A1’ in sheet 2 ‘column A’, if there is no match I would like for it to search text in sheet 1cell ‘B1’ in sheet 2 ‘column A’ and return the value that is in sheet 2 cell ‘E1’ to sheet 1 Cell ‘F1’

Any help will be greatly appreciated.
 

A:If, then macro excel 2003

What do you want the macro to do if the first value (A1) is found in Sheet2? BTW....what you are wanting to do can probably be done using a formula instead of a macro. Is there any particular reason why you want to use a macro?

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 69.2

I used the below macro, which worked perfectly the first time. After adding another Excel workbook to the default directory and running the macro, nothing happened. I expected the data to append to the existing worksheet, but it didn't. I would appreciate any help possible.

Sub ConsolidateAll()

' This macro opens all Excel files in the working (default) directory,
' one at a time, and and copies all filled rows from the first worksheet
' of each to the first worksheet in this workbook (the workbook containing
' this macro), only copying the header row 1 once.

Dim Filename As String
Dim ConsolWS As Worksheet 'The worksheet where the data are consolidated
Dim NextRow As Long 'Next available row in ConsolWS worksheet

Set ConsolWS = Worksheets(1)

NextRow = 1

'Look for all files ending with .xls or .xls + any character
'Can include entire path if desired. This example assumes working
'directory so no path specified.
Filename = Dir("*.xls*")

Do While Filename <> ""

If Filename = ThisWorkbook.Name Then GoTo SkipThis
Workbooks.Open Filename
Application.StatusBar = Filename & " added to New workbook."

'Base count of rows in each workbook on the last filled cell
'in column A
Dim LastRow As Long 'Last row in source workbook
LastRow = Range("A65536").End(xlUp).Row
If NextRow = 1 Then
'copy all rows including header (row 1)
Range(Rows(1), Rows(LastRow)).Copy Des... Read more

A:Excel 2003 Macro

would this work for you, I just posted a similar solution here

You can modify the ranges, as this requestor ( who never replied back) asked for a specific range to copy.
 

Read other 1 answers
RELEVANCY SCORE 68.4

I'm designing some stuff that's a bit above my experience level, and need some help:

I'm looking for a function or perhaps a macro that takes the following info:

Item # Pallets
Carrots 3
Potatoes 4
green beans 1

etc

and return a column like this:
Pallet labels
Carrots
Carrots
Carrots
Potatoes
Potatoes
Potatoes
Potatoes
green beans

etc.

I would greatly appreciate any help you can give me on this. Thanks
 

A:Excel 2003 Function or VBA macro help

Read other 12 answers
RELEVANCY SCORE 68.4

Hi. I have a worksheet with name and date columns among others. I need to format the date cell so that if a date is typed in, it will automatically copy the information from both the name and date columns in that row to an area below. Any idea how I could do this? Would I need to do a macro, function, combination of both or should I keep dreaming? Thank you!
 

A:Excel 2003 Macro or Function or both?

First, welcome to the forum!
A VLOOKUP() formula (or two) could probably do what you want. I would have to know more about the layout of your sheet to help more.
 

Read other 3 answers
RELEVANCY SCORE 68.4

Hi
Can any one help me? I have a CSV file that’s exported, (extract attached )and I want a macro to run that deletes the row with 18 or 66 or 39 in Column B. The file is a different length each time its run.
 

A:loop macro excel 2003

Read other 15 answers
RELEVANCY SCORE 68.4

I'm trying to populate blank fields with data from above. For example, in a column, there will be one cell containing data, I'd like to populate the blank cells below until another populated cell appears etc...

I think this is a "Run a For Each...next Loop" macro but do now know VBA language well enough to build.

Any ideas?
 

A:Solved: Excel 2003 macro

Read other 16 answers
RELEVANCY SCORE 68.4

Hi all, I need some help please with excel 2003 vba code. I have recorder the macro which I want to look at a cell and copy the value, select the custom filter, paste the cell value into the custome filter and then sort the results in Z-A order.

The code however (generated by macro recorder is inserting the text value rather than the cell reference.

Can anyone tell me how to edit the code please?? The part number 2000801990 is shown in red - this should be the cell reference.

Thanks

Nic

Sub engineer_consumption()
'
' engineer_consumption Macro
' Macro recorded 18/09/2008 by Nic Cunliffe
'
'
Range("B1").Select
Selection.Copy
Selection.AutoFilter Field:=2, Criteria1:="=2000801990", Operator:=xlAnd
Range("C177").Select
Application.CutCopyMode = False
Range("A8:C44630").Sort Key1:=Range("C177"), Order1:=xlDescending, Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, DataOption1:=xlSortTextAsNumbers
End Sub
 

A:Solved: Excel 2003 macro help

Read other 8 answers
RELEVANCY SCORE 68.4

Hi Guys

I have a Excel spreadsheet that has a lot of sheets. On the header sheet I have created a checkbox next to the names of each sheet, say "A", "B", etc

I've tried to create a macro that deletes all the sheets with the checkbox checked but unsuccessful so far. Can you guys please help me?
Also what's the best way to get to the sheet from header sheet without slowing down excel too much please? Hyperlink?macro?
 

A:Solved: Need help re Macro in Excel 2003

Read other 16 answers
RELEVANCY SCORE 68.4

Hi all,

Been browsing through this site and theres alot of helpful people out there which is good to see. I've just joined and am hoping someone could kindly help me on this problem I have.

I've got a Excel template for a file which I want to be able to update quickly and press a comand button to send an email via Outlook that sends the worksheet (not attachment) in the message body.

I need help creating that Macro for the command button that when pressed will email a specific address.

I also need help in formatting the spreadsheet. I have attached it here as well. What I want is for column C, D, E and F to have a drop down list with the information provided. I also want it so that whenever I start a new row, the same dropdown information is available again and so on.

Is this possible?

Really really appreciate anyones help!!
 

A:Excel 2003 Macro Help needed please

Read other 6 answers
RELEVANCY SCORE 68.4

Hello,
My question is "Is it possible to do what I want to do" in Excel 2003?

I would like to reformat a large list of name and address info from this format:

1. BUSINESS NAME (MILES)
ADDRESS
CITY STATE ZIP
PHONE: 000-000-0000
TYPE OF BUSINESS: XXXX XXXX

TO THIS FORMAT:

A B C D E F G
BUSINESS NAME ADDRESS CITY STATE ZIP PHONE TYPE

I HAVE NO PROBLEM MOVING THE DATA INTO THE DESIRED FORMAT USING A SIMPLE "RECORD NEW MACRO..." THAT I RECORDED.

MY PROBLEM AND QUESTION IS REMOVING THE RED HIGHLIGHTED INFORMATION.
I AM NOT A PROGRAMER AND HAVE NOT FIGURED OUT THE LOGIC IN THE 'VISUAL BASIC'.

I HAVE TRIED MANY "RECORD NEW MACO..." BUT CAN NOT GET IT TO REMOVE THIS UNWANTED INFO.

IS IT EVEN POSSIBLE TO DO THIS WITH EXCEL 2003 ?????????????????????
AS YOU CAN IMAGINE ANY HELPFUL ADVICE WILL BE GREATLY WELCOME.

THANK YOU SO MUCH.
JOHN
 

A:Solved: Excel 2003 MACRO HELP

Read other 16 answers
RELEVANCY SCORE 68.4

Hi

I'm hoping someone can help me, what I'm trying to get it to do is copy info from one sheet and paste it into another sheet but in a slightly different format.

If I was to write what I want in english it would go like this :

Copy the date from cell F5 on sheet A to the next available row in column A on sheet B, then copy from cell H5 on sheet A to next available row (same row as the date in Col A) in Column B on sheet B. Then copy data from cell F31 on sheet A and to the same row, but if cell B7 on sheet A says 'white' then copy it to Column C on Sheet B, or if cell B7 on sheet A says 'blue' the copy it to Column D on Sheet B, or if cell B7 on sheet A says C then copy it to column E on sheet B and so on, I have 6 different columns. (I do have column titles on sheet B on row 5).

Hope that makes sense, can anyone help?

Thnax in advance.
 

A:Solved: Excel 2003 Macro help

Read other 6 answers
RELEVANCY SCORE 68.4

Hi,
I'm looking for a search macro in excel that can find something and copy the entire row into another speardsheet.
I've had a google, and can't seem to find anything that lets you specify what you want on the fly.
I kinda want a google-esque search. is that possile to try and tell the macro Searchbox = item to find?
I've renamed the cell to Searchbox, to make it easier to identify, otherwise it's cell C8.
Any help will be greatly appreciated.
 

A:Search Macro for Excel 2003

The macro below should work. Just change the copy destination to reflect the correct destination workbook, sheet, and cell where the line will be copied to (I have put a comment in the code to show where to do this)
Code:
Sub FindIt()

Dim vFound As Range

On Error GoTo ErrorHandle

Set vFound = Cells.Find(What:=InputBox("Enter Term to Search For"), After:=Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not vFound Is Nothing Then

'*************************************************************
' CHANGE THE COPY DESTINATION IN THE LINE BELOW
'*************************************************************
Rows(vFound.Row).Copy Destination:= _
Workbooks("Test.xls").Sheets("Sheet2").Range("A1") 'Change Workbook Name,Sheet Name, and Destination Cell
Exit Sub
Else
MsgBox ("NOT FOUND")
Exit Sub
End If

ErrorHandle:

If Err.Number = 9 Then
MsgBox ("Please Open Destination Workbook")
Else
MsgBox (Err.Description)
End If

End Sub



Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 68.4

I have a userform created in Excel with a combo box. I would like to have the user select an option from a combo box, and then filter/hide data on a worksheet based on the combobox selection.

If any further information is needed please let me know.

Any help with this would be greatly appreciated.
 

A:Solved: Excel 2003 Macro

Read other 16 answers
RELEVANCY SCORE 67.6

Hi, I'm newbie here and an absolute beginner, I'm attempting to put together an excel (2003) spreadsheet to help me pull KPI's together. I'm running XP. In short I've created a worksheet which I want my 6 engineers individually to fill in at accounting period end. The cells are highlighted in yellow on the attached file on tabs eng 1- eng 6.I would like it if they could only fill in these cells and not be able to edit others.I've created a macro which I run at the beginning of the next accounting period which rolls round so that the end column (column O) is always the current period. This macro appears to work OK.There is a further worksheet &quot;project&quot; which is a clone of eng1 - eng6 but which collates the info from the engineers worksheets and either sums or averages to output, again I created a macro to roll round the reporting columns but the data thens screws up the inputs and I lose the formulae.It is essential that the current period and YTD remain static in columns O & P as the company has another programme which picks up data from a fixed point.I'm sure there is a simple solution to this problem, but with my limited knowledge can't fathom out, any help and assistance would be appreciatedL
How do you attach a file to this message???
 

A:Macro help - in Excel 2003 Assistance Required

Read other 7 answers
RELEVANCY SCORE 67.6

Hello,
I have the following bit of code as part of an Excel macro:

'In G1, enter formula to apply 6 character French code for AUT vehicles
ActiveWindow.ScrollColumn = 1
Application.Goto Reference:="R1C7"
Range("G1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(FIND(""AUTFORD"",RC[-6])),""FORD00"",IF(ISNUMBER(FIND(""AUTVAUX"",RC[-6])),""VAUXHA"",IF(ISNUMBER(FIND(""VNVF1"",RC[-6])),""NISSAN"",IF(ISNUMBER(FIND(""VF1"",RC[-6])),""RENAUL"",IF(ISBLANK(RC[-6]),"""",""MISC00"")))))"
Range("G1").Select

I'd like to be able to add more IFs to it, but there are already 7 conditions in there, which I believe is the max number of nested IFs allowed.
Specifically, at present, I want to add:

IF(ISNUMBER(FIND(""AUTNIS"",RC[-6])),""NISSAN""

But I may need to add more in the future.

Can someone help me write this in a better way? I'm no VB genius...
 

A:Excel 2003 macro - rewrite a little bit of VB Code

Read other 16 answers
RELEVANCY SCORE 67.6

Recently our Office Suite was upgraded from 2000 to 2003.

I had a macro (that you helped with) working well in the old version to upon save take a field from a worksheet 'lists' and write that field into the header and footer, and then update the last saved timestamp....

Now it will not write to the header and footer at all, and will not update the time stamp for last saved.

Here is the code from the worksheet 'lists':
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Or 2 Then
On Error Resume Next

Application.DisplayAlerts = False
'ActiveSheet.protect UserInterfaceOnly:=True
Range("A2").Select
Selection.TextToColumns Destination:=Worksheets("lists").Range("A3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="*", FieldInfo:=Array(Array(1, 1), Array(2, 1))
Application.DisplayAlerts = True
End If

End Sub
(The 'lists' code as far as I know is only there to set the values to be used in the header, the workbook code is the one that takes those values and writes them to the header/footer)


Here is the workbook code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)

Dim filename, rdims, name, fs As String
Worksheets("lists").Range("A6").Value = fs

Call SetSaveLoc
If Worksheets("lists"... Read more

A:Excel 2003 Header/Footer Macro

Read other 12 answers
RELEVANCY SCORE 67.6

Does anyone know how to setup a command button to run a macro?
 

A:Solved: Excel 2003 Run Macro from CammandButton

I figured out how to get the macro to run from a command button.

Here is the code I used....
Code:


Call MacroName

 

Read other 2 answers
RELEVANCY SCORE 66.8

Hi Guys,

I'm after a bit of help.

I'm working on a spreadsheet in Excel 2003, it's a sheet to monitor staff Holidays/lieu days/entitlement etc.
The basic setup is as follows:
the main sheet is 'Staff List' and each Staff member will have their own sheet (named as per their initials)

What I like to achieve is to have an 'fool proof' button (macro) to automatically insert a new sheet and insert the staff members name in cell AD2 and name the sheet using the staff members initials
and also insert a row on the 'Staff List' sheet (row 50, I will manually move it to the proper location (this macro is simply for the times I'm not in and colleagues mess up the formulas)).
Now the tricky bit is that there are a number of formulas on the 'Staff List' that would need to be copied to the new row

I have attached a test file to play around with for anyone who would like to.

Any Help/suggestions much appreciated!

regards
mad-martin
 

A:Solved: excel 2003 insert sheet macro

Read other 16 answers
RELEVANCY SCORE 66.8

I have 2 systems, Windows XP desktop systems Dell GX520 that will not retain their Excel Macro Securiy Settings. I want to change the macro security setting from Low to Medium to enable the popup msg to disable Macros when needed. When I select the radio button for Medium and click OK then go right back to settings, its reverted back to Low. I have tried a Repair of MS Office 2003 and even a Uninstall/Reinstall to no avail. Any advice on what to try next?

Thanks
 

Read other answers
RELEVANCY SCORE 66.8

I have a large file that I print many times and there are some pages included in the list that are not needed and I throw away every time.

Other than print twice, I'm sure there is a macro that can be programmed to skip those pages, or maybe a macro to link the two print ranges.

Can anyone get me started?
 

A:Solved: Interesting Excel 2003 print macro

Read other 16 answers
RELEVANCY SCORE 66.8

I have a macro I wrote in excel 2003 (by recording small steps as a chain of macros), I just upgraded to a new machine and Office 2010.

When I try and run the macro it comes back with error message "Run time error 438, Object doesn't support this property or method."

When I run debug the code line that is flagged is:

Selection.End(X1 to Left).Select

Are there any commands or options which have changed between 2003 and 2010 and is there a conversion list anywhere?

George intermediate user running Windows 7 64bit, Office 2010
 

A:Solved: Excel macro 2003 to 2010 error 438

Read other 16 answers
RELEVANCY SCORE 66.8

Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers
RELEVANCY SCORE 66

Good morning All!

I hope you guys can help me. All of the threads I've been looking at on here seem very informative but I'm having a bit of difficulty getting my idea to work in Excel 2003...

I want to creat a macro (button) that a user can click on. It would pop up a window that says "Enter Customer Name", at which point the user would do exactly that without it being case sensitive (hopefully). The macro would then search through the entire workbook for all customers by that name, add the amounts of all their purchases, invoices, etc., and post them into the cells I have designated.

Can something like this be done in Excel? I looked into using Access but it seems to me that we have too many unique items for that to be useful.

Thanks!
 

A:Excel 2003 Macro Help Needed - Search and Post Results

Read other 7 answers
RELEVANCY SCORE 66

Hallo again! How are you keeping?

I have an Excel 2003 spreadsheet with data in columns A thru J and no header row. This will always be the same.
At present, I perform a Find & Replace on the data, to convert any commas into tildes. One of the columns sometimes contains address info, and as such can contain commas that need to be retained.

I then save the file as a *.csv (comma delimited) file.
Then I drag the csv file into another program (PSPad) which allows me to Find & Replace the commas (created by saving as a csv file) with little sqaures (by pressing Alt + 030).
I then convert any tildes back to commas and save the result as a text file which we can drop into our system to update a massive database.

I'm wondering if I really need this stage of converting to a csv in the middle - I'm sure the find & replace stuff can be done in VB code, and I can export directly from Excel into .txt format. I could do record a macro of myself doing it manually, but I'm not sure how to refer to the wee squares or export a .txt version of the file - i.e. would I just save as a *.txt (Text tab delimited) file or something?

Any help with such code would be greatly appreciated!
 

A:Excel 2003 macro code converting comma delimiters

Read other 9 answers
RELEVANCY SCORE 66

I have a recorded excel (MS Office 2003 professional) macro that does not run at all in MS Office 2007 professional is there an easy edit that I can do to make it work?

SmallFines macro:

Sub SmallFines()

Range("AQ2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown

Range("AQ3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Rows("2:2").Select
Range("AM2").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=43, Criteria1:="CO"

Columns("A:AQ").Select
Selection.Copy
Sheets("Carry Over").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight
Range("AG1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Range("AG1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown
Columns("AG:AG").Select
Selection.Copy
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("R:R").Select
Range(Selection, Selection.End(xlToRight)).Sel... Read more

A:recorded 2003 excel macro no longer works in 2007

I think a sample sheet would be better.
Does the macro do anything at all?
Do you get an error?
Remember that Macro's are disbled by default in 2007.
Check that first.
If you have a sample, I'm now using both 2003 and 2007 so I could take a look at it.
 

Read other 1 answers
RELEVANCY SCORE 66

I am using Excel 2003 to create a database of hyperlinks to different paragraphs and then through the use of a macro I can create word documents. I inherited this macro from a different department and want to customize it for my needs. That being said I have no idea how to edit a macro this large and complex. What's the best way for me to post the macro and my questions, through email, message through this site, post it in this window? The macro is about 15 pages in length. Thanks.
 

A:Excel 2003 - Macro for creating word document question

First, welcome to the forum!!!

The best way for one of the Excel gurus to work on your problem would be to post your file, after you have removed any confidential data (make a copy and do the editing in it). If it is too large, zip it first (and zip is preferred to rar).
If you are unable to post the file (there seems to be a problem at times with people who don't have very many posts being able to attach files) just send me an email via my profile (include the URL link to this thread). I will respond to you and then you can send it to me, after which I will post it for you.
 

Read other 1 answers
RELEVANCY SCORE 66

Hi

I am using excel 2003 (version: 11.8341.8341) SP3 and created a macro to rename the active sheet:
Code:
Sub renamesheets()
Dim activesheet As String
If activesheet = "[B]Sheet01[/B]" Then
Set Sheets("[B]Sheet01[/B]").Name = "[B]qryOfficeNetForeign[/B]"
Application.ScreenUpdating = True
Else: End If
End Sub
SO the active sheetname is: Sheet01 and should be renamed to qryOfficeNetForeign.

This code is not working I am wondering why...

Thanks,
Wouter
 

A:Solved: macro to rename active sheet in excel 2003

You don't need the SET keyword.
Code:
If ActiveSheet.Name = "Sheet01" Then
ActiveSheet.Name = "qryOfficeNetForeign"
End If
or
Code:
If ActiveSheet.Name = "Sheet01" Then
Sheets("Sheet01").Name = "qryOfficeNetForeign"
End If

Regards,
Rollin
 

Read other 2 answers
RELEVANCY SCORE 65.2

I am working in Excel 2003, within XP and have the following situation.

For approximately 18 months, I have been running the macro listed below without issue. Now, for no apparent reason, the macro fails to execute in its entirety. When executing the macro from within Excel, the execution starts and proceeds a few lines and then just stops. No error, no warning, nothing, the macro just stops.

If I open up the Visual Basic editor screen from within Excel and attempt to execute the macro with F5 or step through line by line with F8, execution will proceed to the end of the Selection.TextToColumns command line, and the insertion point goes back to the start of this same command once executed. If F8 is pressed a second time, the insertion point goes all the way back to the Sub line at line 1 of the macro and tries to run the macro again. The behaviour is consistent, and no error code or indication occurs. The code has not changed.

If I break up the macro into a series of smaller macros at each error point, and run them sequentially, I get the desired outcome.

I have tried renaming, copying and pasting the text into different macro files, exporting the macro and re-importing into different worksheets (this one is currently sitting in personal.xls which is still sitting in my /xlstart subfolder.

I have 8 similar macros that now all do the same thing - very puzzling.

Would welcome any suggestions you may have. Thanks all.

ps. for all you real VBA folks out there, no this isn&... Read more

A:Solved: Excel 2003 macro halts during execution - no error message

Read other 7 answers
RELEVANCY SCORE 65.2

I am currently trying to create appointments in a calender i have created in outlook using information from an excel spreadsheet. I have copied several codes from other forums to help with this Macro but have only been able to add the appointments to my calender.

The code i am currently using is:

Sub ExportAppointmentsToOutlook()
Dim olApp As Outlook.Application
Dim olApt As AppointmentItem
Dim blnCreated As Boolean

'Read the table with appointments:
Dim arrAppt() As Variant, i As Long
arrAppt = Range("A2", Cells(Rows.Count, "E").End(xlUp)).Value
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
Set olApp = CreateObject("Outlook.Application")
blnCreated = True
Err.Clear
Else
blnCreated = False
End If
On Error GoTo 0
'Create the outlook item for the table entries:
'Rows:
' Row 1 = date
' Row 2 = starttime
' Row 3 = endtime
' Row 4 = Description
' Row 5 = Location
For i = LBound(arrAppt) To UBound(arrAppt)
Set olApt = olApp.CreateItem(olAppointmentItem)
With olApt
.Start = arrAppt(i, 1) + arrAppt(i, 2)
.End = arrAppt(i, 1) + arrAppt(i, 3)
.Subject = arrAppt(i, 4)
.Location = arrAppt(i, 5)
.Body = "Created by excel tool"
.BusyStatus = olBusy
.ReminderMinutesBeforeStart = 5
.ReminderSet = True
.Save
End With
Next i

Set olApt = Nothing
Set olApp = Nothing
End Sub
I also have the calender location but have no idea where it would need to fit into... Read more

A:Macro/VBE to create appointments in Outlook from an Excel Spreadsheet (Office 2003)

Read other 16 answers
RELEVANCY SCORE 64.8

Hi all... I just joined this forum as past threads have been of great assistance... But now I have a immediate issue... I have wrote some code to pull info from a Excel report and create an Outlook appointment base on various commitment dates... This report is sent out daily therefore the macro is used daily... An issue that has risen is that everytime the macro is ran it creates duplicate appoinments which quickly bogg down the server... I saw an example on a closed thread and could not quite figure it out... Can anyone assist??? I am running 2003 versions of both Excel and Outlook and my code is as follow and thanks...

Sub ExceltoOutlookCommitDateSmithRenewalReport()
'
' ExceltoOutlookCommitDate Macro
' Load Excel Commit Dates into Outlook
'
' Keyboard Shortcut: Ctrl+o
'
' Create the Outlook session
Set myOutlook = CreateObject("Outlook.Application")

If MsgBox(prompt:="OK if YES / CANCEL if NO", _
Buttons:=vbOKCancel + vbQuestion, Title:="HAS PAA BEEN FILTERED?") = vbOK Then
MsgBox "MACRO WILL NOW UPDATE OUTLOOK CALENDAR WITH PROCLAIM COMMIT DATES."

Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste

' Start at row 5
r = 5
Do Until Trim(Cells(r, 1).Value) = ""
If (Cells(r, 20).Value) = "" Then
' Create the AppointmentItem
Set myApt = myOutlook.createitem(1)
' Set the appointment properties
myApt.Subject = Cells(r, 2).Value
If (Cells(r, 5).Va... Read more

A:2003 Excel to Outlook Calendar appoinment VB macro creates duplicates appointments

Hi jayJay,

I'm not going to look through the whole code but what I think happens is that sinec you run the macro every time it will just add an appoitment, I think an options should be added, something like an extra column namen DONE and when the macro is run the cell in the column DONE should be given a value True() or a 1 to indicate it as done, so that when you run the macro again the column DONE cells where the valkue is not True() or is not 1 (dependign on waht you choose) is carried out.
This way you will avoid duplication.
Just an idea, happy coding.
 

Read other 1 answers
RELEVANCY SCORE 64.4

I have just upgraded to Office 2003 from 2000. Everything is working fine except for the macros in one database. I have two different databases with linked tables to our manufacturing and payroll software. I have several macros setup in both databases which runs a series of queries and reports. The macros in the one database still run fine, but they fail in the other database. When I click on the macro it immediately closes the database. I can run the queries individually without a problem. Any ideas?
 

Read other answers
RELEVANCY SCORE 64

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Read other 6 answers
RELEVANCY SCORE 62.4

Firstly I am a beginner of beginners but I try to learn as much as I can.
I have put the following protection code into an Excel 2003 workbook and everything runs ok. I then opened the workbook in Excel 2007, and saved it as a .xlsm file. When I try to open the .xlsm file in Excel 2007, I get a vba Runtime error 13, "Type mismatch", and the code sticks at the first line below("If Worksheets...). I have tried everything that my limited knowledge allows, searched the web for 2 days, and still have no answers.
Any help would be greatly appreciated.

Private Sub Workbook_Open()
If Worksheets(28).[A51] > "" And Worksheets(28).[A50] = Worksheets(28).[A51] = False Then
MsgBox "Sorry, this program is not registered for this computer, please e-mail .......... for more info."
Application.DisplayAlerts = False
Application.Quit
End If
End Sub
My system specs are shown below.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM) i3 CPU M 380 @ 2.53GHz, Intel64 Family 6 Model 37 Stepping 5
Processor Count: 4
RAM: 2934 Mb
Graphics Card: Intel(R) HD Graphics, 1243 Mb
Hard Drives: C: Total - 290143 MB, Free - 172186 MB;
Motherboard: Dell Inc., 0WXY9J
Antivirus: avast! Antivirus, Updated and Enabled
 

A:Solved: Excel 2003 vba code not working in Excel 2007

Read other 16 answers
RELEVANCY SCORE 62

Hi.

I have built a macro that imports Data from another worksheet, and to add to the user experience, I want to add a progress bar.

I have added the code, and userform to make this happen, however, it wont update (i.e. show me the progress).

I have tried turning screenupdating on, setting the userforms properties ShowModal=True, and ShowModal=False.

When the ShowModal is set to true, the user form actually displays, but gets itself into a neverending loop and wont quit.
When the ShowModal is set to false, all I get is an outline of the form, but the middle of the box is white and works as normal.

The code is invoked from several different subs as I have different subs that import a different part of the worksheet.

I am using the following code:

Progress.Show
Progress.lblBreakoutNo.Caption = "Currently Importing"
PctDone = BreakoutNo / NoofClaims 'update Progress bar
With Progress
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
Any help would be appreciated.

Thanks

Lightingman2003
 

A:Solved: Excel Macro - Progress Bar - Not Working

I have now managed to solve it. It was user error, forgot to add "DoEvents" after the ProgressBar.

Hope this helps somebody else out though.

Will now mark as "Solved".

Thanks

Lightingman2003
 

Read other 1 answers
RELEVANCY SCORE 62

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

Read other answers
RELEVANCY SCORE 62

Hi All

I have a trial excel 2007 version that came with my Vista Home Premium.
I had a spreadsheet which contains macros and I had no problems running with XP.

I changed some of the settings and completed the following:
http://www.howtogeek.com/howto/wind...nt-control-uac-the-easy-way-on-windows-vista/

I followed the following link from the developer of my particular spreadsheet and completed the following for 2007 version:
http://www.mdmproofing.com/iym/macros.php#excel2007

I still cannot use some of the functions that I had been using before such as Sort and doubling clicking a cell which is supposed to open a calendar and auto function add monetary figures.
Could it be the trial version? Any help is appreciated.

Thanks,
Transformer Man
 

A:Solved: Excel Macro Vista not working

Hi Transformer Man,

I was just wondering, have you solved the problem for Macros working on your Vista Machine?

As I currently working with a collegue to solve this problem, and would like to confer with someone else who has had a similar problem.

Regards,
Tom Griffiths.
 

Read other 3 answers
RELEVANCY SCORE 61.2

Hi,
I have a monthly report I run which outputs single sheet "Invoices" from my workbook that is populated by a months worth of sales. The macro goes through a list of names, pulls the details for that person, populates the pivot table on the invoice,
then saves as both a single excel sheet and a pdf. up until today it has worked fine. the code is, and the error occurs when it reaches the date line. It states "Compile error, can't find project or library":
Sub Save_Sheets_To_New_Books() 'INVOICES
Const strWbPath As String = "D:\Accounts\EA Letters\2020 09 September\"
Dim strDate As String 'todays date
'save the sheets to new books within the active folder and print them
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
strDate = Format(Date, "yyyy.mm.dd")     '(Date, "yyyy.mm.dd")
THIS IS WHERE THE ERROR OCCURS the word DATE is highlighted
ActiveSheet.Copy
Call DeleteNamedRanges
Call PrintAreaAndPasteSpecial
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strWbPath & Name_of_Artist & "_" & strDate & ".pdf" _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False         
ActiveWorkbook.SaveAs FileName:=strWbPath & Name_of_Artist & "_&quo... Read more

Read other answers
RELEVANCY SCORE 61.2

Hi there

John Walkenbach, the Excel guru who wrote many Excel VBA books, gave me this hyperlink macro that finds Excel workbooks. The macro is complex but I'm sure that someone out there can figure it out. This macro works great in Excel 2000 but does not work in Excel 2013. When I run the macro in Excel 2013 I get a message that says "File not found" and I can't figure out why. I believe that the line "FileType = msoFileTypeExcelWorkbooks" could be causing the problem but I'm not sure.

Thanks

Mario

Below is the code.

Sub Hyperlink1()

Dim i As Long
Dim Folder As String
Dim FileToFind As String

Set objShell = CreateObject("Shell.Application")
On Error Resume Next

Folder = "C:\"
FileToFind = ActiveCell
Select Case ActiveCell
Case ActiveCell
Case Else
MsgBox "The file was not found"
End Select

With Application.FileSearch
.NewSearch
.LookIn = Folder
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 1 Then
If .FoundFiles.Count = 0 Then
MsgBox "File not found.", vbCritical

Else
For i = 1 To .FoundFiles.Count
If Right(.FoundFiles(i), Len(FileToFind)) = FileToFind Then

Workbooks.Open .FoundFiles(i)
End If
Next i
End If
End If
End With
Set objShell = Nothing
End Sub
 

A:Solved: Hyperlink Macro not working in Excel 1013

Apparently MS has degrade the FileSearch over time.

This looks like something to try Microsoft.com Thread
 

Read other 14 answers
RELEVANCY SCORE 61.2

Hi

I started to use VBA recently. I have a macro, which use solver to get some solution. The worksheet contains about 14,000 rows of data and I need to use solver as many times. In the macro, I added Application.ScreenUpdating=False at the beginning and Application.ScreenUpdating=True at the end. But it seems to me that the screen kept updating. I can see the cells changing values one by one. It took more than one and a half hour to finish the macro. I guess the Application.ScreenUpdating=False doesn't really work in my macro. But I couldn't figure out the reason. Any help or suggestions are greatly appreciated.

Since my data is pretty large, I created a "vbtry.xls' and write the macro in a simple manner. But it uses the solver repeatedly. The macro name is "test". As you can see, the screen keeps flashing if you run the macro. My understanding is that: with Application.ScreenUpdating=False, the window should look like freezing and the data should be posted all at once at the end. Am I right on this? What's wrong with my macro?

Please see the attached file. Thanks.
 

A:Solved: EXCEL: Is ScreenUpdating=False working im my macro?

In your example you have not defined SolverSolve, Solverfinish, and SolverOK. When I run the macro the code bugs out. Are you missing some code? Explain how the macro is supposed to work and what these variables or procedures are. Is there an add-in missing from the project?

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 60

I'm attempting to write my first macro for an Excel 2003 workbook. I'm not completely code illiterate (I've got moderate skills with AutoLISP), but I'm new to VBA and am not yet an Excel power user, so please be gentle.

The macro I want to write will:
check that the selected cell's content is underlined before proceeding
copy the content of the currently selected cell into an external plain text .log file
.log file lines should be: year/month/day - time - username - cell contents
.log file names will probably need to be generated
clear the cell's content and formatting (particularly underline and text/background color)
Here's what I have so far:
Code:
Sub Unpost()
If Selection.Font.Underline = True
Then Selection.ClearFormats And Selection.Clearcontents
Else
If MsgBox("The selected cell is not underlined...are you sure?", vbOkCancel) = vbOk
Then Selection.ClearFormats And Selection.Clearcontents
Else Exit Sub
End If
End If
End Sub
If I've written it correctly, it should currently do everything except log the cell contents. This, from what I've seen, is going to be the trickier part. I intend to use this macro 50+ times per weekday, so at some point the .log files will get too long to be useful, so I assume it will need to automatically create new logs (perhaps "year-month.log"). I've seen some useful info about appending to an external log here and here, ... Read more

A:Excel 2003 macro: log contents of selected cell, clear cell

You need to use the "File Scripting Object" to create and/or append text to a file. I've included a link below to get you started. If you are unable to figure it out on your own let me know and I'll write the code for you.

http://www.virtualsplat.com/tips/visual-basic-fso.asp

Rollin
 

Read other 1 answers