Over 1 million tech questions and answers.

Excel 2007 Macro error

Q: Excel 2007 Macro error

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?

Thanks!

Read other answers
RELEVANCY SCORE 200
Preferred Solution: Excel 2007 Macro error

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.)

RELEVANCY SCORE 80.8

Hi,

Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.
 

A:Solved: excel 2007 macro error

Read other 7 answers
RELEVANCY SCORE 73.6

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

A:Access 2007 VB code to run Excel 2007 macro in active sheet

Read other 7 answers
RELEVANCY SCORE 72.8

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.8

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 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.4

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 70.4

Excel '07 won't open an "excel 4.0" macro (i.e. *.xlm file). I am one of a few users at work using excel 2007, I believe this file was made with excel 2000 or similar. When I had excel 2000 it opened fine, it's a common file on the network that everyone should be able to access, and everyone with excel 2000 can easily. So I know it's not the file. When I try and open it with excel 2007 I get that stupid error about the file being different to the extension.

I've tried registry edits and workarounds after scowering the net but nothing seems to fix it. All it does is display the error, then opens excel in the background without even a new worksheet. Excel isn't hanging, it just does nothing. Any ideas?
 

A:excel 2007 won't open excel 4.0 macro

XLM was long ago replaced by VBA (with Excel 5 in 1993) and MS no longer really supports it (although I think they claim that they do, kinda, sorta.) So the only thing you can do, I think, is to make a copy of the xlm file and convert the xlm code in the copied file to VBA. Fortunately, I found a link that tells you how to do that:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
(Look at the link at the bottom, too. But you will need to download that to an older PC, possibly.)
Now, assuming all goes well, this will give you two versions of the same file, but if everyone else has Excel 2000 (or later) then they will all be able to use the new VBA-based file, so you won't end up with two files that would lead to confusion.
Still, it looks to be a lot of work, so good luck!

On a final note, if you need this file done in VBA and you can't figure out how to redo it, make a post describing your needs - there are some amazing VBA coders on here.
 

Read other 1 answers
RELEVANCY SCORE 69.2

Hi everyone,

Thanks to Zack Barresse for replying to my last thread; I have a similar question this time.

I need a macro that will quickly convert all text in a given cell (or range of cells or column) into uppercase. Up to now I've been manually entering the =upper(cell) formula, copying the contents, and then Paste Special -> Values for each one I have to do; this is obviously time-consuming.

(also a link to a beginner VB or Excel macro course might be useful, heh..)

Thanks!
Joel
 

A:Excel 2007 Macro

Read other 11 answers
RELEVANCY SCORE 69.2

View attachment 212815
Hi,

Please find the attached word document for my query on excel macro. The current data and expected results are mentioned in the attached document. Please help me in creating a macro for my expected results.

Regrads,
Akki
 

A:Macro help for excel 2007

Read other 6 answers
RELEVANCY SCORE 69.2

I have been trying to figure out if it was possible to do a fairly complicated replacement.

Just a description of the file:
- There are columns a-q in the file, with rows going up to about 9000.

Each day I have to upload several hundred files onto an outside website which I then have to copy down by hand the numbers of the files. Those files are organized similar to "5 bay - 99,84,77,33,45 STR 555 - AA.2012.file type". The only unique piece of each file is the number following STR, which is the part that i write down. This number is in column E.

My job after writing down the numbers of each file that fits into a corresponding row in column E, is to go into columns O and P and put my initials in column O and the current date in column P.

I was wondering if it was possible to make this quicker through the use of some macro. I can always type the numbers into a separate excel sheet and feed the macro that list somehow. The columns are not always O and P either. It can also be J and K, or Q and R as well depending on what I had done.

So all in all, I'm asking if it is possible to create a macro that I can feed the number that corresponds in column E and have it add my initials and the current date to the two columns of my choosing. Thanks in advance, even if it isn't possible!

PS: A blanked version of the worksheet we use is included
 

A:Excel 2007 Macro Help

