I need to code an option to overcome the fact that 1 workstation sees all drive letters differently.
I have many macro functions that may refer to "P:My documentsTrucks 2008filename"
However this workstation sees that drive as "T:Trucks 2008filename" and the macro will either not function or cannot find the location to save a copy of action in question.
Can I code an option that will "If filepath not found" ie "P:" go to "T:" etc
I want to list file names from a directory and sub directories and hyperlink them. I have been able to list the file names onto an excel sheet, but I cannot get it to hyperlink to the file.
'Force the explicit delcaration of variables
Option Explicit
Sub ListFiles()
[Code]....
In the code below you'll notice the SubAddress as "Sheet4!B1".
Range("B1").Select
Selection.Hyperlinks(1).SubAddress = "Sheet4!B1"
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
In Range B1 (Sheet1 in this case, just not on sheet4), I have Validation set up to allow the user to click on the required name of a client selected from a list.
I want this constantly changing value of B1, once selected, to become the value currently shown as Sheet4 of the SubAddress above.
e.g., if B1 is Harry, the SubAddress should read:
Selection.Hyperlinks(1).SubAddress = "Harry!B1"
There will be a sheet named Harry and this following of the link must go to B1 on the sheet named Harry (or whatever sheet's name appears in Sheet1, Cell B1).
In my excel file, I have a cell with a hyperlink which points to an intranet portal. When I copy the hyperlink and paste it in the browser's address bar, the site opens. But when I directly try to open from the excel, by clicking on the cell, it redirects to a wrong URL and eventually does open the site.
View 1 Replies View RelatedI currently have the following ...
View 8 Replies View RelatedI want to write a macro that would go to a certain drive and search for a file?
View 9 Replies View RelatedI have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False
[Code].....
I would like to know how is it possible to only run a macro with IF command if a path is found in a computer. My desktop user is example, jc855, and my coworker is jc886.
I would like to start the IF command such as, if C:Usersjc855 & C:Usersjc886 found, then keep going, otherwise, message box : "You are not authorize to run this macro".
By having this path, people will be able to download this macro but the access is only granted if their user is added thru the VBA. (which only me can access to edit it).
I have a worksheet with several option buttons and until yesterday, I had no problem selecting them. But today, every time, I select a radio button, an error shows up stating "Cannot run Macro "Option Button_click. Macro is not available in this workbook or is disabled". When the user selects a radio button, text appears in Col B depending on what button was selected (using VLOOKUP). That stuff is still working fine. It's just as soon as I click a radio button, the error shows up.
Based on the radio button selected, user can add in information to the columns adjacent to it, and then click a button to store that information in some rows below. The code for that button is stored in the sheet module. I have tried enabling macros and disabling macros and no change has appeared. What I have noticed though is that yesterday, when i opened the workbook, a yellow bar appeared asking if I wanted to "Enable Content". Today, that bar didn't appear.
I'm trying to write some VBA which will allow me to find certain instances of data across a number of different sheets and copy this into another sheet.
However if the sheet doesn't contain the data I'm searching for an error pops up and the macro dies, is there a way to search where if the macro doesn't find anything it skips to the next line?
I have a code that copies rows to another sheet if a value in D:D is found. BUT I need to add a "create sheet" IF the value exist.
So if VBA finds the value "TWO" in Column D, then insert a sheet at the end named "Two"
Then the code below will copy all the values of "TWO" over to sheet "TWO"
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Range("D" & i).Value = "9" Then Rows(i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
In essence, the VBA should:
Search for "ONE" in D:D, not find anything and move on.
Search for "TWO" in D:D, will find it, Create a New Sheet named TWO, then the above Code to copy all matching rows into sheet TWO.
Search for "THREE" D:D, not find anything and move on....
There will be roughly 12 sheets created on average, so if I create all sheets beforehand.
I would like a macro to order the columns as per the hardcoded list. If the column is not found than add the column at the position as per the hardcoded list. Below macro does the first part of re-ordering however if the column is not found how do I add an empty column:
Sub Reorder_Columns()
Dim ColOrder As Variant, i As Integer, Found As Range
ColOrder = Array("LogicalFileName", "LogicalFilePath", "UploadedDate", "UploadedBy",
[Code].....
I’m working in Excel ’03 with a large table. I’m hoping to run a macro against a cell with value ‘X’ whenever that value is found within a particular column. I’d like this to be relative and not tied to a particular column so I can tweak it as needed down the road and run it against different columns.
In the example below, lets say I’m working with column B. I’d like something that would go through every cell in the column and run a macro when it comes across a particular value. Let’s say this value is x.
__A B C D E F
1 a x a a a x
2 b a b b b a
3 c x c c c c
4 f f f x f f
5 s s s a s s
6 g x g g g x
7 a a a a a a
8 s s s s s s
So, in my example, the macro would be run on B1, B3, and B6. And, if I wanted to go back in later, I could run this on column D and the macro would run on cell D4.
I get a strange error message when using one of my excel sheets containing code.
The workbook is quite big, but the essence of the issue is the following. I use a macro to refresh about 30 pivottables. The code is very simple and comes from the macro recorder. (Part of) The code is shown below:
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable3").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable8").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable12").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable13").PivotCache.Refresh
.....
for some strange reason, this code does not work anymore. It used to work fine, but when I run it now I get an error message saying "macro 'mDOM.XlinkOn' cannot be found". Does anyone know what this means?? I tried setting up a new pivot table in the same workbook and record a new macro to refresh. While the recording part works fine, running the macro afterwards gives the same error message.
Strangely enough, when I start a new workbook, add a pivottable and record the same kind of macro, it does work! Therefore, for some reason my workbook has been corrupted in some way. Does anybody know how to fix this? Rebuilding the workbook is not really my preferred option as there are about 20 worksheets and 15 pages of code in there.
You can find attached the workbook I am working on.
In my workbook I have four sheets. Only two sheets are relevant in this case: "Sheet1" and "List". On "Sheet1" there in column A there is big range of codenumbers (highlighted with red).
On the "List" worksheet I have a smaller list (highlighted with green).
I have a code also in module1 but the code is not working.
I would like my macro to do the following. Check the code number from the "List" worksheet (green) and search for it in "Sheet1" column A (red). If this codnumber can be found in column A then leave the number on the green list. If it cannot be found in column A then, delete it from the green list. If you open the file, you can see on the "List" worksheet that there are three code numbers in the green range. The upper and the bottom code number can be found on the "Sheet1" column A (red) range, but the middle number cannot be found. So if the macro would work correctly then it would delete the middle number, and leave the other two untouched!
I want to a macro to start of with a Find method Eg Account..something like this
Cells.Find(What:="Account").Activate
Account could be in any row or may not be in the document.
My problem is when it is not in the workbook my macro will come up error. how to recode my macro to fix this.
Dim Finder as string
Set Finder = Cells.Find(What:="Account",...............
'Problem here
'
If Finder is not found then skip till next part of macro.
If it is then just select the Cell and I can work of that.
Also Can I do a Find formula to Find Either 'Account' Or 'Debt' Or Asset'?? Just want to know save me copy and pasting it down to change a name.
I have created a few macros and once I was happy that they worked independently I joined them to run as one macro in several steps. I have just discovered a bug ...
The macro filters on a column and copies and pastes that range into another sheet.
HOwever, sometimes (as I have just discovered) when it filters, there will be no data against the value it is looking for.
I need to say, if no value, move to next step.
I.e. here's my code. It's looking for ASIA and it will copy and paste all rows with Asia to another page. It then does the same for EMEA. Thing is, in my example just now, there was no EMEA data and some times there may be no ASIA data so I need to build in some rules that if it does not find either of these it continues to finish macro or pops up and says 'NO ASIA found, CLick to continue' or something?
Selection.AutoFilter
ActiveSheet.Range("$A$12:AA" & lngLast).AutoFilter Field:=27, Criteria1:="ASIA"
Range("A13:Z" & lngLast).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("ASIA DETAIL ").Select
Range("A19").Select
[code]...
I have a simple macro that copies data from worksheet to another. I only want this macro to be run once per day by the user. One of the cells that gets copied is the value found in the formula "=today()". My thought is that the macro could look for the existance of the current date in the pasted data (meaning the macro has already been run once today), and if the date = today, then a message pops up warning the user that the macro has already been run once today, and ask if they're sure they want to continue.
Any easy way to accomplish what I'm attempting to do? The current date gets pasted into a worksheet name "PriorDay" in cell C5.
I've got an interactive chart on a sheet where users select a chart from a combo box control and a picture link to the chart is displayed. The actual charts are on a hidden sheet. I have to activate the charts first for it to work so I put a button the sheet and recorded a macro where I unhide the hidden sheet, select each chart as I scroll down the screen by clicking on it then hide the sheet again and return to the interactive chart.
My problem is that I'm getting an error when the macro tries to select the first chart which is called "Chart 6":
Run-time error '-2147024809 (80070057)':
The item with the specified name wasn't found
I recorded the macro so I'm not sure why the macro isn't working now.
Code:
Sub ResetCharts()
'
' ResetCharts Macro
' Reset all charts
'
'
Sheets("Budget v Actual Graphs").Select
Sheets("Graph BG").Visible = True
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 35").Activate
[Code] ............
I have the following macro which I've pieced together which works great at clearing the cell as long as the text entered by the user is found in the specified column.
Unfortunately I'm a big rookie with this stuff and I can't figure out how to handle the process/error when an entered value is not found. When ChosenRow returns with a 0 I get a Run time error '91', Object Variable or With Block variable not set.
The code...
Private Sub CommandButton1_Click()
oldSheet$ = ActiveSheet.Name
Dim ChosenRow As Long
Dim loc As Variant
Application. ScreenUpdating = False
With UserForm4
loc = .TextBox1.Text
End With
With ActiveWorkbook
Sheets("Reference").Select
End With
I have written a macro to convert data from a report exported from Salesforce.com to a format that is acceptable for upload to our website. One of the necessary conversions is to convert values from a Boolean value to a Y/N value. I wrote a macro with a series of search-replace commands such as this:
Code:
'replace 0s and 1s in "Direct Billing" column with appropriate data
Columns("R:R").Select
Selection.Replace What:="0", Replacement:="N", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
This code generates a warning when no "0" values are found:
"Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet."
Is it possible to search a worksheet that may contain different number of rows (from day to day) and for every instance of a word like "HOLD" is found in column "C" to Color that intire Row Red and also Bold the Row as well?
View 9 Replies View RelatedI am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
I am trying to make a macro that will query a column J4 for a date, if the date is found say "Wednesday, July 4, 2012" (J8) get the text from Column K8 "Independence Day" and insert the text in Cell (H1).
The Macro Prints sign in sheets with the date in H2, it only prints Monday through friday. Then skips the weekend and the next date will start the following Monday.
The Federal Holiday schedule is in column range J (Date) and K (Holiday)
This is where I am stuck ....
EmployeeTimeSheet_2012.xlsm
I am busy writing projects and the macros that I write reference external files (no surprise there). Normally, all referenced files are on the company's main G: drive but recently the company have started the process of re-appraising all access authorities for the folders in the G: drive. This has necessitated that they create a new drive (called J:) and folders and users are being migrated over there as they have been cleaned and approved.
All of this means that some of my users reference J: and some G: and my macros are intermittently falling over because of it. For each user, all the files that they will need will be on either the J: or the G: drive, but not both.
All of the above leads me to ask the following question... How can I build into the macros a "Check what drive you're on and use that letter" line of code
I'm on excel 2010 and I have a small group excel files I open everyday. Most of the files are static in name and location. I've got a macro created to open those files, which works fine with workbooks.open and the file path.
There are two report files I want to incorporate into my macro of workbooks to open. The files are created weekly and the files names have the following format: "Report Name (YYYY-MM-DD).xlsm". I don't want to use the file's last modified date because older files may get edited after the more recent ones are created. The files are also not always created on the same day, so the solution needs to be flexible enough to not refer to a specific day of the week or anything.
Macro open an excel file based on the latest date found in filename.
Currently I am using this ......
View 9 Replies View RelatedI use the Hyperlink to open PDF files that are on the CD drive from my spreadsheet. I am on Windows XP and the CD drive is the D drive. In Vista the CD drive is the E drive.
When I take the spreadsheet to a Vista machine the link fails as there is no D drive.
Is there any to code the path of the Hyperlink so it will go to th default CD drive for that computer.
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.
Now the problem I have is that we all have the network drive mapped to a different letter.
Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.
I have a an excel workbook that is distributed on a flash drive. Currently a Save As Macro copies "A1" as the file name and then opens a screen to allows the user to save that filename to a location that the user selects. What I would like to have happen is for a command button to start a macro that would automatically save the file back to the flash drive that the master workbook is located.
View 3 Replies View Related