Computer Support Forum

Solved: Hyperlink Macro not working in Excel 1013

Question: Solved: Hyperlink Macro not working in Excel 1013

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

Relevance 100%
Preferred Solution: Solved: Hyperlink Macro not working in Excel 1013

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

Answer: 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

14 more replies
Relevance 93.48%

I am trying to create a macro that will look at the contents of a cell and insert it into an address on the server. For example, the contents of the cell is: folder\file

The location to hyperlink to is: \\server\share\folder\file.doc

I need to keep the file extension in the macro so that clicking the link will automatically open the file. I just need to get the contents of the cell to fill in the folder name and file name.

Thanks in advance.
 

Answer:Solved: Excel Hyperlink Macro to File

16 more replies
Relevance 82.82%

Hello

I am very new to using Macros but I am trying to create a new one that creates a new hyperlink and new file when ever a cell in column A has a value put in it.

What I would like is for the MAcro to take the Value of A1 and impose that value into the name of the new document (which would be created as part of the Macro), which is then shown as a hyperlink in A1.

This process could then repeat down the rows.

Would this be possible?

Thank you
 

Answer:Excel Macro Hyperlink

I made this a few years back, you will need it to convert to hyperlink, and you can use the references to adapt to to your code.

you should use the recorder first to establish a base of what you are trying to do, then adapt.

Code:
Sub hyperlink()
'
' hyperlink Macro
' 7/18/2007 by Ziggy
'
Application.ScreenUpdating = False

Dim hyper As String ' hyperlink variable
'
Dim endrow As Integer
Dim RowX As String ' Cell to change
Dim RowC As Integer 'row Counter
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

endrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row


RowC = 1



Do Until RowC > endrow

RowX = "A" & RowC 'Modify Letter for Column

Range(RowX).Select

If Range(RowX).Value <> "" Then ' skips cell if blank

hyper = Range(RowX).Value
Range(RowX).Hyperlinks.Add Anchor:=Selection, Address:= _
hyper _
, TextToDisplay:= _
hyper

End If


RowC = RowC + 1

Loop


Application.ScreenUpdating = True


End Sub

 

1 more replies
Relevance 82.82%

Here is a challenge for all you Excel Gurus. Attached is a macro that when you run it, searches for the file that you have selected in an Excel worksheet. For example, I have a file called testing.cells. I have typed this file in Cell A1. I then select cell A1 and run the macro and voila, the Excel file called testing.cells appears. The reason I do not do a normal Excel hyperlink is that the hyperlink at times does not work if I e-mail it my colleagues since the file is on a server. This is due to how the paths are configured in different computers.

The attached macro works great if I'm hyperlinking to another Excel workbook. I have tried to customize the macro to bring up a pdf file but without success. I'm Excel macro guru. As a matter of fact, the attached macro was given to me by John Walkenback, the author of many Excel books.

Is it possible to do this or not?? I do not want to waste anybody's time if this is not do able

Mario
 

Answer:Excel Hyperlink Macro

I guess no one out there can help me on this issue. Surely to God someone must have a clue.

Mario
 

2 more replies
Relevance 82%

Not sure if this is possible, but I'd like to get a macro for the following:

I've got a workbook with multiple tabs, let's call them "Summary", "Tab1", "Tab2", etc. On the summary tab, I'd have a value with a reference next to it that says "Tab1". I'd like that reference to be hyperlinked to a similar cell on worksheet "Tab1" where it finds the reference that says "Summary". So basically, if my summary sheet shows an amount of $50, I'd have a reference next to it that says which sheet shows the support for that number, and on the supporting sheet, it would reference that that amount shows up on the summary. I'd like to run a macro that would find those references and hyperlink them to the correct sheets (and if it can do it, to the specific cell on that sheet).

Anyone?
 

Answer:Excel hyperlink macro question

Hi there, and welcome to the board!

Can you define your requirements a little bit more? We need to know about your data structure of your workbook. What ranges are you talking about? Are there any other hyperlinks? What would you like them to say? Care to give a real-world example with your file? Can you upload it?
 

1 more replies
Relevance 82%

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?
 

Answer:Excel 2007 Hyperlink Macro

7 more replies
Relevance 82%

I am not sure how to go about this macro. I am trying to produce a hyperlink in a column (equipment info) but as it goes down a row change the hyperlink to reflect its file name that is found in a different column.

so the hyperlink location is "\\SNOGP01\poshare\PoShare\North Omaha System Files\10 Year Mechanical Plan, major plant equipment\1AC-1A Info & Name Plate.docx"

The next one down would need to change the "1AC-1A" to "1AC-1A-M"

Any help is much appreciated.
 

More replies
Relevance 82%

Hello!

I am new to this site and to the world of macros. I am creating a form in Excel for my company and cannot find any info for creating a macro for hyperlinked info.

Background of my form:
It's a time off request form. My dad (who knows a lot of Excel and macros (but not this kind)) has helped me get to where I am at this point.

Currently I have a dropdown for employee names. Once a name is selected, it automatically fills in their manager and department number. I have a Reset Form button to clear info out and would like to create a Submit Form button that would send the form to the manager. Is something like this possible?

I'm guessing it would need to pull from my Tables sheet.

Help! I hope I made some sense.

P.S. I will attach a sample of what I have so far.
 

Answer:How to have a macro add hyperlink to a cell in Excel

9 more replies
Relevance 82%

I am using Excel 2007. I want to hyperlink several stock symbols to open stockcharts.com. I know how to hyperlink, but think it would be quicker since I have over 50 to create a macro. The problem is the display text stays the same each time I run the macro - so basically each cell reads AAPL instead of continuing. What should I do?

I have Windows Vista installed.

Thanks in advance!
 

More replies
Relevance 81.18%

Hi Again

Keballah gave me this macro that when I put the cursor in a cell that has for example WW1.xls and then execute this macro it opens up the Excel workbook named WW1. However, when I try to open a PDF file like WW1.pdf I get garbage and not the PDF file. Can someone out there take a look at the macro below as an example and create a new one so that it also brings up PDF files??

Thanks

Mario


Sub Demo()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String
Dim thisFolder As String
Dim fullFilename As String

Set fso = CreateObject("scripting.FileSystemObject") ' late binding
'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