Hey there, just registered to help you out! Check this and tell me if it works, just make sure to backup your files beforehand *wink*

Sub Book1()

Dim Initials As String
Dim StoreColumn As Integer
Dim InitialsColumn As Integer
Dim DateColumn As Integer
Initials = "KM"

For i = 1 To 30

If ActiveSheet.Cells(1, i).Value = "Store No." Then
StoreColumn = i
Else
If ActiveSheet.Cells(1, i).Value = "Assignment Completed by:" Then
InitialsColumn = i
End If
If ActiveSheet.Cells(1, i).Value = "Date Assigned" Then
DateColumn = i
End If
End If

Next i

For j = 1 To 10000

If ActiveSheet.Cells(j, StoreColumn).Value = "5526" Then
ActiveSheet.Cells(j, InitialsColumn).Value = Initials
ActiveSheet.Cells(j, DateColumn).Value = Now()
End If

End Sub

Im not sure of your experience level with excel but paste it in VBA in excel and run it. The code searches through columns to find the ones with stores, initials and dates and remembers where they are. Then it searches through stores to find the 5526 value and puts the current date and initials that you can set at beginning of the code. Let me know what you think.

PS. Before you run it make sure to check the names of the columns. I wrote what I saw but it isn't necessarily what there is. Just copy the names from columns and paste them to the code
 

Read other 2 answers
RELEVANCY SCORE 69.2

I wish to make a macro that will paste All from the clipboard Excel 2007 ( do the same as the Paste All Clipboard button )
The cell is A1 sheet "H"
after it has pasted there another macro to clear All (do the same as the clear all Clipboard button)
any ideas on the macro code??
Many thanks
Steve
 

Read other answers
RELEVANCY SCORE 69.2

I am trying to create a Macro in which I have data in sheet 2 that I copy and paste into Excel 2007 from a website that changes on a monthly basis that I need to place into sheet 1 at the bottom of the page.

Currently, sheet 1 column A is the date. Currently the 9/30/12 date is on row 44 and row 45 would be 10/31/12, etc...each row is a new end of month. I do this on a quarterly basis and for multiple tabs so the macro would be for all the sheets, but I figure if someone could tell me the macro for sheet 1, I can change the sheet names in the macro for the other sheets.

I need to create three rows at a time starting on row 45 for the next three months, then row 48 for the three months after, etc... Cell g45 will come from sheet 2 cell F3; g46 will come from sheet 2 cell F4 and g47 will come from sheet 2 cell F5. I need these cells to be copied and pasted with values only because I will change Sheet 2 on a quarterly basis and I need the "old" numbers to stay and be fixed. This means I can not use the equal sign to setup my formula. Cell H45 will use the formula from cell h44 (I normally just drag down) and this will continue until cell h47. Cells M45, N45, & S45 use the same drag down feature for the formula that the "H" cells do. There are no other columns with active data in it.

Does anyone know how to create a Macro for this? I tried to do the Macro Record feature, but it does not move down, it only create three new blank rows and does not give me... Read more

A:Excel 2007 Macro Help

Post a sample workbook and I'll be happy to take a look and offer my assistance.

Rollin
 

Read other 2 answers
RELEVANCY SCORE 69.2

Hi everyone,

I need a few macros (I have no experience with VB or writing macros):

To search a column and add a period (.) to the end of any fields that do not have one.
To search a column for any instances of ellipses (more than one consecutive period) and reduce them to a single period.
To search a column for any instances where there is a period or comma followed by anything other than a space, and to add one.

To have one macro that could do all three would be ok, or separated into separate functions.

Thanks in advance!
Joel
 

A:Excel 2007 Macro

One to eliminate multiple consecutive spaces would be useful as well.

With the others, the functions can be at cross-purposes: for example, if a field ends with a period, I don't want to add a space; similarly I would want to eliminate ellipses first, because otherwise when I search for periods followed by not-spaces, ellipses will be caught in that and changed.

