I've been reading about usage of worksheet functions in macros, and I guess I'm still not getting it. What I want to do, is use Countif and Indirect worksheet functions in a macro but it always fails. Here is my formula
IF(Worksheetfunction.Countif(Worksheetfunction.INDIRECT("[DaysWorking.xls]'Collect No Stats'!$A:$A"),'2009'!Range("D" & x))>0)
Basically, I want to see if 2 different cells in 2 different workbooks match and then I would use the information to write different info in another cell. X is just a variable in a DO WHILE LOOP. Can these functions be used in a macro? Is there an alternative?
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
transferring data from a worksheet (Passdown Report) to another worksheet (Data Base) located in the same workbook. In the source worksheet (Passdown Report) there are 2 cells (B2 and D2) in which I would like the data to be transferred along with the data from B4 to AQ33. All the cells contain a formula which I want to stay after the information is transferred to the target worksheet (Data Base). This will be a daily transfer to the target worksheet (Data Base), so the macros should also identify the next available open row to transfer the data to.
Is possible to pass names into a worksheet function and whether values from other sheets are able to be passed in, eg i am trying to paste this function into the work sheet to find the left two characters of a certain string. However the string position varies:
I use a few Functions in my VBA code. All these functions, are declared as "Public", and reside in a single module. However, they are called from many different modules during code execution. (i.e. many functions called from many modules - hence the "Public" declaration).
My issue is that in addition to being available to different VBA Modules, these Functions are also available on the worksheet as a UDF (so if a user presses "=" in the formula bar, the auto-complete shows these functions when the first characters match). Is there a way to remove the availability of the function on user worksheet? i.e. to allow a Function to be called from different modules in VBA, but prevent it from being available on the worksheet.
My current work-around is to prefix all Public Function names with letter "j" - as no excel formula seems to begin with it - none of them show up as auto-complete options. Nevertheless, the Functions are still available to the user - which is what I would like to prevent.
I have an excel problem with formula. I’m not sure of the formula I think its sumif but don’t know how to express the criteria . I am trying to create a spreadsheet that will total the data from Colum B, C & D. I have 3 columns with the following data.
Column B Heading Length Column C Heading Width Column D Heading Depth
Column E Heading LM (lineal meters) will have the data only from column B. Column F Heading M2 will equal the data of column B & C (remembering the math is M2 = column B x Column C. Column G Heading M3 will equal the data of Column B, C & D (remembering the math is M3 = column B x Column C x Column D. That’s the easy part but in columns H to J, I have other headings and column K is my Rate.
Column L is my subtotal and this is where my problem lies, because the formula is =SUM(E1:J1)*K1 it has the potential to double up on figures when I use the dropdown formula for Columns E,F, and G all data is carried down with them. I want Column E to show only the value, if column B has a number and Columns C & D are empty. I want Column F to show only the value, if columns B & C have a number and Column D is empty. I want Column G to show on the value, if all columns have a number. If all these conditions are met, then only a value will be displayed in either column E, F, G Remembering that the values returned must be multiplied by the value of each number in the columns.
I have a spreadsheet which has extra worksheets at the end that the user can move around, if they need them.
Then i found out that users were adding there own sheets, so i protected the workbook, but i found out that this stops the user from moving the sheet.
Whats the easiest way probably using code to disable the worksheet functions, ie the ones that appear if you click on the tab (insert,delete,rename,move+copy,select all sheets etc) but still allow the user to move the sheet within the workbook
I have a spreadsheet using Data Validation to offer a list of text values for cells in the range E6 - E100. I have a worksheet change funtion to change the colour of the entire row based on the text chosen from the list.
What I would like to add is an input box that will appear when the value in the cell = "COMPLETE" asking for a completion date and adding the inputted date to the cell in column I in the same row.
Existing Worksheet Change Code;
Private Sub Worksheet_Change(ByVal Target As Range) 'Colour code rows based on order status Dim rng As Range, i As Long Dim cell As Range, Answers As Variant Dim Colors As Variant Colors = Array(24, 15, 38, 44, 42, 20, 36) Answers = Array("CLOSED", "SUSPENDED", _ "COMPLETE - Awaiting Inspection", "COMPLETE", "WORKING", _ "SCHEDULED", "READY") Set rng = Range("E6:E100") rng.EntireRow.Interior.ColorIndex = xlNone For Each cell In rng For i = LBound(Answers) To UBound(Answers) If LCase(cell) = LCase(Answers(i)) Then cell.EntireRow.Interior.ColorIndex = Colors(i) Exit For End If Next i Next cell End Sub
I have a workbook that has many simple functions moving data around to different sheets and processing some if statements on some of the data. The problem that I am running into is all of the data is initially pulled in from a SQL DB, and I believe that the workbook is calculating many of it's if statements, prior to all of the SQL data being loaded, therefore, many of the worksheet formulas are coming up with the wrong result. Is there a way to delay the calculation of all the basic worksheet functions, so that I can ensure that all the data is in the workbook from SQL, before they all fire. I have attempted to use Tools--Options--Calculation--Manual Calculation, but it appears that even though I have things set that way, all of the formulas in the workbook have already pulled their values through.
What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:
VB: Function SHEETOFFSET(offset, Ref) [COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR] Application.Volatile With Application.Caller.Parent SHEETOFFSET = .Parent.Sheets(.Index + offset) _ .Range(Ref.Address).Value End With End Function
This returns a value to a row in my mater sheet and I reference this for the data validation list.
However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.
I have an array with dimensions (5000,30). I want to perform a worksheet function "Percentile" on specific columns within the array. So for instance I may want to know the element falling at the 50th percentile in column 5 of the 30 column array. Is there a way to do this without having to place the array onto a worksheet?
I have question about next worksheet, let say I have VB code that’s Run on sheet1, but Sheet2 to Sheet 7 al so needs after sheet1 finished, same format on at sheets.
I need to protect the attached to stop people deleting the formulas. The problem is that there are hidden macros and when i protect the cells the conventional way they stop working and i lose all the data in the IAS sheet. I have highlighted all the cells in yellow that i want to be able to change once protected.
At this stage I would just like some one to explain how to protect it. Ideally I want to do it normally (unlock the cells individually that I want to remain unprotected, then protect the whole sheet). If its not then presumably its a macro alteration that's needed?? don't do it at this stage as depending on how it will be protected will mean that i need to make a few adjustments.
It's simple enough to delete all macros. I have a form that starts as a landing area for a bunch of data then it gets renamed. I want to delete both of my command buttons from the sheet and remove all of my macros. How am I going to pull this off, how can i save it to the sheet the first time?
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'cut command buttons ActiveSheet.Shapes("CommandButton1").Select Selection.Cut ActiveSheet.Shapes("CommandButton2").Select Selection.Cut
I have a worksheet "OPEN", and would like to apply both:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim i As Long Set rng = Target.Parent.Range("B:B") If Target.Count > 1 Then Exit Sub
[Code] .........
The first removes the rows if the validated column 'B' is equal to "closed", to a second worksheet "CLOSED". The second orders the rows by the same column, followed by column 'A'. Being brand new to macros, I've no clue how to make this happen...
I have a website that I have been posting filtered worksheets of specific formats of items for interested viewers to view. The Looker can click on the Button and Open or Save a Copy of the Worksheet. I hide unused rows and columns to reduce size.
I filter my main worksheet to find the item type that I want to isolate - I copy the Result to another file - Run a Macro to Remove the information that I dont want to show. Hide Unused rows and Columns. Save as my File name and Upload to the Website.
BUT -- In my Excel Workbooks I have Macros - Some in the personal file. Some attached to Specific Files. When the Viewer clicks to see the file -- it offers the Macros ( Enable or Disable ). This Scares off many viewers and they Cancel.
When I delete the macros and save -- it deletes the Macros from MY computer as well.
How can I remove Macros from only the Sheet that I want to post for my website.
Here is the URL of one of the Current Buttons to a posted file if you care to view and see what I mean and am attempting to do.
I've managed to be able to create a macro to make a copy of a worksheet within excel.
However, when that copy is create all the macros are lost. If it is not possible to copy all the macros that are within the "Original Hours" sheet, I at least need a macro that will take people back to the original hours sheet (we have disabled sheet tabs, so need the macros to navigate through the worksheet).
Code used is below
Private Sub Hours_Click() Sheets("Original hours").Copy After:=Sheets("Original hours") ActiveSheet.Name = "Update hours" ActiveSheet.Unprotect "PASSWORD" With ActiveSheet.UsedRange .Value = .Value Sheets("Update hours").Select End With End Sub
Am a newbie in excel macros. pls i need assistance as to how to create a macro that searches a worksheet for a particular word. i wrote something like below but am having problems in it.
Private Sub CommandButton1_Click()
Dim word As TextFrame
If word Is Not Empty Then For word = "aaaaaaaaa" To "zzzzzzzzzz" ThisWorkbook.Worksheet.Find Next word End If
I have a formula which i need to paste into a worksheet, doing this is tedious and boring to say the least, problem is i am not sure how to get the macro to skip blank lines and only insert the formula where there are prices. Each week the worksheet changes so there is no consistent layout.
if you open a workbook & choose not to enable macros, is there any way to later change your mind & set macros to enabled without having to close & reopen the workbook?
I'm trying to avoid using macros in my workbook, however, I'm unable to get my worksheets to change name (based on a named range) without use of them, as follows.
I've written the macro:
Sub MyMacro() ActiveSheet.Name = Range("MyRange") End Sub
and then dropped the following into the worksheet Private Sub Worksheet_Activate() Run "MyMacro" End Sub
This works fine, but I'm trying to avoid using macros. When I try to place the following script in the worksheet private code, nothing happens:
Private Sub Worksheet_Activate() ActiveSheet.Name = Range("MyRange") End Sub
I have a file here that already has macros in it. The file is basically a excel document generator.
When you click create sku, the document will generate multiple documents based on the user inputs.
Two things in this file
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
combine two Worksheet Change event macros into the one macro? The macros are listed below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo exitHandler Dim rngDV As Range Dim lRow As Long Dim lCol As Long
[Code]...
exitHandler:
Application.EnableEvents = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("O:O")) Is Nothing Then Application.EnableEvents = False
I have a complex sorting macro with userforms & Modules. Works great! Now I need to save the final Worksheet without the macros. The final step to my project is a Application SaveAs. Can I add a delete-all-macros step when the user clicks the Save As button which would create a new workbook with the finished worksheet and NO macros?
I need to copy my worskheet multiple times to create about 10 of the same worksheets within a workbook. I know if you click in the very most top left hand corner of the worksheet, then click copy, go to the new worksheet and click paste, it copies the whole page, but it does not copy over the VBA Code.
Also, for some reason, it does copy over my macro buttons, but does not put some of them in the right spot?