Set fldStart = fso.GetFolder("M:") ' <-- use your FileDialog code here
Mask = ActiveCell.Value
thisFolder = fldStart & IIf(Right(fldStart, 1) = "\", "", "\")
Debug.Print thisFolder, Mask
If Not IsError(Dir(thisFolder, vbReadOnly)) Then
If Dir(thisFolder & Mask, vbReadOnly) <> "" Then
Workbooks.Open Filename:=thisFolder & Mask
Else
fullFilename = ListFolders(fldStart, Mask)
If Len(Trim(fullFilename)) > 0 Then
Workbooks.Open Filename:=fullFilename
End If
End If
End If
End Sub

Function ListFolders(fldStart As Object, Mask As String) As String
Dim fld As Object 'Folder
Dim thisFolder As String
For Each fld In ... Read more

Answer:Hyperlink Macro in Excel VBA that finds PDF files

You're getting garbage as you're asking excel to open a pdf as a workbook:

Code:
Workbooks.Open
You'd need to open the PDF using a PDF viewer and code accordingly.
This might be helpful:
http://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html
 

2 more replies
Relevance 80.36%

Hi,

I've used Tech support guy for quite a while and almost always find what I need.
I guess it's about time I join and post a question

So this is my problem.
I send out an excelreport every day.
In it I use the content in column X to populate the hyperlink address in column Y.
I've done this manually but now the report is getting bigger and a macro seems in place.
Problem is I just don't know enough about the syntax used in excel.

What I want to do is loop though each row - if there is data in column Y then check in column X.
If X is populated use the data as hyperlink address in column Y (info in Y should still be what is shown).
This should be done for each row where Y is populated.
Then the X column shall be deleted (but I guess I could do that manually )

It feels pretty straight forward and I could do it in RPG or SQL but the syntax in excel befuddles me
Any tips?
 

Answer:Excel macro - use info from one column as hyperlink address in another.

Assume Y2 and X2 cells
=if( Y2 = "", "", IF(X2="", "" , do Y2)

but you need to have the formula in Y and change Y - i think that may need a macro, as you are testing Y and also rewriting Y

a spreadsheet with dummy data would be useful to see
 

1 more replies
Relevance 78.72%

Hi. I have a select range of cells with a set of code that unprotects the sheet when selected in order to allow hyperlinks to be created.

However when I select an active hyperlink in one of those cells it gives me the following error even though it works correctly:

Run-time error '9':

Subscript out of range

The code is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 13 Then
Sheets("Summary of Contracts").Unprotect Password:=""
Else
Sheets("Summary of Contracts").Protect Password:=""
End If
End Sub

I would like to ask whether this error can be avoided or whether some coding can be added to hide/remove the error message itself.

Thanks
 

Answer:MS Excel 2000 Select hyperlink on a macro enabled protected sheet

I tried this on my machine and it was caused when the sheet name wasn't 'Summary of Contracts'. Suggest you need to check the sheet name, if you don't use the sheet names then you could use sheets(1).unprotect etc. But thats not a great idea either!
 

3 more replies
Relevance 75.85%

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
 

Answer: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.
 

3 more replies
Relevance 75.85%

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
 

Answer: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
 

1 more replies
Relevance 75.03%

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.
 

Answer: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
 

3 more replies
Relevance 70.52%

Whenever I click on any hyperlink, got an error "The operation has been cancelled due to restrictions in effect on this computer.Please contact your system administrator." My system is windows 7 and Microsoft office 2007.

Answer:Hyperlink is not working in excel & outlook.

What I will check?

2 more replies
Relevance 68.47%

Trying to add hyperlink to text box in Excel 'webpage'.
Is there a way to make the hyperlink open in new page, using the Excel 'Edit Hyperlink" window, when any person is browsing and clicks on the link ?
 

More replies
Relevance 66.83%

I need to add a Hyperlink from this formula =IF($D$3="","",VLOOKUP($D$3,Database!$A$2atabase!$Z$199,5,FALSE)). Everything I have tried I have not been able to get it to work. Please help!
 

Answer:Solved: Excel VLookup Hyperlink

8 more replies
Relevance 66.83%

Trying to make a spread sheet with some weblinks. Some links are very short and will fit on the page, others run off the page onto another one. Is there any secret to shortening the link. It's even too long for landscape
 

Answer:Solved: Insert hyperlink into Excel

16 more replies
Relevance 66.83%

I have Excel 2007. Today, (never previously), when I click on a hyperlink to send an email to the subject, I get a Microsoft Office Excel message "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator".

To my knowledge I have done nothing to create this. The only remedial action I have tried is a reboot, but the problem persists.

Can anyone offer any solutions please?
 

Answer:Solved: Hyperlink error in Excel

6 more replies
Relevance 66.83%

Hello All,

I was wondering whether any of you can help me. I am using the following hyperlink in excel. When a user clicks on it it open up another excel spreadsheet, in a different area on a shared drive.

here is the link i am using

=HYPERLINK("W:\Licensing\Gambling Act 2005\REGISTERS GA2005\AWP Premises Licence.xls")

Now this work fine. However I was wondering whether or not it is possible to also direct it to a cell or row of cells. I am using Excel 2003. I have tried the actual hyperlink icon and this works until I come to saving the spreadsheet and I get an error to say it cannot save certain elements and when I go back in to it the link no longer works.

If you need anything clarifying, please contact me.

Thanks in advance.
 

Answer:Solved: Help with excel HYPERLINK or formula

9 more replies
Relevance 66.83%

Running Excel 2003, I can't create a hyperlink because I am getting this error, "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator". Since this is my personal computer what do I need to change to make my hyperlink work?

Thanks,

Upon further research, this is what I did to fix it. : See http://www.slipstick.com/problems/link_restrict.htm#reg2

Edit Registry, Part 2

Start, click Run, type Regedit in the Open box, and then click OK.
Browse to HKEY_CURRENT_USER\Software\Classes\.html
Right click the value for the .html key and select Modify...
Change the value from "ChromeHTML" to "htmlfile" (or from FireFoxHTML to htmlfile)

 

More replies
Relevance 66.83%

Hi all,
Using Excel 2010 and Acrobat X.

I have a spreadsheet with a list of names in one column and email addresses in another column. I want to combine them so that the name is displayed and the email address is a hyperlink. I can do this easily by using =hyperlink("mailto:"&a1,b1). The problem is that I then need to convert the document to PDF. The conversion will keep manual links, but not links created with the hyperlink function. I've seen a couple of suggestions such as saving the spreadsheet to HTML and then converting to PDF or a rather ingenious solution to copy the contents of the worksheet and then use Acrobat X to create a PDF from the clipboard. Both of those work, but they lose things like the borders around the cells and the repeating rows at the top of each page.

Any ideas? Thanks.
 

Answer:Solved: Excel Hyperlink Function to PDF

This should be the links as per Microsoft will create.
Not sure if this is what you are looking for.
 

2 more replies
Relevance 66.01%

HiWhen I try to open a hyperlink in Excel 2003 I'm suddenly getting:'This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator.'It happens with hyperlinks to webpages & also to items in the same workbook, so all hyperlinks.It's happening to existing hyperlinks in previously working workbooks & new hyperlinks.Thanks in advance for any help...

Answer:Excel 2003 hyperlink problem *SOLVED*

Do you own this computer or is it owned by your employer or someone else?  Who is the system administrator?

10 more replies
Relevance 66.01%

First time posting here. I'm somewhat stuck with a rather simple macro. I cannot get the hyperlink that is created with the macro to link back to the sheet I just created. I get a "Reference not valid" error.
Can anyone find any format errors in my hyperlink line? I've tried every combination I've found online but cannot get it to work.
Code:
Sub newFAIRcopy2()
'
' newFAIRcopy2 Macro
'
'
Sheets("FAIR").Select
Sheets("FAIR").Copy Before:=Sheets(2)
mynewsheet = ActiveSheet.Name
Sheets("Sheet1").Select
Selection.Copy
Sheets(mynewsheet).Select
Range("H4:I4").Select
ActiveSheet.Paste link:=True
Sheets("Sheet1").Select
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=mynewsheet
End Sub
 

Answer:Solved: Excel VBA Hyperlink to Variable name worksheet

Hi

Try this line to substitute for your last line
Code:
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & mynewsheet & "'!A1", TextToDisplay:=mynewsheet

Because you are linking to a sheet that has a space in the name the hyperlink reference must be enclosed in single quotes. Also, you need to provide a cell reference for the link.

See if this does what you want and if you need any more help please upload the file so we can see what you are copying and pasting and what results you expect to see.
 

2 more replies
Relevance 66.01%

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

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

7 more replies
Relevance 66.01%

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
 

Answer: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?
 

1 more replies
Relevance 66.01%

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

Answer:Solved: Excel macro to extract Word form data into Excel

7 more replies
Relevance 65.19%

[Already posted on Mr Excel but received no replies.]

I've inserted a simple hyperlink in an excel 2003 spreadsheet that links to another excel 2003 workbook. The link works fine and does exactly what it's supposed to do: namely, when I click on it, the target workbook opens.

The minor, though irritating, problem I have is that the hyperlink overrides my custom settings for XP. In the advanced settings of My Computer's properties, I have set the Performance Settings to Custom. There, I have unchecked all the options except 'show windows contents while dragging'. Now, whenever I click on the hyperlink on my worksheet, either XP or Excel overrides my custom settings by checking the 'Animate windows when minimizing and maximizing'. If I go into the custom settings and uncheck the box, it gets re-selected again as soon as I click on the hyperlink.

I can't for the life in me understand why this should happen, as the one does not appear to have any relation to the other. I've tried googling the solution but have come up with nothing. Can anyone tell me what's going on and how I can prevent it from happening?

Thanks.
 

Answer:Solved: Excel hyperlink overrides XP custom settings: help!

Further to my post. I've since discovered that 'animated windows' only lasts until I switch off my pc. Next time I start it up, the custom settings have reverted to their original settings (i.e., with the animated windows option unchecked). In other words, the way the excel hyperlink overrides my custom settings in XP only lasts for the duration of the current session. Bizarre and pointless.
 

1 more replies
Relevance 64.78%

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Column... Read more

Answer:Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again
 

1 more replies
Relevance 64.78%

I am following instructions, and the link appears in the cell, but when I click on it it only jumps to My Pictures and not to the actual photo jpg. What am I doing wrong?
 

Answer:Solved: Trouble inserting a hyperlink to a photo in Excel 2003

9 more replies
Relevance 64.78%

Hi,

I'm creating a procedure using Excel. Each cell is used to list an action step and includes reference to a procedure which resides on the business's website. I want to add a hyperlink to the reference text title only.

I can only seem to create a hyperlink for the entire cell, but some of the steps refer to two separate procedures, hence I need to create the link to the text.

PS. I've used Excel, rather than Word, as I'm using formulas to then sort and filter various steps.

Many thanks in advance!
 

Answer:Solved: Creating a hyperlink for text/icon WITHIN an Excel cell

 

3 more replies
Relevance 64.78%

Hi folks,

Does anyone know how to change the purple default colour for a used hyperlink in Excel 2003?
I have managed to use Format/Style/Modify option to take off the underline and set my font size but would like the actual text not to change clour from blue to purple.
 

Answer:Solved: Changing visited hyperlink colour in Excel 2003

Make sure you have a hyperlink and a followed hyperlink in the sheet

Format > Style and in the name dropdown choose hyperlink / followed hyperlink and > modify. You should be able to change the colour
 

2 more replies
Relevance 64.37%

I have been using a macro in my excel spread sheet for a year. it usually updates the formula to the next row cells but now it is just adding the rows and not the formula. How would I repair this if I didnt create the macro or why has it stopped working

Answer:excel macro is no longer working

First thing that comes to mind is to make sure you still have formulas in the row to be copied.How big is your spread sheet?I haven't tested the macro extensively, but it should keep adding rows till you hit the bottom of the sheet at row number 1,048,576 MIKEhttp://www.skeptic.com/

13 more replies
Relevance 64.37%

Hello,I am trying to write a macro to determine the percentage of dates in a column of cells (format: 30-Sep-10) that occur before the end of the current calendar year.I have tried using the DCOUNTIF function, but I think I am having problems with excel recognizing the date and working with the date in the context of the current calendar year. This is what I have, which doesn't seem to work at all. The C[-2] is the column with the dates in it:For i = 2 To Sheets.Count Worksheets(i).SelectRange("J8").Select ActiveCell.FormulaR1C1 = "=DCOUNTIF(C[-2]:C[-2],<DATE(year(now())+1,0)" Range("K8").Select ActiveCell.FormulaR1C1 = "=DCOUNT(C[-3]:C[-3])" Range("L8").Select ActiveCell.FormulaR1C1 = "=((RC[-1]-RC[-2])/RC[-1])" Range("J6").Select ActiveCell.FormulaR1C1 = "Ground Task IDs" Range("J7").Select ActiveCell.FormulaR1C1 = "due this year" Range("K7").Select ActiveCell.FormulaR1C1 = "Req" Range("L7").Select ActiveCell.FormulaR1C1 = "% complete"NextThank you for any help.

Answer:Excel Macro working with dates

Hi,I don't think that you need a macro to do this.As a test I had 1000 dates in column A in cells A2 to A1001The dates ranged from 01 January 2009 to 31 December 2011The method I used is based on counting the total number of dates and the total number of dates that are on or before 31 December 2010.Then calculate the percentage.=COUNT(A2:A1001) counts all cells containing numbers (Excel stores dates as numbers - 40373 is 14 July 2010)I used =COUNTIF(A2:A1001,"<=" & DATE(2010,12,31)) to count the number of cells containing a date on or before the 31st December this year.Putting all this into one formula I get:=COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))/COUNT(A2:A1001)Then format the cell with a percentage number format.Or if you prefer the value directly use:=(COUNTIF(A2:A1001,"<=" & DATE(2010,12,31))*100)/COUNT(A2:A1001)Regarding a macro, i did this:Option Explicit

Sub datePercnt()
Dim intTotal As Integer
Dim sngBefore As Single
Dim rngDates As Range
Dim dtEndDate As Date
Dim rngCell As Range
Dim sngResult As Single

'set the range containing dates
Set rngDates = Worksheets("Sheet1").Range("A2:A1001")

'set counters to zero
intTotal = 0
sngBefore = 0

'set end date
dtEndDate = DateSerial(2010, 12, 31)

'loop through each cell in the range
For Each rngCell In rngDates
'add 1 to count of all dates
intTotal = intTotal + 1
'test date in cell against end date
If rngCell.Value <= dtEndDate Then
'if on or before add 1 to 'before total'
sngBefore ... Read more

8 more replies
Relevance 63.55%

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.
&n... Read more

Answer: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 & "... Read more

2 more replies
Relevance 63.55%

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
 

More replies
Relevance 63.55%

How do i get the same macro format but for it to read from Sheet2 CX2:CX150 And only give the answer for the row that they are on in Sheet 1, eg, AT3 would run macro and show CX3

I have already worked out how to get the macro working, if someone clicks Yes in the listbox in Sheet 1, AT2.. it then runs the Worksheet_Calculate2 macro and checks to see if any of the cells are not correctly filled in (in this case, CX2 checks multiple cells) however when they goto AT3, and then click yes, then i'd need another macro for the excate same thing but for CX3..



Code:

Sub Worksheet_Calculate2()
Application.ScreenUpdating = False
If Range("Sheet2!CX2").Value > 0 Then msgbox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub


This is a stubbon problem and i've been trying to work on it for nearly a week now..
If i can just get some help on how to do this...

Thanks in advance..
 

Answer:Problem with macro in Excel VBA working across sheets

Hi Bitsbb01,

If you are using worksheet level event then you can use offset, something like

Private Sub Worksheet_Change(ByVal Target As Range)
If VBA.InStr(1, Target.Address, "AT", vbTextCompare) Then
Worksheet_Calculate2 Target
End If
End Sub

Sub Worksheet_Calculate2(ByVal SelectedCell As Range)
Application.ScreenUpdating = False
If SelectedCell.Offset(0, 56).Value > 0 Then MsgBox "Theres At least 1 Cell Not Filled in, Please check again and then continue"
End Sub
 

3 more replies
Relevance 63.14%

The site was built with MsFrontPage, but I now edit the site using SharePoint Designer. My hyperlink should be opening an Excel file, but instead it's opening a zip file. The type is listed as a Compressed (zipped) folder. How can I fix this issue. Here is the link: http://www.msrevenda.com/HTML Pages/free_lessons.htm / try to download the 2007 Excel Bill Tracker. Thanks in advance for your help.
 

Answer:Solved: Hyperlink Not Working

Change the program association to Microsoft Excel for the filetype .xls
On Windows 7 you can do that by clicking Start >> Default Programs >> Associate a filetype or protocol with a program
 

2 more replies
Relevance 63.14%
Question: Hyperlink Macro

Hello every one.i have the below workbook structureI have a Summary Sheet and a variable no' of sheets (sheet are added each time a new entry is required)The variable sheets are in serial ( labled 1,2,3,....), each time sheet is added it's labeled with it's sequence ( last sheet serial +1)Each sheet has a row to summarize it in the Summary sheet, the row has a label in cell B1, this label is the sheet nameI have a macro that copy a sheet and insert it at the end of the sheets and give the sheet it's serial and add a row in the summary sheet and label it with the serialwhat i need to do is to add instruction to the macro to add hyperlink to the row lable to the sheet it summarizes So if the summary sheet cell B5 value is "8" then i need summary sheet cell B5 to have hyperlink to sheet "8"Can this be done?

Answer:Hyperlink Macro

It might help if we saw the code that you are using, but here's an example to get you started.The code makes 2 assumptions:1 - The last entry in Sheet1 Column B is the one that should become a link.2 - The link should take you to last sheet in the workbook.Sub LinkToSheet()
lastBRow = Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
Sheets(1).Hyperlinks.Add Anchor:=Range("B" & lastBRow), _
Address:="", SubAddress:= "Sheet" & Sheets.Count & "!A1"
End Sub

5 more replies
Relevance 62.32%

To anyone who can assist with this and teach me something new in the process. I have an issue with the hyperlink not working where I'd like it to work in the attached spreadsheet.

I've named and created a hyperlink below the form attached in cell B196 titled Banned Countries Policy. In the form in cell L73 is where I would like to be able to actually click on the link, strictly for countries that fall on the banned countries list. Currently, the way the formula is set up I get an error stating, "cannot open specified file".

The cells that I'm working with are also highlighted in yellow in the attached spreadsheet. FYI, I'm working in Excel 2010 but needs to be compatible with 2003.Does anyone have any suggestions or remedies to this issue?

Thanks,

Mario
 

Answer:Solved: Hyperlink not working in form

6 more replies
Relevance 62.32%

I ran a report from a database for a user which opens up to an html page with hyperlinks to pdf files. She she clicks on the hyperlink it attempts to open in IE and will not open. I have gone into file types and made sure pdf docs are to always open in Adobe, and if she clicks directly onto pdf file, the document will open in Adobe with no problem. However, when she clicks on the hyperlink, it still tries to default to IE and won't open. I can't duplicate this problem and it works fine for other computers. Any ideas on how to fix this problem please???
 

Answer:Solved: Adobe Hyperlink Not Working

7 more replies
Relevance 62.32%

First time poster so bare with me.

My issue is: My customised QAT in Excel 2007, that includes 'not in ribbon' buttons, doesn't save when I add a macro button for default or specific docs.



The macro button and the macro will work fine when I add it, but when I close and reopen the file or email the file, the button has gone

The bizarre thing is, it was working a few weeks ago. I even had some customised macro buttons in the file that I was trying to add more to, and when I saved and reopened the file, ALL the custom macro buttons disappeared.

I've already searched google for days now trying to find a solution.....

I've read ron debruin's site back to front http://www.rondebruin.nl/qat.htm and http://www.rondebruin.nl/qat2.htm

The instructions are there for the process but no solution as to why the button wont stay put.

I'm running Windows 7, and obviously Office 2007. Also, this is a work PC on a network (I'm not the network admin). The network admin hasn't done anything to the system that he thinks would have done the changes i'm whinging about.

Any help, ANY!!!!, will be greatly appreciated.

Thanks

D
 

Answer:Excel 2007: Macro Quick Access Toolbar Not Working

WoW

I've either not explained myself properly or i've stumped even the brightest of PCReview forumers.

Guess i'll have to live with the issue

Thanks anyway
 

13 more replies
Relevance 61.5%

1st cell A4 (worksheet: SumList) -- link to cell K1 (worksheet: Communication History)2nd cell A5 (worksheet: SumList) -- link to cell K27 (worksheet: Communication History)3rd cell A6 (worksheet: SumList) -- link to cell K53 (worksheet: Communication History)This goes on until A3004 (3,000 records) -- each time link to cell in worksheet: Communication History with an incremental of 26 cells.Please advise how I could use macro to create hyperlink to another worksheet with an incremental of 26 cells for 3,000 records.Thank you.

Answer:Create macro to hyperlink between 2 worksheets

I believe that this code does what you ask, but I had to change Communication History to Communication_History. It appears that building the Hyperlink in VBA doesn't work correctly if there is a space in the sheet name. It throws up a Reference Is Not Valid error when you try to use the Hyperlink.
Sub IncrementHyperlink()
'Set Row For First Link
linkRw = 1
'Loop Through Rows 4 - 3004
For rw = 4 To 3004
'Build Hyperlink By Appending Link Row Variable, Place In Column A
Sheets("SumList").Hyperlinks.Add _
Anchor:=Cells(rw, 1), Address:="", _
SubAddress:="Communication_History!K" & linkRw, _
TextToDisplay:="Communication_History!K" & linkRw
'Increment Link Row By 26
linkRw = linkRw + 26
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03

4 more replies
Relevance 61.09%

i have created a spreadsheet that has a list of names down the left, days of the week and dates across the top and a "user' choose in the data fields.The spreadsheets are in one workbook and i have a sheet for each month.

What i need to do is create a form that will enable users to choose a "Date" period ie start and end dates and choose a "name". i then need this form to have a button on it that when clicked would generate an email to the relative person named which would have all of the relevant data in it.

I know how to create the actual form, it's linking everything to it thats the problem, so would really appreciate some help.

Hope this all makes sense !!!
 

Answer:Solved: Excel macro help req

15 more replies
Relevance 61.09%

Hello,

I've used this site to create my current excel file; it works pretty good but now it need the evolve and I require some help.
I'm sure there are easier ways to get what I need; I am no expert at macro writting so please bare with me.

THe file current creates graph for me; 'data input' has a button where i input the locations i require a graph for and it simply selects info (from a master file database) and pastes it into 'sheet2' where a graphs exists.

the code is written so that it will go to a pdf print screen for me to input the name and save. this worked well but now there are so many locations that i would like to automate some functions.
what i would like the update to do is the following;

-'results table', column A, "Location" will have several locations listed (only in the white blank spots). I need the macro to generate a graph for each location listed.
-for this to occur, it makes sense that I would also require the pdf to automatically name each file ~ naming it the location that it was looking up (otherwise i will have a bunch of pdf save as files open and i wouldn't know which was which.
any and all input would be greatly appreciated. i also looked up the auto name pdf file; i did not include my attempts to add as it kept getting hung up at the 'save as' file box. i can share my pregress on that if you like.

below is my current code. Please email me so that i can send you the file a... Read more

Answer:Solved: Excel Macro help

15 more replies
Relevance 61.09%

I need to write a macro to look down all the cells of an entire column of one worksheet and whenever the value of a cell is greater than zero, copy that value and paste to the next empty cell along a different column. For instance, I'm looking down the entire column and D5 is greater than 0, so I paste it to N1,. Next, D8 is greater than 0, so i need to be able to paste this to N2 and so on. Can someone help? Thanks
 

Answer:Solved: Excel Macro

Hi,

Copy and paste the code to a "Module"
Code:

Sub Check_Zero()
Application.ScreenUpdating = False
Dim cel As Range
Dim lcel As Long
For Each cel In Range("D2:D" & Range("D65536").End(xlUp).Row)
If Cells(cel.Row, 4).Value > "0" Then
lcel = Range("N65536").End(xlUp).Row + 1
Cells(lcel, 14).Value = Cells(cel.Row, 4).Text
End If
Next
End Sub
 

3 more replies
Relevance 61.09%

here my situation: I have created 2 worksheets. Both are contact list.
the 1st list is of People who work in the same building as I. The second is
a list of nationwide remote users. My boss emailed me a third sheet
containing names from both list combined. the question I have is can I
write a macro to check the third sheet against the 1st two then seperate
them back out? if you need more info let me know
 

Answer:Solved: need help with excel macro

8 more replies
Relevance 61.09%

I have 50+ excel workbooks and I need to extract 1 tab from each of them and create an individual file for each.

A girl who used to work in my office found a macro or program using excel, that only works on PCs, but I have one available, thatall you have to do is have the selected tab saved so that it opens up to the one you want and paste the entire thing as values and it will do it for you real fast. The alternative is to go in and copy and paste each tab or extract the sheet in each and every one. If anyone can help me find the name of this or where I can find it I would really appreciate it. Oh ya the girls who used to work in my office moved to India, canceled her cell and I do not have her personal email so contacting her is harder than I first thought.
 

Answer:Solved: Excel Macro?

14 more replies
Relevance 61.09%

hi... i am really having a problem with creating a macro, could you please help me.

i have to copy content from a auction website, paste it into excel.
this is what the content looks like

Auction - 125458
view bigger image --- name of item
ref number --- start date
end date --- price of item
page views
users tracking
no bids
relists remaining (this is sometimes left out and causes my stress)
copy or relist
edit
close
delete
the above content over 3 columns and over different rows.
i managed a simple macro to put these into column’s, but the problem being that sometimes there is not the same amount of rows with the relists remaining left out sometimes....
so what i need is a macro to put the data above into column’s,
eg... word starting with auction = c1
word starting with view bigger = d1
word starting with item name = e1
---------- then when the next word starting with auction = c2

could you pretty please help me with this
 

Answer:Solved: excel macro help please

9 more replies
Relevance 61.09%

Hello, I need some help with a macro. I am writing an If...Then...Else statement. The If is based on whether the current cell is blank. This seems simple enough from my experience with Lotus 1-2-3 macros but I can't make it happen. Here is where I am -

If ActiveCell.Value.Isnull = True Then
Blah...Blah...Blah
Else.... Help me! Thanks
 

Answer:Solved: Excel Macro (VB)

Welcome to the board.

If ActiveCell = "" Then
Do this
Else
Do that
End If

It's the same as IF formula, only in code.

Rgds,
Andy
 

2 more replies
Relevance 61.09%

I am running Excel 2003. I am creating a macro that open files from a external hard drive. I was hoping to find a way for the macro to find the Drive letter of the external hard drive instead of me having to statically assign the drive letter.

Any help with this would be great.
 

Answer:Solved: Excel Macro

9 more replies
Relevance 61.09%

Hello TECHGUYS.
We are using macro fro creating P.O.B. lis ( persons on board) , from the 3500 persons, copying only those who is on board at the moment , then grouping them by company and than sorting in alphabetical order in each group.
Now is the challenge : Is that posiible to modify macro or add another function to it to make it sort personnel of first company in the list by rank / position?
I am almost green in scriptin, but very interesting in it , but now my boss wants to see Himself on top of the list He's last name not starting from A
Thanks in advance...
 

Answer:Solved: Excel macro help

12 more replies
Relevance 61.09%

I am have a form in excel that when a certain person types his name at the end the following cell will put in date that he signed it. The problem is that when you e-mail the doc. the following day (or whatever day) the date changes to the current date and not the actual date that the doc was signed. (by signed i mean the person typing his name there) Here is the code that is currrently in the doc.
=+IF(F36="Persons Name",H43," ")

I'm not sure if there is some kind of formula for this or do i have to write a macro for it. If i do have to write a macro for it, could someone please help me out with it.
 

Answer:Solved: Excel Macro

6 more replies
Relevance 61.09%

Hello,

I've built an excel spreadsheet and am having trouble with a part of a macro. The relevant section of the macro that I am having trouble with is below:

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("$A$1:$A$1094"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

The spreadsheet is basically a template, so as I get new data, I open it up, dump the data in, run the macros and save. The problem is, the data is often different sizes, so one day I may have 1000 rows, and the next 1400. This is not a problem with generating charts, since I can just select that it plots cells 0-2000, and it will ignore cells without data, but if I do this for the above regression, the macro will not fully run and I get an error. It's not really a big deal, as I can open up the macro every time and just edit the range to fit the data I have, but other people often need to use it so I'd like to make it as simple as copy, paste, and run macro1.

Is there a way to insert a macro within the macro so excel automatically searches the column and then runs the regression?

Something like this

Application.Run "ATPVBAEN.XLA!Regress", ActiveSheet.Range("Only rage of cells with data in them"), _
ActiveSheet.Range("$B$1:$C$1094"), False, True, , "PS stats", False, _
False, False, False, , False

I've seen a macro here on how to find cell... Read more

Answer:Solved: Help with excel macro

9 more replies
Relevance 61.09%

I have an excel report (tab name is "sample report")that lists many rows of data (each row is a new case). In column "H" it lists who is working on each case. Column "I" lists dates. If a date is shown then that means the case is closed. If the cell is empty that means the case is still open.

What I need is a macro that will copy all of the "Open" cases from the "Sample Report" tab and put them into the other tab "Open Case Log" under the specific names in column "H". I have put 5-10 blank rows under each name on the "Open Case Log" tab but I am never sure excalty how many open cases each person will have at any given time. This is where I am having the problem. I have attached the sample excel report.

Any and All help would be greatly appreciated.

Thank you.
 

Answer:Solved: Need Help with Excel Macro

16 more replies
Relevance 61.09%

Ok not sure if this can be done or not, but hopefully it can.
What I want to create is a macro or something that would read the values in column A, if it finds a duplicate number it would insert a row under it

i.e.

5
5
6
7
8
It would insert a row between the 5 and 6.
Is this possible?
 

Answer:Solved: Excel Macro - Not sure if it can be done

16 more replies
Relevance 61.09%

Hi Friends,

I'm very much new to Macro in excel. I wanted to Run Macro in the following situation :
I have 2 Excel Book having same header but some data which to be consolidated in a 3rd Book. I recorded macro in 3rd Book the macro function is to " to open Book 1 & Book 2 & consolidate in Book 3" on running the recorded macro.

But after saving the macro and running tha macro again with some new data in Book 1 & Book 2 it gives the following error message "Run-time error '9':
Script out of range

and on debug the following macros are displayed.
---------------------------------------------
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book2.xlsx"
Workbooks.Open Filename:= _
"C:\Documents and Settings\Administrator\Desktop\Book1.xlsx"
Windows("Report.xlsx").Activate

Selection.Consolidate Sources:=Array( _
"'C:\Documents and Settings\Administrator\Desktop\[Book1.xlsx]Sheet1'!R1:R1048576" _
, _
"'C:\Documents and Settings\Administrator\Desktop\[Book2.xlsx]Sheet1'!R1:R1048576" _
), Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
End Sub

--------------------

can anyone help. I'll be very much grateful to you.

I'm using excel 2007 but I dont think it has got to do something with macro!

thank you in advance.

Hemen
&nbs... Read more

Answer:Solved: Help in macro (Excel)

This message usually occurs when the code is trying to call a workbook or worksheet that is not available. Your code opens two workbooks from your desktop (Book2.xlsx, Book1.xlsx) and then is trying to activate a workbook called Report.xlsx. If this workbook is not already open then you will get the error. You need to add some code to open the workbook prior to activating it.

Regards,
Rollin
 

2 more replies
Relevance 61.09%

Hi,

I have following data with me and this data extends till 1000 rows.

IDSYSTEMASSOCIATED_ABC_PROCESSASSOCIATED_XYZ_PROCESS9ABC Stage IIIABC.101-1XYZ.103-459ABC Stage IIABC.101-2XYZ.103-459ABC Stage IABC.101-3XYZ.103-459XYZ Stage IABC.101-4XYZ.103-459ABC Stage IABC.102-2XYZ.103-469ABC Stage IABC.103-2XYZ.103-479XYZ Stage IABC.103-4XYZ.103-4810ABC Stage IABC.104-1XYZ.105-4-310ABC Stage IIIABC.104-2XYZ.105-4-410ABC Stage IIIABC.104-6XYZ.105-4-510XYZ Stage IIABC.105-1-1XYZ.105-4-610ABC Stage IVABC.106-1XYZ.107-3

I am looking for a macro which will give me results as mentioned below.

IDABC / XYZ METRICABC XYZ STAGES9ABC.101-1
ABC.101-2
ABC.101-3
ABC.101-4
ABC.102-2
ABC.103-2
ABC.103-4
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-45
XYZ.103-46
XYZ.103-47
XYZ.103-48
ABC Stage I
ABC Stage II
ABC Stage III
ZYZ Stage I
10ABC Stage I
ABC Stage III
XYZ Stage II
ABC Stage IVABC.104-1
ABC.104-2
ABC.104-6
ABC.105-1-1
ABC.106-1
XYZ.105-4-3
XYZ.105-4-4
XYZ.105-4-5
XYZ.105-4-6
XYZ.107-3



In Column 'ABC / XYZ METRIC' the values from coulmns 'ASSOCIATED_ABC_PROCESS' and 'ASSOCIATED_XYZ_PROCESS' are combined for the respective ID.

Thanks in advance,

Regards,
Akki
 

Answer:Solved: Excel Macro Help

I don't know how the table are disorganized after submmiting the querry. Submmiting new query with attachment.

Regards,
Akki
 

1 more replies
Relevance 61.09%

Hello
I'm using Excel 2003 (on Win XP). I currently have an excel doc which I use for "form filling". There is a lot of questions, 40 or so on Tab1 and another 40 or so on Tab2. Both use "data, validation, list" for the questions (to sub sub level). Once the form is filled in it is saved as an excel file. Then I fill in the next excel doc, roughly about 50 excel docs are created for each job. I know I should probably use a database but I've never used one before.
What I'm looking for is a macro at the end of the form which I could click and it would collect data from tab1, cell A1, A2, B3, C7 and MOVE it into tab3 to a position A2 – all in a single row (i.e. B2, C2 etc). I can then amend the macro and use it for tab2, so the data is saved in tab4.
Here is the tricky part.
The first reference in cell A1 contains the “Question No” referenced as Q1, Q2, etc as a list box. Sometimes I will need to go back to a previous “question” and amend details but most of the time it’s used for printing purposes at the end of the day. Hence I will need a second macro which when clicked on will ask for “Question Number” and once I input it in, should retrieve the information from Tab2 and insert all the data back into the original cells. I suspect this is impossible as it would then paste the raw data over my “list boxes” making them unusable.
But maybe by having another tab, say “amendments” with a macro button on it, I could select the “questio... Read more

Answer:Solved: Need a Macro for Excel

16 more replies
Relevance 61.09%

hi all i've never written a macro before and have been searching online for how to do it but am still pretty confused. i have 4 columns of data, the first of which has a number 1-8 or is blank. if its blank, i want my macro to fill in a number 1-8 based off the next 3 columns. below is how i've thought about how i want my macro to look, but i don't know how to translate this (b, c, d, and e are the columns). any help would be MUCH appreciated. thanks,
If b = "" Then
{If c > 0 Then

(If d > 0 Then

If e > 0 Then b = "1"

If e < 0 Then b = "5")

(If d < 0 Then

If e > 0 Then b = "2"

If e < 0 Then b = "6")}
{If c < 0 Then

(If d > 0 Then

If e > 0 Then b = "3"

If e < 0 Then b = "7")

(If d < 0 Then

If e > 0 Then b = "4"

If e < 0 Then b = "8")}
Else: b = b
 

Answer:Solved: excel macro help please

6 more replies
Relevance 61.09%

what i am trying to do is to add a macro that will take information from cells 'A1' and 'B1' and use them to create a hyperlink in cell 'C1'. for instance, 'A1' is the address, 'B1' is what i'd like to appear in the cell, and 'C1' is where i would like the finished product to show. i have a little over a thousand of these that i need to get through, any ideas?

Answer:macro to use cell info to make hyperlink

With this in A1, http://www.computing.net/forum/office/1.htmland this in B1:Office Forumand this in C1:=HYPERLINK(A1,B1)I created a link that displays Office Forum which, when clicked, brings the user to this forum.

7 more replies
Relevance 61.09%

Hello, I discovered this forum when searching for a way to create a search macro to copy rows of data based on values in certain columns. The post resolved by bomb#21 has given me code that does 90% of what I need:
http://forums.techguy.org/business-applications/559825-solved-excel-search-macro-needed.html
Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = UCase(Range("E5"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -1).Resize(, 3).Copy Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

As an absolute newbie to macros (I've been using them for 3 days, and it took me an hour to figure out why I was getting errors when changing from column B to column A in the above code - for any other newbies it is the Cell.Offset (, -1) part of the code), I was wondering if it is possible to have this code in a Sheet2, to return data from Sheet1 (I had assumed it would be as easy as changing the code to <For Each Cell In Range("Sheet1!B2", Range("Sheet1B" & Rows.Count).End(xlUp))>) and whether it would be possible to have a search popup box instead of just entering text into a cell (E5 in the above code).

Apologies, I know that this is probably a very basic question, that I could learn with some excel training. Unfortunately this is a smalli... Read more

Answer:Solved: Excel search macro almost solved by an earlier bomb#21 post

9 more replies
Relevance 60.68%

Well...I've been reading some of the posts regarding Excel, and it look like Zach is the man!...the expert! Not to say anything less about anyone else.

Anyhow, I'm getting better at my formulas and macors, but I need some help. I need a formula that calculates the anniversary of employee's start date. I would then like the conditional formatting to change the colour to yellow if it's 11 months after their start date...then red if it's on or after their anniversary date.

But wait...there's more...

I would also like Excel to automatically send a reminder email to me when the first condition (yellow) is met.

I've attached the worksheet (not very complex).

Any help would be appreciated.

Thanks!

TBaker14

 

Answer:Solved: Excel Formula and Macro help

7 more replies
Relevance 60.68%

In attempting to record a macro I accidentally created a couple macros for which I have no use. How can I delete them?
Tools / Macro brings up a list of macros, but among the action choices offered, the Delete button is grayed out.
 

Answer:Solved: How delete an Excel macro?

10 more replies
Relevance 60.68%

I am trying to get make a macro/script to hide cells based on what cells on another sheet are filled.

If A1 is filled in sheet 1 then I want 2 columns to show on sheet 2
If A2 is filled in sheet 1 then I want 4 columns to show on sheet 2
Etc.

I currently have macros to hide the appropriate columns and have them assigned to buttons. I want to eliminate the buttons and have sheet 2 automaticly format based on which cells on sheet 1 are filled.

Any suggestions? I am sure I will hvae to use VB. I think I can get it to run a macro if a cell is filled but how do I get it to run based off of the highest cell thats filled. If A5 is filled then use macro for 10 columns ignoring cells A1-A4.

Hope this makes sense.
 

Answer:Solved: Excel, VB, IF Statement to Run Macro

11 more replies
Relevance 60.68%

Hi all,
I'm having a few problems with a macro that sorts data in ascending order in a number of different worksheets. I've created ranges in teh worksheets. There are a number of different ranges but I've attached an example of these (ranges "pm" and "rj"). The macor works on the first range but not on the second when it has to switch worksheets - any ideas!!

Private Sub CommandButton1_Click()
Application.Goto Reference:="pm"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Goto Reference:="rj"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
 

Answer:Solved: Excel - Sort Macro

7 more replies
Relevance 60.68%

Hi everyone,
does anyone know how can I accomplish the following:
I would like to pop a message when I enter a qty. this field will add a amount time the qty then the result will be added for a total of rows. If the result is greater a 100 then I need to popup a message to warm the user.
I know data validation do something like that but only when you are in that cell.
If anyone have any idea of how to do this please let me know

Thanks,
AMD2800
 

Answer:Solved: Excel question Macro or VB ?

10 more replies
Relevance 60.68%

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?
 

Answer:Solved: VBA Macro - Excel 2007

16 more replies
Relevance 60.68%

I am having a problem with my macro code.
First i will describe what i am doing then i will post my code for help.

I have created an open items list for work. I have created 2 identical headers, each in a different worksheet. One of the columns in both work sheets, F to be specific, is a status field. There are 3 to chose from pending, waiting on approval and completed. I have created a button at the top of the header in the open items worksheet so the when it is pushed it runs the macro and moves all of the items that have a status of complete to worksheet 2, the closed action items.
Item # Type Item Description Priority Due Date Status Owner Support Team Compl Date Updates

This is the header that i have in both worksheets.

Here is the code that i have written to move the completed actions over to the closed worksheet.

Sub ClosedItems()
Dim wsOpen As Worksheet, wsClosed As Worksheet
Dim rFilter As Range
Dim lRow As Long

Set wsOpen = Sheets("Open Action Items")
Set wsClosed = Sheets("Closed Items")

For lRow = 6 To wsOpen.Cells(Rows.Count, 1).End(xlUp).Row Step 1

If wsOpen.Cells(lRow, 5) = "90-Completed" Or wsOpen.Cells(lRow, 5) = "99-Cancelled" Then
wsOpen.Range("A" & lRow).EntireRow.Copy
wsClosed.Range("A" & wsClosed.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial
wsOpen.Range("A" & lRow).EntireRow.Delete
lRow = lRow - 1
End If
Next lRow

End Sub
PLEASE HELP!!!!!!!!
 

Answer:Solved: Excel worksheet macro

16 more replies
Relevance 60.68%

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
 

Answer:Solved: Excel 2007 Macro help

8 more replies
Relevance 60.68%

Hi,

I'm a very new VBA user and have spent the better part of 2 day on this great website looking for a similar problem i've run into.

My problem is that i want information from "test" workbook~"results" worksheet to automatically update the "Tech Data" worksheet as well as export an update to the "Master" workbook.

FYI, two separate people input data: iron&manganese and Corrosion&scale inhibitor residuals. This data is exported to the 'results' worksheet and the same simple formula can be used to export to "tech data" worksheet. I am having a hard time getting the 'tech data' worksheet to export to the master workbook. I'm hoping someone can help me ensure the master file compiles all informaiton being exported to it. Note that the master file has several other headings as i was hoping to expand on the starter macro.

I created the tech data worksheet so data placement matched the master workbook format.
 

Answer:Solved: Excel-macro code help

15 more replies
Relevance 60.68%

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.
 

Answer:Solved: Excel 2003 Macro

16 more replies
Relevance 60.68%

I have a userform that contains a textbox for user entry. What is the best way to determine if the user has just hit the spacebar a few times to enter a blank data in the textbox?
 

Answer:Solved: Excel Macro -> UserForm

I was able to figure it out.

I used the following:
Code:

Len(Trim(Me.Textbox.Value)) = 0
 

2 more replies
Relevance 60.68%

I am trying to come up with a macro that once executed will go to a defined name, insert a row, copy what is in row one and then paste the copy in the row that was just inserted.
 

Answer:Solved: excel macro copy

7 more replies
Relevance 60.68%

I am attempting to write a macro that will do the following:
If A2=A1 set B2=B1
At first glance I know what you are thinking; just type that in as a formula dummy. Well, I would like it to run down an entire column of 6000+ entries such that also:
If A3=A2 set B3=B2
If A4=A3 set B4=B3
If A5=A4 set B5=B4
Etc.

I have a macro that I recorded and it gets the job done but
A) It is really sloppy
B) It does not take user selection into consideration such that
If I were to select Column E I want
If A2=A1 set E2=E1
If A3=A2 set B3=B2
If A4=A3 set B4=B3
If A5=A4 set B5=B4
Etc.

Any input will be sooo much appreciated. Here is the code I have right now that has limited functionality:
I was attempting to copy column D on this run.

Sub Macro1()
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("D6").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-3]=R[-1]C[-3],RC[-1]=""""),R[-1]C,RC[-1])"
Range("D6").Select
Selection.AutoFill Destination:=Range("D6:6287"), Type:=xlFillDefault
Range("D6:6287").Select
ActiveWindow.ScrollRow = 6251
ActiveWindow.ScrollRow = 6242
ActiveWindow.ScrollRow = 6233
ActiveWindow.ScrollRow = 6224
ActiveWindow.ScrollRow = 6206
ActiveWindow.ScrollRow = 6198
ActiveWindow.ScrollRow = 6180
ActiveWindow.ScrollRow = 6126
ActiveWindow.ScrollRow = 6055
ActiveWindow.ScrollRow = 5922
ActiveWindow.ScrollRow = 5761
ActiveWindow.ScrollRow = ... Read more

Answer:Solved: Need help writing an Excel VBA macro please!

PS I meant

I have a macro that I recorded and it gets the job done but
A) It is really sloppy
B) It does not take user selection into consideration such that
If I were to select Column E I want
If A2=A1 set E2=E1
If A3=A2 set E3=E2
If A4=A3 set E4=E3
If A5=A4 set E5=E4
Etc.
 

3 more replies
Relevance 60.68%

I want to write a macro that points a cell to the next cell of a row. Ie, the cell currently is set to "=AA3" and I want the macro to take the reference to cell AA4, and then AA5, and then AA6, etc. each time the macro is initiated.

Ideas? I am great with formulas but bad with macros! Is this one I need to write or I can record the macro?

Thanks for suggestions!
 

Answer:Solved: Macro Question for Excel

I think an easy way is to "hide" an incriment value somewhere outside your data table - possibly on a different sheet.
Lets say this value is in cell ZZ99, and that your list of data that it needs to go through is in line 2.
The formula in your resulting cell should now be: "=OFFSET(A2;0;ZZ99)"
Now you start the value in ZZ99 off at 0, and your macro would simply incriment this value. The formula will return the value A2 when ZZ99 is 0, then when ZZ99 becomes 1 the formula will return the value of B2, etc.
 

1 more replies
Relevance 60.68%

I've read these forums for a while and never posted but this problem is really kicking my butt.

I've been using the following macro to insert rows in my spreadsheets for a while now.
Code:

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set wks = Worksheets("Duration Report")
With wks
FirstRow = 2 'headers in 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then
'do nothing
Else
.Rows(iRow).Resize(1).Insert
'insert blank row
End If
Next iRow
End With

My problem now is I'm trying to get it to insert a second row with the heading data in it below the blank row. The heading data is the same every time on the sheet. Any help would be greatly appreciated it, I've been fighting with this for hours now. I'm using Excel 2002 if it matters at all.
 

Answer:Solved: Using macro to insert row in excel, need help.

16 more replies
Relevance 60.68%

Hi there,

Could anyone help me with a formula or macro that will read the data in column A and then display a value in column C in the attached .xls?

EZ-Links would display 5
Hutton would display 3

Many thanks!
 

Answer:Solved: Excel Formula or Macro help please

Place the following formula in C11 and copy down for all rows. Will there ever be any other values that you need to check for?

=IF(A11="EZ-Links",5,IF(A11="Hutton",3,""))

Regards,
Rollin
 

3 more replies
Relevance 60.68%

I have a terrible report that i cannot modify. It can be any where from 8-20 pages long, but i only want the first page of information

I have built a macro to import the report into excel. After my macro is done its thing it finds the first line of data that i would not need selects the cell.

Now what i am looking for is a macro to either just select the information from A1 to that active cell and copy it into a new worksheet (remember the range of data could be anywhere from 20 to 40 lines or even more, so i cannot have a cell reference to the cell that was found).
Or a macro to select everything from that selected cell to the lst line and deleting everything just leaving me the top lines that i would need)

Is this clear?
Is this possible?
 

Answer:Solved: Excel 2002 Macro

6 more replies
Relevance 60.68%

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
 

Answer:Solved: Excel 2003 macro help

8 more replies
Relevance 60.68%

Hi,
I am in some major need of help.

I'd like a macro -- actually a plug in/add-in if that can be set up -- that finds the maximum DPI print quality setting of a worksheet within an Excel workbook (this is the setting under File | Page Setup | Page tab | Print Quality), and will take that max value and make it the default DPI setting for all of the pages in that same workbook.

At the same time, I'd like to have a custom footer placed on all of the pages of the workbook that reads something along the lines of "My footer text - Page x of y".

If possible, I'd like this plug-in to run upon saving of the Excel file.

Thanks very much!
 

Answer:Solved: Excel Macro help needed, please!

16 more replies
Relevance 60.68%

This is my first message to this forums so Hi everyone!

to the point. Im trying to make a macro that will help me submit some information to a database. The information will be put in excel table and then exported to a CRM. So I made a userform with all the textboxes and I need to make each textbox user submited text to be pasted to a cell in the table. Do you have any idea how to make this?

So far Ive written this little code but im stuck:
Code:
Private Sub CommandButton1_Click()
Sheets("Hoja1").Select
Range("B1").Select
TextBox1.Text = Range("B1")
End Sub
Thank you!
 

More replies
Relevance 60.68%

I have:
A worksheet with 11 columns, about 400 rows, with mostly text data.

I want:
To create a "Search" button on the spreadsheet that opens a form with a text box to enter search criteria, with a "Search" button and a "Cancel" button. When the user enters criteria and clicks "Search," rows with cells matching the data return in a different worksheet.

The problems:
Say the user wants to search for "Texas." Texas could be in several cells within the same rows - B7=Texas, C7=Texas Study, D7=Go Texas, E7=Texas, Texas, Texas, etc. So I don't want it to return the same row 6 times since it has 6 different instances of "Texas."

As shown above, the specific keyword they're looking for may be buried in other text within the same cell, so the search function needs to sort through and find all the instances.

I'm a beginner to this macro stuff, but I have the basics down. I just need help with a code for the search function. Any help would be appreciated!
 

Answer:Solved: Excel Search Macro

12 more replies
Relevance 60.68%

I currently have a macro which I use to assign names in coloumn W. Depending on the total number of rows I equally divide it. This is a sample macro

Sub Macro1()
'
' Macro1 Macro
'
Range("W1").Select
ActiveCell.FormulaR1C1 = "Name1"
Range("W1").Select
Selection.Copy
Range("W2:W561").Select
ActiveSheet.Paste
Range("W562").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name2"
Range("W562").Select
Selection.Copy
Range("W563:W653").Select
ActiveSheet.Paste
Range("W654").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Name3"
Range("W654").Select
Selection.Copy
Range("W655:W675").Select
ActiveSheet.Paste
Range("W676").Select
End Sub

So lets say today I have 22543 rows of data. Now I will divide it by 16 (because I have to assign it to 16 people) 1409. I want to be able to assign 1409 rows to each name total 16 (copy pasting or however) using a macro?

How can this be done without modifying the macro every day, I usually go in and change the numbers under Range.

Thanks
 

Answer:Solved: Excel Variable macro help

16 more replies
Relevance 60.68%

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
 

Answer:Solved: Excel 2007 Macro Help

13 more replies
Relevance 60.68%

This post is related to an earlier one, which has now been solved and closed:http://forums.techguy.org/business-applications/955009-excel-2007-lookup-formula.html. The macro outlined in this thread works fine as long as there are 4 values in each row the macro references. If there are less than 4 values the formula errors when trying to return the

Code:
WorksheetFunction.Large
value. Could somebody please edit the macro so that if it causes an error due to too few values, that it will then break out of the code instead of keep running? The ideal situation would be that it outputs any values it finds, and skips past and carries on if the function errors.
 

Answer:Solved: Excel Macro Error

No problem - all sorted now A bit of out of the box thinking was all that was needed!
 

2 more replies
Relevance 60.68%

Could someone explain the code below?
Code:

Cells(8, Weekday(Date) + 2).Select

I understand that the code is looking at the 8th row, but I am a little confused on the ColumnIndex.
 

Answer:Solved: Excel Macro Explanation

8 more replies
Relevance 60.68%

Hi,

I urgently need some help creating a macro to help me print hundreds of excel spreadsheets. The ranges containing data varies on each one.

My normal process would be to manually select a range that covers all cells containing data (variable), set that as the print area, then open the page setup dialogue and change the settings to what ever works.

Could someone help with this please?
 

Answer:Solved: VB macro for Excel printing

I think I may have found the answer: I just found out about the Ctrl+Home and Ctrl+End shortcuts and recorded them.
 

2 more replies
Relevance 60.68%

I'm sure this is a simple task for you boffins out there.

In Col A, I have a list consisting of a series of number ie
24,56,23,98,34,77,01
65,98,12,17,36,99,31
etc
Unfortunately, these lists of numbers are taken from several different sources and some contain spaces ie
24,56,23,98,34, 77,01
65,98, 12,17,36, 99,31
etc
I just need a macro, which I can then assign to a toolbar button, which eliminates these spaces.
All the list is contained to Col A and the number of rows varies from, say, 18 rows up to possibly 25.000 rows.

I'd greatly appreciate any help with this please.
 

Answer:Solved: Excel - Need simple macro

6 more replies
Relevance 60.68%

I need some assistance creating a macro to automate a process by clicking on a button. I would like to have multiple functions performed when a single button is clicked. They are:

1. Save the active file
2. Open a new mail message (and attach the active file if possible)
3. Fill in the To: with "[email protected]"
4. Fill in the CC: with "[email protected]"
5. Fill in the subject with "Performance Tracker"

Is this possible? I have no idea where to start other than I know it will require VBA. Can someone help?
 

Answer:Solved: Excel Macro for Button

8 more replies