Recorded Macro Doesn't Seem To Provide Expected Result
On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Formula Doesn't Provide Amount As Expected
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
View Replies!
View Related
Highlight A Cell Formula Result If It Doesn't Match
I've been asked to assist with modifications of an excel spreadsheet. One of the features is a column that has a formula of what the total order should be. For example, 1 base order of 25.00, plus 1 bonus order of 18.00, plus 2 bonus orders of 15.00 would be $73.00 due. Then data entry persons enter the actual amount collected. Is there a way to highlight either the cell or the result if the amount collected does not equal the amount due? I know I can accomplish this with another column, but they want to keep the columns to a minimum. This is for a non-profit agency and the orders are now at 1000 that must be entered within a day or two period, so they want to keep the fields to a minimum so as to make the entry easier for those doing the input. And they would like the data entry person to see an immediate flag if what he or she enters as collected does not equal what is due.
View Replies!
View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another. When I run the macro from the Tools>Macros menu it works perfectly. But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed. The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button. Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
View Replies!
View Related
Script Works In Debugger But Doesn't When Macro Runs
I am trying to do a very simple Macro for merging data. My problem is that the macro works when I am stepping through in debugger, but does not work when I am in excel and I use the shortcut key (crtl + m) to run the macro. What happens when it doesn't work properly is it selects rows 2 and 3 and tries to paste them to the new workbook and I get an error saying the cells are not the correct type. I don't want it to do this because rows 2 and 3 are titles and have nothing to do with the data I am moving. As I said before, when I open VBE and step through the code it works just fine. The error only happens when there is no data on the initial work sheet.
View Replies!
View Related
Insert Picture Macro Doesn't Work In 2007
I just upgraded to Excel 2007 and my macro from Excel 2003 for inserting a picture doesn't work correctly in 2007. I have a command button that states insert picture and when you click it, it will let you insert a picture into the cell and hide the command button. In 2007 it will let you insert the picture, but it isn't centered and expands into other cells and the command button is still visible. The picture appears to be the correct size it just doesn't center itself in the cell and the command button is still visible. Below is the
View Replies!
View Related
Macro Doesn't Work When Workbook/File Is Shared
I am encountering a specific scenerio where In I am creating a New file by copying one of the sheets And renaming that With todays date. here starts me problem when I try To share the sheet To work around I am seeing that th macro Is Not copying the sheet properly even though i have given PasteSpecial. The code goes here VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing Is Not transferred when copy/pasting, but Is If the keyword uses "quotes". Sub Newsheet() sheetname = Format(Now, "dd-mmm-yyyy") MsgBox sheetname sheet_count = Worksheets.Count 'Checking for Replication................
View Replies!
View Related
Combo Box Macro In Worksheet Doesn't Run Automatically
I have a combo box in a worksheet with a macro written for it that works fine but when I open the worksheet it doesn't run automatically, I have to find the macro and tell it to run. How can I get it to run as soon as the worksheet is opened Here is the code for the macro Private Sub combobox() ComboBox1.clear ComboBox1.AddItem "Mit Rekuperator" ComboBox1.AddItem "Ohne Rekuperator" End Sub Private Sub ComboBox1_Change() Run "PinchInternHeatExchanger" Select Case ComboBox1.Text Case "Mit Rekuperator" ActiveSheet.Range("I62") = Worksheets("uorc").Range("L99") Case "Ohne Rekuperator" ActiveSheet.Range("I62") = Worksheets("uorc").Range("E11") End Select Run "PinchPoint" End Sub
View Replies!
View Related
Import Macro Trying To Gain Acces To File That Doesn't Exist.
I get the following error: "Run-time error '1004': "cmc4906.xls" File cannot be found. Check the spelling of the file name and verify that the file location is correct." I am not trying to open a xls file. The path is clear and there are no file names with extensions in the path name (C:Weekly). I'm unclear of why the code thinks its looking for file cmc4906.xls and a xls file at that. Sub Import() Dim inputfile As Variant Dim path As Variant path = ("C:Weeklys") inputfile = Dir("C:Weeklys") Do While inputfile <> "" Workbooks.OpenText Filename:=inputfile, Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, Comma:=False, _ Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 1), Array( _ 3, 1), Array(4, 1), Array(5, 2), Array(6, 2), Array(7, 1), Array(8, 1), Array(9, 1), Array(10 _.............
View Replies!
View Related
Bug - Macro Crashes When Run, Doesn't Crash If Save First
I'm just wondering if any of you can lend some help on a bug i'm encountering. When I press a button on my worksheet to call a macro that brings up a userform, the excel will crash, and give that "do you want to report this error", close down excel, and reopen on a blank worksheet. Strange thing is, this error is avoided if I save the workbook upon opening it, and then call that macro. The workbook does not read data from external sources, so i dont think is that problem. Has anyone encountered this?
View Replies!
View Related
Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints ! Is this possible? FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells. I used this code from a post on a similar topic, but excel is complaining code in bold Private Sub Print_Area_Click() Dim lastCell As Range Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0) Do Until Application.Count(lastCell.EntireRow) 0 Set lastCell = lastCell.Offset(-1, 0) Loop ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address End Sub
View Replies!
View Related
Doesn't Work If The Worksheet Hasn't Been Saved Yet
I'm trying to simplify a formula that I'm using in a spreadsheet that's being used mostly in Excel 2000. 2 reasons I'm doing this: 1) It doesn't work if the worksheet hasn't been saved yet. 2) It's ugly =DATE(YEAR(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2))),MONTH(DATEVALUE(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3) & " 20" & MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+5,2)))+1,0) The formula returns the last day of the month of the name of the sheet. For example, if the sheet is names "Mar 07", it returns 3/31/07. (yeah, when the 2100 issue becomes a big deal, I'm not going to be ready but I have 92 years to think about it) Even if I can't make it prettier, does anyone have a way to get the sheet name into a formula without VBA Code in a file that hasn't been saved.
View Replies!
View Related
Worksheet Function Too Long To Be Recorded In Macro
I have a list of items in Sheet1 column A (starting from row 1). I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows: If the value in column A has "SYS????CZ", column B should be "HPC" If the value in column A has "SYSNIS", column B should be "NIS" If the value in column A has "SYSJBE", column B should be "JBE" If the value in column A has "ICG????", column B should be "HPC" If the value in column A has "IL????", column B should be "RUP" If the value in column A has "SYSHPC08", column B should be "HPC" What I tried was to record a macro while typing the formula in B1: ...
View Replies!
View Related
Recorded Macro Missing Paste Commands
I have been using the "record macro feature". What I want the macro to do is as follows. 1. cut all data from column C and paste it into G. 2. Use the textTocolumns feature to split a comma delimited string into 2 seperate columns. 3. user the textToColumns feature to remove any leading spaces from column G. 4. cut columns d-H and shift them left startign in column C. I can do all of this find bu using the GUI while recording the macro but when I check the vb script it's completely missing the paste commands. When I try and run the macro it, quite rightly, returns "run-time error '1004':No data was selected to parse. Below is the resulting recorded macro. How do I amend this macro do include pasting the cut data?
View Replies!
View Related
Speed-Up/Optimize Recorded Macro Code
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro; Sheets("Calc sheet").Select Range("C6").Select ActiveCell.FormulaR1C1 = "=Schedule!R[1]C" Range("C7").Select ActiveCell.FormulaR1C1 = "=Schedule!RC[1]" Range("C8").Select ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]" Range("C9").Select Sheets("Schedule").Select Range("G7").Select..................
View Replies!
View Related
Type Mismatch '13' On Passing Function Result To Variable
I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly. Sub addtasks() Application.DisplayAlerts = False myrow = Cells. Find(" Total P&C Estimate").Row - 3 mycell = Cells(myrow, 2) mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1 With Range(Cells(myrow, 2), Cells(myrow + 2, 2)) .EntireRow.Copy .EntireRow.insert Shift:=xlDown End With Application.CutCopyMode = False Cells(myrow + 3, 2) = "Task#" & mynum Application.DisplayAlerts = True End Sub
View Replies!
View Related
'greater Than >' Formula & Result
Column B is basically 550 points + column C = Base Roll. We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps. So far it's all ok. Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000. Entering a value of 500 displays 800 in column F instead of 500.
View Replies!
View Related
Hyperlink Doesn't Work
I have a hyperlink to [url] that doesn't work. When I click it I get an error saying "Unable to locate the server or proxy server". But when I cut & paste the address from the hyperlink into my browser (no chance of mistyping), it works fine.
View Replies!
View Related
Validation Drop Down Doesn't Appear
I named a range State, it contains a list of all the US state abbreviations. I do a data validation that refers to this range as a List. If I type in something that's not in the named range I get the error message I created for the Validation. But - I don't get a drop down button. I tried to do the same validation on another cell and get the same thing. Other drop downs for validation appear.
View Replies!
View Related
Right Click Doesn't Work
As said, the right click doesn't work anymore, last week it was working but it doesn't anymore. When I right-click wherever in the spreadsheet nothing happens but I know it's working because it works outside excel.
View Replies!
View Related
Listbox Doesn't Redraw
I have a worksheet with a listbox from the controls toolbox. I have to use the controls toolbox activex version because the box is formatted and I don't want a vertical scrollbar. I don't want to use a userform either because that would be major overkill. When the view is set to full screen the listbox partially disappears. Paging up and paging down will redraw the screen and the listbox. I've tried application.screenupdating with different listbox events but nothing works.
View Replies!
View Related
Some Times If I Do A =if( ) Formula It Doesn't Work
What am I doing wrong here. Some times if I do a =if( ) formula it doesn't work. For example, look at the attached picture. Cell K63 should say "End of Run". But it doesn't, What gives? Also I have had before where i do =IF(X62>$O$2,"End of Run", 0) (and $O$2 =81) and the cell when X62 is 81, not greater than. I know I can to >=, but thats not what I am doing.
View Replies!
View Related
Why Doesn't The Sound Play When The Value Changes Indirectly?
This is a complete mystery to me. I have some code (reproduced below) that plays a .wav file when any of the values in a1:a9 changes from 0 to greater than 0. This code works when I manually change one of the values to a trigger number in any of the cells. The problem is that the values in a1:a9 are really pointers to other cells. For example, a1 is "=sum(D9:D20)" So, when I d9:d20 chnages values such that a1 changes from 0 to 5, the sound doesn't play. To put it simply, if I type "5" in a1 the sound plays. If a1 = D1 and I type "5" in D1 the value in A1 changes to 5 but the sound doesn't play. How do I modify this code such that any change in the value of A1:A9, no matter the source of that data change, triggers the playing of the sound? ......
View Replies!
View Related
NAME Doesn't Stay Attached To The Cell
I have a cell (H27) which has a (sum) total in and have attached a NAME FRED. This is fine as long as the client doesn't add a row above it. If they do when I re-open the sheet the NAME doesn't stay attached to the cell (now H28) Is there a way to keep the NAME attached to the cell regardless where it is? Private Sub Workbook_SheetActivate(ByVal Sh As Object) Range("$H$27").Name = "Fred" End Sub
View Replies!
View Related
If Statement Doesn't Recognize Non-zero Values
I have the following formula with multiple if statements: IF(N2>0,N2,IF(O2>0,O2,IF(P2>0,P2,IF(Q2>0,Q2,IF(R2>0,R2,IF(S2>0,S2,IF(T2>0,T2))))))) This formula refers to cells in columns N,O,P,Q,R,S and T. Every cell in columns N,O,P,Q,R,S and T also contains formulas (VLOOKUPs). My if statement works fine but when it checks cells in column P, for some reason, it doesn’t recognize cells with non-zero values. I can’t figure out why this is happening and how to fix this problem.
View Replies!
View Related
Isolate Data That Doesn't Match
I have a long lists of values (product numbers) from our inventory records. Now, we've been given a new list of product numbers, some are the same and some are not. I want to isolate (spit out) the values from column A, than don't match column B. Column B is the list we SHOULD be using, column A is what we're currently using. See example below. Old Products New Products 34544 34544 34545 34546 34546 34547 34547 34548 34548 34550 34549 34551
View Replies!
View Related
Full Screen Doesn't Work.
This might be extremely easy, but why my Excel (nor Word for that matter) doesn't go to full screen anymore? I'm pretty sure that I haven't used any options, I do have a new graphics card, but all the other applications goes to full screen. I have restarted my computer. Basically I think that it's either some option, or then reinstalling Office.
View Replies!
View Related
VBA Doesn't Recognise Selection
I have a report that has been running in it's current state for several months with no issue. However, today it has decided that it doesn't understand the word 'selection'. eg: selection.copy gives compile error. Now... obviously NOT USING selection is preferred. However, let's put that aside for now because there are LOTS of these in this report and it's never had an issue until today. Is there some setting that could have gotten changed or something? It's happening on multiple computers and we're running Excel 2003 (standard) with SP3.
View Replies!
View Related
Counting Even If Doesn't Meet Criteria
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26. Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field. Here is the formula/function I'm using: =(COUNTA(C2:C26))/COUNTIF($B$2:$B$26,"YES")
View Replies!
View Related
Private Sub Workbook_Open() Doesn't Execute
why the code below might not execute ? It just seems like it skips over the code and doesn't run at all. Private Sub Workbook_Open() 'AssignRequestNumber() MsgBox ("Whoa") Run "AssignTrackNumber" End Sub I have a macro assigning a unique number to the file in a fixed cell whenever the .xlt file is opened from File-New.
View Replies!
View Related
Code Doesn't Loop Properly **
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time: Dim c As Long Dim Limit As Long Limit = Cells(Rows.Count, 11).End(xlUp).Row For c = 2 To Limit If Cells(c, 22).Value = "" Then Cells(c, 22).EntireRow.Delete xlUp End If Next c
View Replies!
View Related
Programme Doesn't Work When Sent To A Different Computer
I have a programme which works fine on my computer at home but when I send it via email to work the 'sort' function messes up (there is a chance it could be the 'vlookup' function, though) and all the cells in the sort range lose their formula reference - which is of course a tad annoying. The part of the programme I'm having difficulty with is this: Range("SsNo1").Resize(d, f).Offset(a, 0).Select Selection.Sort Key1:=Range("FRSet1"), Order1:=xlDescending, Header:=xlGuess _ , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal
View Replies!
View Related
Array Doesn't Seem To Write Out Results
I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please? This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet. Sub Macro1() Dim temp(1, 1000) As Integer i = 0 previouscell = 0 For Each cell In Range("C:C") currentcell = cell.Value abc = previouscell + 1 If currentcell <> abc Then temp(1, i) = currentcell i = i + 1 Range("D" & i).Value = temp(1, i) End If previouscell = cell.Value Next cell End Sub
View Replies!
View Related
Why Doesn't My Variant Array Work
The answer is probably "because I'm stupid", but I really can't get my head around it! I'm playing with variant arrays for the first (and possibly last) time, The code I have is: Sub test() Dim vSheetColours As Variant Dim iCounter As Integer vSheetColours = Range("Colours").Interior.ColorIndex For iCounter = 1 To UBound(vSheetColours, 1) MsgBox vSheetColours(iCounter, 1) Next iCounter End Sub (Obviously this code doesn't do anything useful - but if I could get it to work, I might have a chance of making my real code work!) Colours is a range of 8 cells. Each one has some text in, and has a different background colour. I'm trying to store the colours. If I run this code, I get a runtime error 13 type mismatch, and it highlights For iCounter = 1 To UBound(vSheetColours, 1) But if I replace vSheetColours = Range("Colours").Interior.ColorIndex with vSheetColours = Range("Colours").Value it works fine.
View Replies!
View Related
Delete Row If Doesn't Contain Certain Numbers
Working at a company where we every month extract our customers into a excel document. This is the CRM system , and some of the clients can't (shouldn't) be approached. In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
View Replies!
View Related
Right-click Menu Doesn't Exist
I have few questions, 1. why the "auto sum" icon grey out, and doesn't work. 2. when I right click the sheet, the menu doesn't show up. 3. Insert columns/rows, When I highlight 2 columns/rows, right-click the mouse & the menu doesn't show up for me to choose insert col/row.
View Replies!
View Related
Workbook_Open Doesn't Run On Second Open
I have noticed this on more than one workbook with an Workbook_Open macro. When you open Excel, open Workbook1, do some work , save or don't save and close, then reOpen Workbook1 without having closed the Excel application, the Workbook_Open macro in Workbook1 doesn't run. Closing Excel and reopening Workbook1 initiates the Workbook_Open macro. It's as if Excel remembers having previously opened Workbook1 and so it doesn't rerun the Workbook_Open macro the next time you open it.
View Replies!
View Related
Scroll Down/Across Worksheet Doesn't Move
I have two basic questions regarding the Excel program/UI I'm currently using Excel 2000, One annoying thing I find with this version is the way the screen doesn't follow along with my scrolling. ie: when I use the vertical scroll bar on the main window, it only updates once I release... not like a typical window which follows the movements of the scroll curser. Is this typical for later versions? I would update this program just for this feature alone! Also, a final question.... Does anyone know if there are any plans to update the handheld (pocket pc) version of Excel? The factory installed program is incredibly limited.
View Replies!
View Related
|
TRACKER
