This is probably a simple question, but I couldn't find the answer by searching...
I have a macro that I wish to run which calls one of two different macro's depending on if the user has Excel version 2007 or if they have an earlier version.
This is to overcome 'Save as PDF', which only works in Excel 2007.
I need to find out which version of excel i am using in macro.I mean whether it is 2003 or 2007.Because i need to put 2 diff codes according to the version of excel.Can any one please provide me the macro code to findout the version of Excel.
I have a file that I save with a new version number each time I make major changes. The file name currently is: "Telephony Equipment Inventory v26 (Summary).xlsm". The "26" is the variable number. give me the vba code to ensure I open the file with the highest version number?
View 6 Replies View RelatedI am trying to reference a cell that displays "#NAME?" due to an unrecognized formula. However, I keep getting an "Type Mismatch" error. Is there a way to reference a file that displays "#NAME?"
Example:
If Sheets("sheet1").Range("B6") = "#name?" Then
Sheets("sheet1").Range("B3").ClearContents
End If
Is there a way to find out which version of excel somebody is using within VBA?
View 2 Replies View RelatedI am writing an excel 'tool' that must be compatabile with both Excel 2003 and Excel 2007. I originally wrote some functions using Application.Filesearch, and had to re-write that code due to the lack of Application.Filesearch in Excel 2007 (I used the chdir method). The chdir method is much slower the way I am using it, therefore, I would like to still use the Application.Filesearch method for those using Excel 2003. Now, I have the user select their excel version and run the correct macro. Is there a way to detect the Excel Version automatically and the call the correct macro?
View 2 Replies View RelatedIf I want to create an Excel file that other users would use, and want to create a specific version number of the file. Is there a place I can save that information outside of the worksheets?
For example, lets say I create a file that is version 1.0. Can I save that somewhere in "Properties" or somewhere else so that "1.0" can't be modified, and if I come out with a different version say 2.0, I can update it?
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.Application.DisplayAlerts = False
xlApp.Workbooks.Open ("somepathWebquery.xlsm")
xlApp.Application.Run "'some macro"
The only problem is, the web query will only work properly if it is in Excel 2007 (2003 will parse the table in a way that is not acceptable). However, when I tried to run the script, the stubborn Excel 2003 will show up. I even tried to uninstall the Office Compatibility Pack and the stubborn Windows will only run Excel 2003 and ask me to download the Office Compatibility Pack instead of giving me Excel 2007.
I tried to open it up using Open With...and it did not work, I even tried to regserver and unregserver and that did not work as well. Somehow Windows thought the only Excel I have is Excel 2003.
Is there anyway I could change the VB Script so that it would force Excel 2007 to open?
I have two numbers, they are version numbers, I am having problems with my If then else and elseif statements.
59821 for version 5.9.8.2.1
is more than
5983 for version 5.9.8.3
even though 5.9.8.3 is a greater version number. Do you see where my problem is. How do I compare # by # reading it, checking to see what one is bigger and going from there. so when it gets to the 3 of the 5983 its bigger than 2 of 5982 of the 5.9.8.2.1
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
View 4 Replies View RelatedThe other day, I had posted a macro, which copies one of the sheets from a workbook, on to a blank sheet, and then saves it with a name obtained from the value of cell E7. I required certain modifications to it, before I could implement it.
You will find it here and the code looks like this:
I'm looking for a way to identify the caller procedure and to act according to that.
I tried the CALLER command but it seems not to be the appropriate one. The sample code is as follows:
Sub AAA()
MsgBox "This MsgBox is displayed from within Proc. AAA"
BBB
CCC
MsgBox "This should NOT to be shown if Proc CCC was called from here"
End Sub ............
I need to build an Excel application that allows a user to select and download files from a website. The files change daily. Is there a way in VBA to obtain the current list of files that are available for downloading?
View 4 Replies View RelatedI'm trying to determine if the last character of a cell is a number or not a number. So in a cell I may have a string like:
ZXC123
or
ZXC123A
If the last character is not a number I want the letter returned. So I thought I could use a formula like: =IF(NOT(ISNUMBER(RIGHT(D88,1))), RIGHT(D88,1),""). However for the string ZXC123 it still returns '3' so anyone have any ideas how I can do this?
I have a column of random number in Column A and need a formula to identify if any of the numbers in Col A is a prime number. If it is, then say "Prime" in the cell adjacent to the prime number in Col B. Eg if A12 has a value 17, then cell B12 should say "Prime".
Determine If A Number Is Even Or Odd. i would like to Determine If A Number Is Even Or Odd...?
View 3 Replies View RelatedI have about 50 lincharts that I wont to update with VBA but they have different number of series. Some have two series, some three and others four. Is there a way to count how many series a chart has? Otherwise could I get tips on how to approach this problem.
View 2 Replies View RelatedIs it possible to programmatically determine the page number on which a specific cell will print?
View 14 Replies View RelatedTrying to do a simple workbook here, with hopefully no macros/VB script.
Essentially, I want a cell to display the row number (-1 to take into account the heading) which has the last value contained in it.
The workbook is a list of users that have signed up in 1 month, and is distributed to staff for a particular reason.
The cell I'm trying to create is part of a 'stats' section.
I have a worksheet that contains active rows and hidden, inactive rows. On occasion, I would like to unhide an inactive row. I intend to do this by running a macro which will generate an input box to enter the file number of the row in question. I can restore the row, but what I am unable to find a solution for is converting the file number to the row number to be restored. The file numbers reside in column A, so if I input file number 2300, I need the macro to look through column A, find the cell that contains the value 2300, and return (as a string?) the row number that contains that value. From this I can have that row returned to visible status.
View 9 Replies View RelatedWe have list for entries that is shared among few people. In that list, we manually add number for the entry. So if I input 99 then, next person should add 100 and so on. I would like to automize this, so say If I use this, then no one else can use it in the mean time and then when number is input, it should disallow if same number is input and it should automatically enter the next number.
View 3 Replies View RelatedWhen I click on a commandbutton20 on sheet1 it moves the user from sheet1 to sheet2, hides some columns & formats the height of a row in a range of cells I will refer to as Area X. On sheet 2 I have a target condition that if the user clicks on any part of Area X it move the cursor to cell C8. What I want to accomplish is that if commandbutton20 in clicked, the target condition is suspended. otherwise the target condition works. The code I wrote is as follows. What happens when I press F5 to execute it... a macro window pops up.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a1:x3,e4:x6")) Is Nothing Then
If Worksheets(1).CommandButton20_Click() = True Then
Exit Sub
Else: Range("c8").Select
End If
End If
End Sub
Is it possible to display both the current code line number and the total line count of a macro while it's running?
I was thinking it would be handy for a progress indicator if the ProgressBar max value could be set to the LineCount total of the procedure and each line of code would increment the progressbar as the macro ran.
I have a report of time balances that employees enter. I need to run a macro that looks at each cell and determines if the number is divisible by 4. (We only track vacation and ill time in 4 hour increments) If the number the employee entered is not evenly divisible by 4, I want the cell highlighted.
All of this I have figured out, the loop to look at each row, the IsNumeric function to test if there is a number...
My problem is with the MOD. I figure I should be testing number MOD number, but it is not reliable.
4.1 Mod 4 returns 0
.5 Mod 4 returns 0
I just need it to return zero if the number is evenly divisible by 4.
I considered the RoundUp function, but then it would also round anything above 3 hrs to 4, and that won't work.
I have client sheets that need to be printed at the end of each month. Sometimes I need 1 sheet, sometimes more. I have a macro which pops up a userform in which I can select the client I want to print and the month. I would like to be able to have the macro determine how many pages need to be printed so I get all the pages and do not end up with any blank pages. Is this possible?
View 1 Replies View RelatedIt's been awhile since I've done this...and I tried searching for the solution but to no avail.
I'm trying to edit the contents of a series of numbers by using the Rounding function. When I execute my macro on the next cell, it uses the value of the original cell when the macro was created. Here's a copy of my macro:
Sub RoundTest()
'
' RoundTest Macro
' Macro recorded 7/26/2007 by Aramark Uniform Services
'
' Keyboard Shortcut: Ctrl+l
'
ActiveCell.FormulaR1C1 = "=ROUND(2103.86,0)"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"
Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.
I have two columns of data with a minimum value in the first column and a maximum value in the second column. What I'm trying to get is an indicator in a third column which tells me if a whole number falls at or between the minimum and maximum values. Here is a sample of my data:
A1 B1 C1
0 0.1 TRUE
0.2 0.5 FALSE
0.6 0.8 FALSE
0.9 1.2 TRUE
1.3 1.6 FALSE
1.7 2.0 TRUE
is it possible if I have a directory like the following
[Code].....
with .csv files which are placed in arithmetic order 01,02,03 etc to determine if one .csv file is missing and print an error that this file is missing? I think that if I knew how many files I got I would create an array and check one by one the files.. But in case I have a dynamic directory and the number of files is not the same all the time , is this possible?
if the active cell is formatted as number and I write
x = Cells(Row, Column).Value
and the cell is empty or blank, x will be set to 0.
How can I determine that the cell is blank and not actually 0 ?