Prevent Custom Function Running When Inserting/Adding Rows
Apr 8, 2008
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
View 2 Replies
ADVERTISEMENT
Jul 31, 2014
I have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
View 2 Replies
View Related
May 26, 2014
I have the macro to insert rows every nth row, but need to be able to insert text into that nth row.
I have a list of addresses and every 10 addresses i need to insert a 'seed' which will be have the same details everytime. This is what i have so far...
Sub InsertRowsMod10()
Dim r As Long
r = 10
Do Until Len(Cells(r, 1)) = 0
Rows(r).Insert Shift:=xlDown
r = r + 10
Loop
End Sub
View 2 Replies
View Related
Dec 7, 2006
If I have a sum function that adds up:
= SUM(U41:U45)
but I add a row at 41 I now get sum function
=SUM(S42:S46) when I really want it to incorporate the just add row to look like this:
=SUM(U41:U46)
View 3 Replies
View Related
Jan 7, 2010
I am instantiating Excel 2003 from vb.net and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?
View 2 Replies
View Related
Nov 14, 2008
I need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies
View Related
Apr 29, 2008
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 Replies
View Related
Sep 11, 2008
I have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If
If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub
View 9 Replies
View Related
Oct 19, 2007
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...
At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
View 5 Replies
View Related
Feb 26, 2013
I have a spreadsheet with 27 Columns and 439 rows of data. I need to copy each row of data that has a certain criteria and paste the same data 141 times below it and then manipulate the data. In the same spreadsheet I need to copy a row of data that has other criteria in it and past it 30 times below it, and then manipulate the data.
I have been using the copy and insert copied cells function, but I have to scroll down 141 or 30 rows each time to ensure I add in the correct amount of rows. Is there a more productive way to do this? I have about 10 workbooks with approximately 47 tabs/worksheets each that I will need to update in a similar fashion.
View 4 Replies
View Related
Sep 8, 2007
I added a menu command button to a toolbar in a specific workbook. The macro is housed only in that workbook and the macro needs to run on every worksheet in the workbook. However, the button also appears on EVERY Excel workbook that I open when it should not. How can I create a menu command that only appears in the workbook that contains the macro?
View 2 Replies
View Related
Nov 12, 2013
I have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.
This is my formula;
=IF(ISERROR(VLOOKUP(A3,Data!$A:$AD,23,0)),"",(VLOOKUP(A3,Data!$A:$AD,23,0)))
When I insert a new column at 'A' the formula changes (as below)
=IF(ISERROR(VLOOKUP(A3,Data!$B:$AE,23,0)),"",(VLOOKUP(A3,Data!$B:$AE,23,0)))
How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?
View 2 Replies
View Related
Aug 15, 2012
How do I set up a custom validation formula to prevent duplicate enties?
For example I I've already enter the song name 19 and Paul Hardcastle (BandName), how do I set it up where I can not enter that combination again on a row?
I was wanting to use a custom CountIF function but I could not get it to work.
A
B
Song
BandName
19
Paul Hardcastle
[Code] ....
View 4 Replies
View Related
Aug 23, 2008
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
View 7 Replies
View Related
May 17, 2008
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies
View Related
May 22, 2012
Basically, I have a sheet that shows time in Days going downwards (monday tuesday etc.) and is broken out by week. In Column C, I have Project Names. Columns G-O have numbers.
What I need to do is run a custom sort multiple times down the page so that each week has been sorted by Project Name (column C). I was hoping to be to just make a quick macro shortcut to run my custom sort and each new range I selected but I don't know how to make the macro run on the actively selected cells and not hard-coded cells.
View 2 Replies
View Related
Mar 20, 2009
Another interesting dilemma to solve. Using this formula:
View 2 Replies
View Related
Aug 14, 2014
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
[Code].....
View 2 Replies
View Related
Jan 9, 2008
I have a dynamic chart that allows the user to select the starting and ending dates for the data range by using a scroll bar.
The dates in the data table are input daily and are generally in sequential order, however there are gaps between dates (eg. 1/4/08, 1/7/08), such as would occur over a weekend or holiday period. The dates are used as labels for the X-axis.
The problem is the chart interpolates the dates in the "gap" and includes them on the X-axis and plots them as zero values.
I would like for the chart to display only the dates and values actually in the dataset and not interpolate and include dates/assign zero values.
Haven't had any success in searching the forum. Can this interpolation be prevented?
View 5 Replies
View Related
Nov 9, 2007
I use this function to prevent that the user is saving the excel file manually:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
But now I also cant save the file with a macro!
ActiveWorkbook.SaveAs pathResult & "Files" & name & No & ".xls"
How can I allow the macro to save the file?
View 9 Replies
View Related
Mar 27, 2013
I have cells (all in one column) containing text separated by commas e.g. (SD-299, SD-200, SD-300)
I am trying to transpose the text in these cells into rows.
VB:
Sub SplitAndTranspose()
Dim N() As String
N = Split(ActiveCell, ", ")
ActiveCell.Resize(UBound(N) + 1) = WorksheetFunction.Transpose(N)
End Sub
The problem with that is that when transposing it does not shift the cells down (/ insert new rows) so I lose the data already in the cells underneath.
Also, that macro would only apply to one cell I would like to be able to apply it all the data in the specific column on my Sheet.
View 3 Replies
View Related
Mar 28, 2014
I'm inserting a function in a cell via code:
[Code] ........
The problem is that I want the second cell of the NETWORKDAYS function (in this case: R2C11) to be a variable cell. I want it to be cells(2, c - 3).
View 3 Replies
View Related
May 25, 2013
I'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1
Test 2 32, 343, 63, 34 email2
Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1
Test 2 32 email2
Test 2 343 email2
Test 2 63 email2
Test 2 34 email2
Test 3 4934 email3
Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code:
Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
View 7 Replies
View Related
Dec 31, 2008
I have an excel sheet that has the A column populated with many different things.
I'm not sure how possible it is, but I'm trying to group everything up by 7s and putting a space in between the groups.
So...
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
Would turn into
ABCDEFG1
ABCDEFG2
ABCDEFG3
ABCDEFG4
ABCDEFG5
ABCDEFG6
ABCDEFG7
HIJKLMNOP1
HIJKLMNOP2
HIJKLMNOP3
HIJKLMNOP4
HIJKLMNOP5
HIJKLMNOP6
HIJKLMNOP7
QRSTUV1
QRSTUV2
QRSTUV3
QRSTUV4
QRSTUV5
QRSTUV6
QRSTUV7
View 9 Replies
View Related
Apr 21, 2009
I'm using IF functions to get the difference of two cells. An example:
View 5 Replies
View Related
Jul 15, 2014
The code below pulls information based on what i specify in a userform from another sheet.
I am trying to total the last 3 columns but for some reason the code sticks the sum formula right in middle of all my information. However, when i run the code (the exact same way) again then the code puts the sum underneath the last row as indicated in the code. How can i get the code to run right the first time around?
View 8 Replies
View Related
May 21, 2009
I have around 700 workbooks in a folder on my PC.
My master file(master database.xlsx) is kept open from which i have to Vlookup the values into these 700 workbooks.
The "agent Id" column is common in both the master database.xlsx as well as those other workbooks also.
The thing i want to do is i need the data from the "reporting supervisor" which is in the master database.xlsx file to be available in the G column of the other 700 books ( i have attached only 4 sample books). The G column can be then named as "Reporting Supervisor". Since "Agent ID" is available in both files we can vlookup taking this as a base. I have attached the 4 sample files out of 700 as well as the master database file in a zip file.
View 14 Replies
View Related
Oct 31, 2011
I have macro that brings information from outlook to excel. In the beginning of macro, it deletes range of cells. That destroys the functions that target those cells. Is there a way avoid that? Using some different method or ?
Code:
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myCalItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
[Code] ........
Running the macro messes up all funtions that targets those cells.
Like:
Code:
=DATEVALUE(MID(data!#REF!;4;2)&"."&LEFT(data!#REF!;2)&"."&RIGHT(data!#REF!;2))
This really great code to get data from outlook is originally: [URL] ........
View 2 Replies
View Related
Mar 16, 2009
I have a set of macros that allows me to call up lotus notes and send emails. However, i am not able to call up the 'prevent copying' function of lotus notes.
View 9 Replies
View Related
May 3, 2014
I am trying to use the simplest code possible (being a novice) to execute a macro which makes a copy of Cell A1:A4 and paste its values to Cells B1:B4 if cell B5=1 (or whatever). The Macro with copy and paste works (I recorded it and in my testing it was ok). And also the function, that contains If-Then status of cell B5, works and calls correctly the Macro.
Yet when the macro is called by the function (=CopyMyCells(B5)), the Macro (MacroCopy) runs - I tested this with a Msgbox - but stops just in doing what I need, i.e. in doing the copy-paste process. Here the code which is placed in a module of the proper Worksheet (Excel 2003, on WinXPSp3):
[Code] ........
View 6 Replies
View Related
