Macro Stops At End Of Designated Range?
Apr 11, 2013
Problem with the attached Range Overrun.xlsm.
Form opens with set number of rows. End User then enters figure into C3 of how many row are to be added.
VB:
Option Explicit
Dim c As Range
Dim j As Integer
[Code]....
Macro works correctly by adding the number of rows listed in C3. It SHOULD then "name " various cells in the original and new rows.
BUT the Macro goes ON to name cells in rows BELOW the ones that are added.
So on the attached worksheet the original rows were 6 - 9. Rows 10 - 12 were added, but the macro names the cells from Rows 6 - 14.
View 2 Replies
ADVERTISEMENT
Nov 2, 2009
I really appreciate help from the excel gurus and expert here on my little problem. I'm rarely using excel and have a very little knowledge on VBA programming.In short, I've been requested to develop a shift schedule for my department that can automate the shift staff schedule on the monthly basis.
I've created two worksheet of which the first worksheet is represent as master data list (INPUT Worksheet) that contain with formulas. While the other worksheet is the automated shift data (REPORT Worksheet) that linked from the master data list (INPUT Worksheet).
Attached herewith is a sample of my work. As you can see,I wanted to copy comments from the input worksheets (on DUTY column) so that it will then automatically appears or updated in the shift schedule. Whenever the dropdown selection change, the data will change accordingly. On the REPORT worksheet, each cells, range D14:AH38 are referring to DUTY column month by month. I'm able to link it but it fails to copy over the comments as well. I believe that macro can resolve this, can someone help me on this?
View 12 Replies
View Related
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Feb 25, 2009
The macro stops in the last row - it isn't able to select a cell in another sheet than "Menu" (I tested it different variations).
View 9 Replies
View Related
Apr 13, 2014
i ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
View 1 Replies
View Related
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Dec 10, 2013
I have a macro stored a module at Personel workbook and i am using personel workbook as a hidden one. when i assign a shortcut below macro it is not working but whrn i am press F5 at VBA Editor it is working.
Here is the code:
Code:
Sub Transferdata()
On Error GoTo err1:
Application.DisplayAlerts = False
[Code]....
View 7 Replies
View Related
Mar 12, 2009
I have a problem with the following code
Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok
' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok
This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.
If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.
View 9 Replies
View Related
Mar 25, 2007
I could need a code for a Combo Box to selects the range in Column A. The range includes all non-blank cells.
View 9 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Jun 10, 2008
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.
View 14 Replies
View Related
Sep 28, 2011
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
View 8 Replies
View Related
Oct 2, 2008
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
View 9 Replies
View Related
Jul 10, 2006
I am wirking on a macro which opens a 'sourcefile' and then do some filtering and vlookuping to distribute the data to several files. When i run the macro from the VBA editor, everything runs fine. But if i run it from .xls file, the macro stops after opening the 'sourcefile'
Workbooks.Open Filename:=sourcefile
For i = 0 To 13
'do something...
Next
View 9 Replies
View Related
Oct 20, 2006
Can't run vba macro. I have the following problem with a macro in excel. i wrote it, the macro runs very good from excel /tool/macro/macros/run, i also put a button on toolbar -it works too, i want to assign a keyboard shortcut from tool/macro/macros/options / shortcut and THE MACRO RUN INCOMPLETELY (stops running ).
Actually the macro opens all files in a specific folder, and delete their codes as follows:
Sub delete()
Const strNotFound As String = "There is NO Excel files..."
Dim ffTmp As FoundFiles
Dim wb As Workbook
Dim objVbc As Object
Dim objFile As Variant
Dim lngRet As Long
Application.DisplayAlerts = True
'Search Excel files
With Application.FileSearch
.NewSearch
.LookIn = "C:Corectii\_CS"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks...............
View 7 Replies
View Related
May 16, 2007
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then
ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls"
Else
ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls"
End If
Workbooks.Open Filename:=ReportFile, ReadOnly:=True
Worksheets("TRANSFER DATA").Select
PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
View 9 Replies
View Related
Sep 3, 2007
I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True
1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.
View 5 Replies
View Related
Oct 15, 2008
I have a macro that that stops every time I’m trying to delete the sheet. I get this message. “Data may exist in the sheet(s) selected for deletion. To permanently delete the data, prese Delete.” and I have to manually click “delete” to continue running the macro. How can I avoid this stop so that macro runs thru it without stops?
View 2 Replies
View Related
Oct 18, 2008
I have a Macro that changes the background colour cells dependant on the value in another cell, all seems ok but when it runs it scrolls the page as it shades the cells and then exits leaving the sheet where it scrolled to.
Is there a way to get it to return to where it started or not to scroll?
View 14 Replies
View Related
Jan 28, 2009
I have been working on some Macros. I am compiling a macro that runs other macros. I have two noticeable problems.
1 - My SaveAs macro runs fine by itself. But when called or ran by another macro, (which calls more than one macro) the SaveAs macro it stops.
2- Many of my macros, when ran by another macro (which runs more than one macro) cycles through twice.
My specific question is about no. 2 and the following are the individual macros and the macro that runs them.
View 2 Replies
View Related
May 10, 2006
I have built a small userform with 3 fields. The macro ran OK first few times. Now, when user enters data in the form and clicks OK nothing happens. I found that repeated clicking on OK or Cancel button on form had no effect. I then observed that VBA editor was open and the yellow cursor was displayed on the first code line under cmdOK_click procedure. The code line was also highlighted in yellow. There are no errors to debug and no break points etc. When I clicked the Run (or Continue) icon from the VBA toolbar, the macro completed OK. Question: Why is the macro pausing on the first line and how can I make it run without pausing for no apparent reason.
View 4 Replies
View Related
Nov 15, 2013
Whenever I try to use Sheets("Volumes").Select or .Activate on a specific worksheet, my macro code will immediately terminate with no error message. Iv'e used F8 to step through several modules and found that this happens every time it hits that line. The wierd thing is that i can select this sheet when screenupdating is off and I select it from a called subroutine. No other worksheets in this workbook are having this problem.
Additional info : using Sheet1.Select will select it with no issues but I don't want to go this route since it's a workaround and not a solution to somthing that should work.This problem occurs in several modulesEverything used to work fine and just one day it decided that i couldn't select the "Volumes" sheet in VBA anymore.I can click on the sheet no problem and it is not protected or hidden.
View 1 Replies
View Related
Jan 4, 2012
I have a simple list of data (Name, Room, etc.) in a spreadsheet. Each day I click on the spreadsheet tab and create a "copy" and (move to end). This creates a spreadsheet for the next day's data.
My problem is that the macro I use to sort this data never works in the newly created spreadsheet within the same workbook. As usual, I'm sure it is something relatively simple that I am overlooking.
View 1 Replies
View Related
Dec 12, 2013
I am using Excel2010.
My sheet displays values collected from a database via an AddIn. The values are updated by pressing CTRL+G (AddIn function).
I have an OnTime macro running SendKeys "^G" to update the values every minute. When the values exceed a certain limit, a sound is played from a custom Alarm function.
This all works as long as the excel window is active.
But if excel is minimized or another window is opened, the values stop updating until Excel is active again.
When the values dont update the sound is not played and that defeats the purpose of the sheet.
View 6 Replies
View Related
Apr 1, 2008
I have a excel spread with a range of A1:H64. The spreadsheet is designed for the user to enter information into the appropriate cells, i.e. user ID in cell D4; name in cell D5; and date in cell F5. My question is instead of the user scrolling around and finding the cells that require input is there a way to set the spreadsheet where the user tabs and/or uses the enter key to move around and go only to the cells that require input and not to the ones that are locked and protected?
View 9 Replies
View Related
Feb 11, 2009
I have a data validation in cell A1 = 2,3,4,5
A2, A3, A4, A5, A6 is empty.
What i want to be able to do is,
when cell A1=2 (A2 & A3 wilL appear the value "1")
when cell A1=3 (A2, A3, A4 will appear the value "1")
when cell A1=4 (A2, A3, A4, A5 will appear the value "1")
when cell A1=5 (A2, A3, A4, A5, A6, will appear the value "1")
View 9 Replies
View Related
Dec 23, 2008
I read in a book that if you enter a formula in a cell, like cell A1 contains
=rand() for instance, that if you select Go on the menu tab, and then enter the final destination cell or range (ex: A1,A200), then hit ctrl+Enter simultaneously, it will fill the formula down to that cell. I can get it to select the range, by hitting Shift+Enter, but not copy down the formula using Ctrl+Enter, or Ctrl+Shift+Enter. I am using excel 03, XP. It only returns blank values for the range.
View 2 Replies
View Related
Aug 1, 2012
I am trying to do is have a VBA automatically fill a blank cell with text of a different color.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B8") = """" Then
Range("B8") = "Name"
End If
End Sub
Is what I've been trying to get to work. If the value in B8 is suddenly deleted, I want that cell to show "First Name", in a lighter grey. I know I can get the color of the text by using the conditional formatting, but how do I get that cell to auto-fill when deleted?
View 3 Replies
View Related
Jun 30, 2013
What am I missing here. The following code fragment
strTestString = In_Wrksht.Range("A" & i)
strNameString = Trim(Mid(strTestString, 6))
Debug.Print strTestString & " uuu"
Debug.Print strNameString & " eee"
In_Wrksht.Range("B" & i) = strNameString & " XXX"
Debug.Print Trim(In_Wrksht.Range("B" & i)) & "ggg"
returns these results:
7/ 31 FIDELITY SHORT TERM BOND uuu
FIDELITY SHORT TERM BOND eee
ggg.
strNameString is not being assigned to B&i. Why Not?
View 9 Replies
View Related
Jun 24, 2014
I have several separate Excel files that are all formatted in the same way.
I want all this data, excluding the header rows, from those separate Excel files, to appear in a new/designated Excel file. - I don't want to keep copying and pasting.
I also want the data, once extracted/copied/exported, to be formatted according the formatting style on the designated Excel file.
View 4 Replies
View Related