Forgive my lay-speak. I'm not a dev or anything, just an editor who wants to make his work faster by eliminating these common errors.
 

Read other 2 answers
RELEVANCY SCORE 69.2

I hope you guys can help me out again with this. It seems similar to the the issue that I posted last time. But I think it is a little bit more complicated.
I receive two notepad - files (for example importfile 1 and importfile 2) via mail, I copy paste the data in an excel sheet.
Each line in the notepad - files is an order. The format of the file is :
- Character 1 to 2 (included) = Plant
- Character 3 to 5 (included) = Route
- Character 6 to 8 (included) = Suffix
- Character 9 to 18 (included) = Delivery Date
- Character 19 to 27 (included) = DunsNo
- Character 28 to 35 (included) = ContainerNo
- Character 36 to 40 (included) = Quantity
- Character 41 to 49 (included) = Total Weight
- Character 50 to 61 (included) = Volume
- Character 62 to 69 (included) = Planned Date
A csv-file ( has to be created for each line with the same route, suffix, delivery date and DunsNo.
The name of the csv-files to be created has the following format : Route_Suffix_Delivery Date
(for example DG2_3HB_22-12-2010
The output file consists of following format :
Cell A1 is always T , Cell A2 = the data in collumn D that matches the routeNo in collumn A of a excel-file (for example Trailer_Types1)
Then each row begins with P in collumn A for each line with the same route, delivery and DunsNo
Collumn B = Plant of notepad - file
Collumn C = Route of notepad - file
Collumn D = Suffix of notepad - file
Collumn E = Delivery Date of notepad -file
Collumn F = DunsNo of notepad - file
Collumn G = Container... Read more

A:VBA - Excel 2007 - Macro

Read other 16 answers
RELEVANCY SCORE 69.2

Hi,
i'm using excel 2007 for the first time. i download a few spreadsheets every day that require a lot of reformatting and i use the "record macro" button to write the macro so that i can same about 1 hour every day (i have very limited knowledge of vba). the problem is that even if i save the worksheet the macro disappears the next day.

please walk me through on how to exactly record a macro that i can use on a daily basis. i tried putting it in the personal macro workbook but it doesn't take.

regards,
 

A:Excel 2007 Macro help

Read other 10 answers
RELEVANCY SCORE 68.4

Hi. My name is Chuck and I am new to the forum. I am working on an Excel 2007 Workbook in which I am saving certain activities to macros. When creating these macros, I am storing the macros in "This Workbook". My ultimate goal is to be able to save this workbook with the macros I have recorded and assigned to buttons within the workbook to a thumb drive and give it to a friend who can then open the file on any machine that has Microsoft Excel 2007 running on it, and use the workbook and have the macros work. However, it appears that when I close the file and reopen it later to continue working on it (on the machine I am creating the file on), it is as if I never even recorded the macros... they are gone. Please help.
 

A:Excel 2007 Macro Question

Read other 9 answers
RELEVANCY SCORE 68.4

Hello,
Macro's for excel is new for me so I hope somebody can help me.
I receive a notepad file via mail. The content of this mail I copy/paste in an excel sheet.
This Notepad file consist of H-lines (=header lines - begins with H) and B - lines (Goods lines - begins with B).
For each H-line I have to create a new file in Excel. In this file, for each B-line that comes after the H-line,I have to get data in different cells:
In Cell A, I need characher 420 to 422 (included) of the H-line
In Cell B, I need character 2 to 10 (included) of the H-line
In Cell C, I need character 23 to 31 (included) of the H-line
In Cell D, I need character 27 to 35 (included) of the B-line
In Cell E, I need character 2 to 9 (included) of the B-line
In Cell H, I need character 348 to 351 (included) of the H-line
In Cell I, I need character 61 to 69 (included) of the B-line
After all the B -lines of the H-lines are processed the file may be saved as CSV-file (comma delimited)
Then the next H-line can be processed and a new file may be created.
Then the process is the same as above. This continue until no H- or B-lines are found in the file.

Can anybody help me?
 

A:Solved: VBA Macro - Excel 2007

Read other 16 answers
RELEVANCY SCORE 68.4

Hi, I'm trying to make a macro that will go to a comma in a cell and then delete everything from the comma to the end of the line and then do this for the whole worksheet. The comma must also be deleted. Is it possible to do something like this or must it be done manually? Thanks for your help.

Example:
Red, Gold and yellow with Green Stripes

The final result:
Red
 

A:Solved: Excel 2007 Macro Help

Read other 13 answers
RELEVANCY SCORE 68.4

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

Sale Date Sale Price Adj Sale Price 2010 Land 2010 Imp 2010 Total
7/7/2009 10,000 10,000 13,300 0 13,300
7/7... Read more

A:Excel 2007 Macro Sum 2 Columns

Read other 13 answers
RELEVANCY SCORE 68.4

Hello,
I am an analytical chemist and I am dealing with very large data matrix (11,000 x 3).

I am looking for a macro that can reduce the data in following way

Column 1 Column 2 Value
A, B 1
B, A 1
A, C 1
C, A 1

With a Result output

Column 1 Column 2 Value
A, B 1
A, C 1

In other words in this data set A to B is the same as B to A and would like to delete the duplicate row.

Any help doing this with Macros would be appreciated. You can imagine going through 11,000 line looking for these types of entries
 

A:Solved: Excel 2007 Macro help

Read other 8 answers
RELEVANCY SCORE 68.4

I have researched my issue but other threads posted here did not solve my problem. I have an Excel 2007 spreadsheet that was first designed in Excel 2003, and it has a macro that generates 93 spreadsheets with prepopulated data. The macro works, it generates the files but then I cannot open them due to the following error:
'Excel cannot open the file 'xxx.xlsx' because the file format or file extension is not valid.'
I have tried changing the macro to save as .xlsm, but get the same result. I have conditional formatting that will not work in Excel 2003, plus most of the people who will be opening the file are on Excel 2007, so I don't want to save in xls format; however, I did change the macro to see if it worked for Excel 2003 and it does.

Sure hope someone can help.

Here is a sample from the macro:

Range("B1:I1").Select
ActiveCell.FormulaR1C1 = "HP Argentina"
Range("C2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\P2012 Templates\P2012 Country Summary and Cost Forecast - HP Argentina.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
 

A:Excel 2007 Macro Issue

If the macro is being run in 2007 you should first try changing the FileFormat value that you are passing. Change the portion below.

FileFormat:=xlNormalClick to expand...

So that it reads

FileFormat:= xlOpenXMLWorkbookClick to expand...

If the workbook is a macro enabled workbook use the line below instead.

FileFormat:= xlOpenXMLWorkbookMacroEnabledClick to expand...

Rollin
 

Read other 2 answers
RELEVANCY SCORE 68.4

Hi, I have got an Excel spreadsheet which has incident numbers in column A. For each of these incidents I need to put in hyperlinks which are very very similar. The first part of the hyperlink is the address to the database and the second part is the Incident number itself. I'm trying to create a macro which automates the process of putting in the hyperlink plus the incident number in. Can this be done?
 

A:Excel 2007 Hyperlink Macro

Read other 7 answers
RELEVANCY SCORE 68.4

hi hi,
could you help me to solve my macro problems with excel 2007.
i had the following macro in 2003 which is not working now:

Sub Journal()
'
' Journal Macro
' Macro recorded 28/01/2008 by od28689
'
' Keyboard Shortcut: Ctrl+j
'
Sheets("Upload").Select
Sheets("Upload").Copy
Dim div As String
div = Range("A1")
ChDir "Z:\UPLOAD\US1"
ActiveWorkbook.SaveAs Filename:="Z:\UPLOAD\" & div & "\JOUR25.prn", FileFormat:= _
xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Sheets("Journal").Select
Range("D8:E8").Select
End Sub

Many thanks in advance!
cheers,
tekike
 

Read other answers
RELEVANCY SCORE 68

I have a workbook that has no macros. Every time I open the file I get this message: "Security Warning Macros have been disabled" Is there a way I can get excel to show me what it is disabling?
 

Read other answers
RELEVANCY SCORE 68

Hello. I am attempting to develop a macro that will allow me to find a number in column D and replace that value with a text name. I have 31 - 5 digit codes with 31 corresponding text names. The spread sheet is 10,000+ entries and growing every day. For example, I would like to find all instances of "21021" (in column D ONLY) and replace them with the word "Boyle." And so on, for all 31 codes. I'm new to Macros and could really use a little help to get me started!

Thanks!
 

A:Excel 2007 Macro for Find and Replace

Read other 7 answers
RELEVANCY SCORE 68

Hi All.

I have an attendance list for a course I'm running. It is essentially an array with the names of the attendees down the first column and a series of columns to the right (so that there is a block for the "X" i use to mark that they attended)

The problem is that if a person leaves early or starts the course late, I need to add their name and so I need the whole array to auto sort in rows by the first column. Obviously I can do this manually but I would prefer that it happen automatically if the first column list is changed. I have tried using a macro but the calculation takes ages. VB code is the same...

Can someone help me with a code which will do this for me?!?

I have attached the worksheet in question

TIA

drj
 

A:Excel 2007 Sort Macro Problem HELP!

Read other 7 answers
RELEVANCY SCORE 68

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 68

I'm working on a macro that does several things with budget reports. I have zero knowledge of Visual Basic, just recorded my macro and played with the code.
It creates a line called "total expenses" then, six cells to the right, I need it to add the value in a row titled "total coaches" and another value in a row called "total directors". these values are 6 cells to the right of the titles. so what I had tried to do was search for the "total coaches", move over six cells, take that cell's location and add it to the "total directors" value with the same process. however, the macro just used RC to record the cell's position. the position of the value needs to be dynamic as the reports will change.

I can get it to create the "total expenses" line and make the cell six to the right of it active. I just can't get the formula to be dynamic based on a text search.

here's the relevant code:
ActiveCell.FormulaR1C1 = "Total Expenses"
Range("A247").Select
Cells.Find(What:="Total Expenses", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(6, 0).FormulaR1C1 = "=(R[-147]C+R[-1]C)"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFo... Read more

A:Solved: Dynamic Excel 2007 Macro

This should do what you want, but I don't have an example to look at so I just made up my own example

You can adjust the column numbers to match yours. Note that I made some additions. I think in your recorded macro you went to Row 247 at the start, which I am assuming is the last row...I added code to find it for you.


Code:
Public Sub test()


Dim Coach As Integer
Dim Direct As Integer
Dim totalExp As Integer

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand


' finds the last row in column 1
lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'fills in cell 2 rows below last empty
Cells(lrow + 2, 1) = "Total Expenses"

totalExp = lrow + 2


Cells.Find(What:="total coaches", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Coach = ActiveCell.Row


Cells.Find(What:="total directors", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Direct = ActiveCell.Row


Cells(totalExp, 2) = Cells(Coach, 6) + Cells(Direct, 6)


err_Hand:
' 91 is the error number when the search value is not found
If Err.Number = 91 Then
MsgBox "Search value not found"
Exit Sub

End If

End Sub

 

Read other 3 answers
RELEVANCY SCORE 68

Is there a way to have Excel open a folder and place the file name with extensions in Column A. Then open another folder and place the contents of that folder into Column B?

I also would like the list to skip adding file to column if it already exist on worksheet, but if a file was deleted from folder it would be removed from the worksheet.
 

A:Excel 2007 Macro/Folders & Files

Hi

Here is a macro that I use quite often ...
Code:


Sub Get_File_Names()

[B]'1st directory[/B]

lRowA = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lRowA).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 1) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 1) = MyFile
Wend


[B]'2nd directory[/B]

lRowB = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:B" & lRowB).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 2) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 2) = MyFile
Wend
End Sub

Unless identifying which files are new and what's been deleted is important I would just delete the previous list and get all current files - as I've done in this code.
 

Read other 2 answers
RELEVANCY SCORE 68

Greetings!

I have a macro in Excel to filter out rows with zeros. That works good. I want to change the "0" to a text string "(A1)". How do you do that? I tried putting both double and single quotes and it wouldn't work.

TIA for your ideas!

---------------------------------------------------
Option Explicit

Sub HideZeros()
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd End Sub

Sub ShowZeros()
Selection.AutoFilter Field:=1
End Sub
 

A:Office 2007 Excel Macro has me stumped...

Try something like this....
Code:


Selection.AutoFilter Field:=1, Criteria1:="=[I]TextGoesHere[/I]", Operator:=xlAnd

 

Read other 2 answers
RELEVANCY SCORE 68

Hello, I am very new to Excel 2007 and even newer to Macros. I have a spreadsheet that is created automatically that can contain many rows of data. The First column always holds certain words, such as "pending" or "Resolved". I would like to create a Macro to sort all of the rows with the same word in the first column into new sheets. For example, let's say their are 20 total rows in this sample, 8 rows have Pending in the first column and 12 have Resolved in the first column. I would like to search for all the rows that have pending in the first column and cut and paste the entire row to a new sheet. Then do the same for Resolved. I do not have much coding experience, what would be a good example?
 

A:Solved: Excel 2007 Sort Help - Macro

Welcome to the board.

Here's some code, whether it's a good example would be debatable. Post back if you need help with installing/running.

Sub pending_resolved()
'add new sheet, 'Pending'
x = ActiveSheet.Name
Sheets.Add
ActiveSheet.Name = "Pending"
Range("A1") = "Status"
Sheets(x).Select
'get last row on main sheet, for range to loop
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'loop range; if cell = 'Pending', copy row then blank cell
For Each Cell In Range("A2:A" & LastRow)
If Cell = "Pending" Then
Cell.EntireRow.Copy Sheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1)
Cell.Clear
End If
Next Cell
'delete all rows = blank in column A
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'rename main sheet 'Resolved' (only 'Resolved's remain)
ActiveSheet.Name = "Resolved"
End Sub
 

Read other 2 answers
RELEVANCY SCORE 68

Hi All,

I'm trying to create a macro that will fill cells within a column with a zero when the cell is blank and leave the cell as-is when it is populated with a value. I figured out how to fill a column with zero down to the last active row in the sheet but I need to revise it to skip the cells with values.

Please help, I'm stuck at this point and it is the last part of the macro that I cannot figure out.

Thank you!

Jen
 

A:Solved: Excel 2007 Macro Question

Read other 9 answers
RELEVANCY SCORE 68

I am looking for a macro that willl do the following:
I have a date in cell A12
I want to put that date in each cell of column b as long as the corresponding cell in column a has a number in it.
After all dates are entered, I want the macro to skip two lines and start again, but this time add one day to the previous date.
Can someone help me on this?

Here is a screenshot which will hopefully clear up what Im wanting to do.


 

A:Solved: need help writing macro in excel 2007

Read other 16 answers
RELEVANCY SCORE 67.2

Hi there - Using MS Access 2007.
I have a FORM with a macro that runs a query to generate ONE excel file and attaches to email using MS Out look to send out. - That Works great.

Problem: Multiple users to send email & attachment.
I would like for the macro to generate excel file (S) - based by the UID.

Can someone suggest a better way of handling this that is not time consuming?

Thanks In advance!
 

A:MS Access 2007 - Macro > Excel > Auto Email

Access can open Outlook and send an attachment to mutiple recipients either as a list or as individual emails.
So are the attachments all the same for the recipients?
 

Read other 1 answers
RELEVANCY SCORE 67.2

Hi Guys,

First of all thanks for the great support provided by your forum!

I'm using with remarkable profit the following macro edit by Rollin_again (sorry in case the nick is wrongly written!)

PHP:

[SIZE="5"]Sub ReplaceText()



For 
Each vCell In Range("A2:A200").Cells



Columns
("C:C").Cells.Replace What:=vCellReplacement:=vCell.Offset(0Read more

A:Macro Multiple Find and Replace in Excel 2007

To get a green fill the code you could use would be
vCell.Interior.Color = vbgreen

but is you place this before Next cCell, I think all the cells for vCell would turn green.

You turn the colouring back to normal by using

vCell.Interior.Color = xlNone

Give it a shot
 

Read other 3 answers
RELEVANCY SCORE 67.2

Hi all,
This is just a preliminary request for advice before I undertake writing a fairly substantial VBA macro.

My plan, in short, is as follows:

- User receives a daily Invoice file. The data from this is to be dropped onto an Excel Table I've prepared (let's call this the Invoiced Table) in the Master File, which will allow some formulae, such as IFs and Vlookups to static tables, to auto-populate. Each day's data is to be appended to the bottom of the table.

- User receives a weekly Paid file. The data from this will be dropped into a second Excel Table (let's call this the Paid Table) on another worksheet in the Master File.

- Once the weekly file data has been dropped in, the user will run the macro. This will compare records from the Invoiced Table against the Paid Table using at least 4 (probably 8) different sets of lookups. In a simplified example, these might be 4 different customers.

- Each set of lookups will result in some matches. For each matching record for a specific customer, fields from the Invoiced and Paid tables need to be combined into a new record on a seperate worksheet.
So in a simplified example, Record 1 might have an Invoice Number in the Invoice Table that matches the Invoice Number in the Paid Table. I'll need to copy the Invoice Number, Customer Number and, say, the Invoice Date field from the Invoiced Table, plus the Paid Date from the Paid Table.

- I'll then need the macro to delete those lines from both t... Read more

A:MS Excel 2007 - VBA Macro to split large file

Is excel the only option? Access would do a lot of this without the having to write a macro every step.
 

Read other 2 answers
RELEVANCY SCORE 67.2

Hi Guys,

I'm using with much profit a code that I have found in this forum:

Sub ReplaceString()

StartRow = 1
EndRow = 10
For i = EndRow To StartRow Step -1
Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Acutally this search coloumn "C" for values in Coloumn "A" and if found replace those values in "C" with what you have in "B:".
Example:

Before MACROAA1ABB1TCC1NB1B2B1After MACROAA1A1BB1TCC1NB1B2B2

what I need is the replaced value in "C" be highlighted as already in the example (iether by red fonts or green interior is ok).
The code need an addition to do that.

Could you help me please?

thanks!


 

A:Macro Multiple Find and Replace in Excel 2007 (2nd leg)

Hi, Why not post a sample file with the code you're using and also show what you need.
It's easier to work with a sample file.
Make sure the data is dummy data
 

Read other 1 answers
RELEVANCY SCORE 67.2

Hi
I need a macro for sorting name and adresses;
Orginal format in cells like this
A1 Name
A2 Last Name
A3 Adress 1
A4 Adress 2
A5 Po number
A6 City
A7 "EMTY"
A8 Name
A9 Last Name
etc....
Some times there is a emty cell between adress and sometimes not.
Also the number of cells may variate (downwards). One complete adress could sometimes be 6 cells and other times 5 or even 4. Mixed in same file

I want it to be sorted so that it appear in a new worksheet sorted like this
A1 Name, B1 Last name, C1 Adress 1, D1 Adress 2, E1 Po Nr, F1 city
A2 Name ...............etc.......

Some ideas ????
 

A:Solved: EXCEL 2007 - macro - Real Challenge !!

Read other 15 answers
RELEVANCY SCORE 67.2

Hi:
I am very new to Excel 2007 and macros. I have a spreadsheet that I am trying to get to send an email reminder to the point of contact [ col b ] 5 days prior to the closure document due NLT date [ col m ]. I am looking for assistance in writing a macro which will accomplish this if it is possible. I have attached the spreadsheet that I referenced.
Your assistance would be greatly appreciated.
Thanks in advance.

desantisj
 

A:Excel 2007 Macro to Send Reminder Email

desantisj, welcome to the Forum.
There are already 3 or 4 posts on this forum that have the VBA code (Macro) that you can modify for your Workbook if you can read the code. Zack has written the code so it is a bit complicated, but it should be a case of substituting your Cell references that hold the data for the ones that others have used.
Otherwise it is a case of waiting for an Excel guru to come along and help. If none of them come along I can probably help you, but I normally work with Access.
 

Read other 2 answers
RELEVANCY SCORE 67.2

I just bought a new laptop running windows 8, downloaded and installed Excel 2007. I have the Developer tab active, did the Macro Security changes to allow activex, enable all macros etc., but the Record Macro, Visual Basic and Macros buttons are inactive. That is, they are not clickable. I updated to windows 8.1 - no joy! Otherwise the install of Excel 2007 seems okay. Spreadsheets from my old computer & a much older version of Excel open okay, formulas work, etc. The Macro issue is the only problem I am aware of at this point. The symptoms are the same on a new (blank) workbook or on an existing spreadsheet. Any suggestions? - I guess a re-install is my next option. Thanks in advance. Bob
 

A:Solved: Excel 2007 Macro & Visual Basic Not Available

A re-install of Office 2007 did the trick. I enabled functions this time that I had not enabled the first time. Don't know which one solved my problem but the macro buttons are now active & I can get on with it....
 

Read other 2 answers
RELEVANCY SCORE 67.2

Greetings -

Given:

Target Complete Money
A B C
1 1/1/09 1/7/09 50.00
2 1/5/09 1/12/09 100.00
3 1/7/09 1/14/09 50.00
4 1/17/09 1/17/09 25.00
5 2/2/09 1/26/09 25.00
6 2/5/09 150.00
7 2/6/09 150.00
8 2/5/09 75.00

Currently i have the following formula to calulate how much money that I might make.

{=SUM((MONTH(A2:A100)=1)*C2:C100)}
This works fine as I have no blank cells in column A

If I try to apply this formula to column B I get an incorrect number as I have blank cells in there with no values.

Is there a way to continue to use this formula but have it check for blank cells and if it finds any, don't add those values??
Thanks!
 

A:Excel 2007: Date/Sum Macro/Blank Cell

Welcome to the board.

How about the attached? Contains 1 "master" dynamic named range & 2 others based on the "master". Use E2 to define the month you're after.
 

Read other 3 answers
RELEVANCY SCORE 66.4

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

Read other answers
RELEVANCY SCORE 66.4

I'm really new with macros, but I want to know if it is possible to do the following scenario and how to accomplish it:

I have two sheets in one workbook. On sheet 1, there is an inventory which contains codes, dates, titles, and most importantly, subjects in 6 columns (G-L). On sheet 2, I put the old subjects (from G-L on sheet 1) in column A and the new subjects in column B. I want to utilize a macro to find all the "sheet2 column a" subjects on sheet 1 (match entire cell contents) and replace them all with the new "sheet 2 column B" subjects. Can this is done? How would I do it?

Thank you for any advice you might have, I truly appreciate it!

A
 

Read other answers
RELEVANCY SCORE 66.4

Hi,

I'm new to this, so any help would be appreciated.

My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

Sub CheckBox2_Click()
Rows("1:3").Select
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Range("$A$1").Select
End Sub

And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.

SO PLEASE HEEELP ME
 

A:macro excel 2007 that copies filtered rows to another sheet

Read other 13 answers
RELEVANCY SCORE 66.4

Hi,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. I want to be able to find the value from Column A and replace it with the value in the same row in column B wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).
Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,

Thanks

Lynchie
 

A:Solved: Macro Multiple Find and Replace in Excel 2007

Read other 9 answers