I added a new sub to and now I'm getting a compile error. This was working fine until I added "Cust_Rev1". I get an compile error saying "expected variable or procedure, not module". Both are located in personal.xlsb. If I rem Cust_Rev1 out, I don't get a hiccup. (FWIW, I've been running "Cust_Rev1" on its own to debug it.)
View 2 RepliesI would like to apply different conditional formatting at different times with a click of a button. I setup a dummy and turned on the recorder and recorded this
Range("A7:N7").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1"
Selection.FormatConditions(1).Interior.ColorIndex = 37
Range("A1").Select
End Sub
I tried changing to this
'/Conditional Format - OTHER EXPENSE B/L
Set rngConditional = wsData.UsedRange
With rngConditional...............
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
I am having a problem trying to create a macro that will loop in order to create several macros. I have a long macro (call it mainmacro) that needs to be able to create several simple macros to be used by the user of the workbook. Each macro has a variable that needs to be taken from mainmacro. Each macro is different based on the value of the 'i' loop variable that the macro is created in. i'm sure im making this sound more complicated than it really is...basically:
Sub mainmacro()
'lots of code not relavent to this issue here
For i = 1 to 10
'create 10 macros that, when run, would highlight A1, A2, etc.
Range("A" & i).Select
Next i
End Sub
This is just an example. But, basically, i just want to be able to automatically create a bunch of macros with a variable from mainmacro.!
I have a list on my first worksheet that is sequentially numbered in the first column, and has work activities in the next column. On my second worksheet, I have those sequential activity numbers as the column headers on a new list. Problem is I don't have any room to label the new list by the activity's actual description(second column, first sheet). If I did, I would just use a simple VLOOKUP.
So, what I would like to do is utilize the VLOOKUP to pass the activity description string to a UDF that will create a comment in those column headers. Then when the user mouses over the activity numbers, the respective activity descripiton will pop-up as a comment thus solving my space problem!
i have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
View 9 Replies View Relatedi have a combo box that contain range of number from (1-9)
i need some help in creating the yellow boxes automatically after i have choose the number that i want from the combo box
In case i creates a list using validation in column d ( for ex list contains either A or B or C ), now i want to create another lis in column e but wants that this new list will relate to list of colum D , i.e. in case some one selects A from drop sown list of column D then one list appear containing some options to select or if he selects b from drop down list in column D then diff list appear in column e ,
View 9 Replies View RelatedI have a database (ATTACHED) which contain name, roll number, courses taken etc. I want to create different file for different courses showing the roll number and name of the student who have written their name as REGISTER in the course.
View 3 Replies View RelatedI have a vbscript that creates a workbook in an existing spreadsheet. I would like to have the script create the spreadsheet if it does not exist. That part is easy enough. The part I don't know how to do is to have the macro that has to run on the sheet created by the script.
View 9 Replies View RelatedI've written some code that fires on a worksheet_change event. It worked fine for the initial bit of code I wrote, but then once I repeated it for several different ranges, I got an error stating COMPILE ERROR: PROCDEURE TOO LARGE. (This amount of code below works fine, it's only when I add the rest it becomes too large.)
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect Sheet
ActiveSheet.Unprotect Password:="clemson"
'Freeze screen
Application.ScreenUpdating = False
'Turn Off Auto Calc
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
' Master Bath Shower Listello
If Not Intersect(Target, Range("Listello1ShowerMasterBath")) Is Nothing Then
If UCase(Target.Value) = "NONE" Then
Range("Listello_1_Options_Shower_Master_Bath").EntireRow.Hidden = True:
Range("Listello_2_Shower_Master_Bath").EntireRow.Hidden = True:..........
I need a macro that will create a popup message any time 1) a new worksheet is created and 2) a worksheet is copied. The contents of the message left aside (use "message" as our example message)
View 9 Replies View Relatedi need to create an user form that creates an ordered list when an input for the starting number, the ending number, and the increment is entered by the user. the ordered list must also start in the specified cell that is entered by the user. i understand how to design the user form but the coding necessary for making the macro baffles me.
View 4 Replies View RelatedWhen ever I add a secondary axis to my chart my bar chart sub type changes to a stacked bar chart. How do I keep the sub type bar chart? I have attached an example. I have tried playing with the gap and width of the bars but to no avail.
View 3 Replies View RelatedI have a Pivot table.
Layout:
Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: $Amount
"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"
The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.
Now.... the trouble...
I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "$0.00" since there are no "Revenue" or "Expense" entries.
How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?
Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies View RelatedI'm am running Excel 2003 SP2 on Windows XP SP2.
I have a macro which modifies cells in a spreadsheet. But for brevity sake, I have an empty spreadsheet which has an Auto_open macro and two subroutines in it. The subroutines initially consist of just the Sub and End Sub statements.
The Auto_open procedure reads in lines from two text files (generated by another application), and inserts the lines into each of the empty subroutines. Auto_open then runs the two subroutines.
I have been able to get this dynamic creation of the subroutines to work for one subroutine, but not for the second. I receive the compile error: "Only comments may appear after End Sub, End Function, or End Property". I don't see anything wrong with the inserted code.
The modified subroutines appear fine, and if I save the macros with the modified code, close excel, rename the text files so they are no longer read in, and re-open the spreadsheet, the auto_open procedure and the two subroutines run fine. This tells me the code itself is okay, and yet it won't work during the initial run.
Below is the macro code in its original state:
Sub Auto_open()
Dim fso, f
Dim VBCodeMod As Object
Dim LineNum As Long
Dim StrFileName As String
ShowVisualBasicEditor = True
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
End Sub
----------------------
The contents of the read in file, %TEMP%subA.txt, is just one line:
MsgBox("inside SubA")
The contents of the read in file, %TEMP%subB.txt, is just one line:
MsgBox("inside SubB")
------------------------
When I open the spreadsheet the first time, it gives me the first message box from Sub A, but then generates the error and highlights the Sub SubB() line.
To duplicate the problem:
1. Insert the macro into a spreadsheet. Save and exit it.
2. Create the files %TEMP%subA.txt and %TEMP%subB.txt containing the single MsgBox lines.
3. Open up the spreadsheet. SubA will run and a message box will appear. Then the compilation error will occur.
4. Save and exit the spreadsheet.
SubA will now contain:
Sub SubA()
MsgBox("inside SubA")
End Sub
SubB will now contain:
Sub SubB()
MsgBox("inside SubA")
End Sub
5. Rename the two text files, so that the next time you open the spreadsheet it won't try to insert the lines from the files into the subroutines.
6. Re-open the spreadsheet. Two message boxes will now appear, one from SubA and one from SubB.
I am trying to use a macro that creates sheets and places formulae in cells but the resulting formulae are not what I coded. Here is similar
Function CreateSheet(i As Integer)
Dim CellAddress As String
Set Adjustment = Worksheets("Sheet1").Cells(6, 3 + 4 * i)
Celladdress = Adjustment.Address(ReferenceStyle:=xlR1C1)
With Selection
.Offset(22, 9).Formula = "=Sheet2!B14*Rating!" & CellAddress
.Offset(23, 9).Formula = "=Sheet2!C14*K4*Rating!" & CellAddress
.Offset(24, 9).Formula = "=Sheet2!D14*K5*Rating!" & CellAddress
End With.............
I'm trying to create a spreadsheet that has a data validation in it. I have a drop down list that has to options to select; Yes and no. Every time I create a new row to add more information I have to go through the process of creating a new data validation for for each cell in this coloumn.
Is there anyway of creating a way that every time I insert a new row, a data validation list box will appear in the coloumn that I want it in?
Sample Workbook: datavalhelp.xls
I need to add to a current macro which currently copies the master tab and creates a new tab with tomorrow's date on it. For reference here is the current macro:
View 3 Replies View RelatedI need a formula that will add .1, .2, .3, etc. to a repeating list number. See the example below.
Project Work Id
8069000
8069000
8069000
8069000.1
[Code]....
I have a list of pricing and I want to be able to change the text of cell, say "D1", which says "Standard" to say "Premium" and all the formulas would change in accordance. The "Standard" pricing would be, Starting D2, "=C2*.3" and I want them all to go to "Premium" pricing which would be formula "=C2*.35".
What would be the best method of creating this function or formula?
how to create a formula that creates an average but excludes the top 10% and bottom 10% values?
View 5 Replies View RelatedB20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.
If i change the sheet name below, B21:F21 would also automatically change.
I am having an issue with Excel creating a duplicate file when logged in as a user with restricted permissions on our network. If I modify a file while logged in as another user, excel will save the file but will also create a duplicate file with an eight character random name and no file extension. For example if user "Dave" opens a file called test.xls and saves it, in that same folder on the server a file named 296E9E20 will be created. Users are using Windows XP SP3 boxes and the server is running Win 2003. I am not sure why this only happens with Excel.
View 3 Replies View RelatedWhy when I drag the dotted blue page break line does it sometimes break the entire doc into one page per cell ?
The doc is not wide. When I first load I can drag the break line successfully. Then I print preview... select print on both sides... boom.. goes from 4 pages to 14. Then I go back to page break view... drag the line... boom... Hundreds of pages. Even if I revert back to printing on one side it still is messed up.
How do I make this stop?? What am I doing wrong?? Office 2010
Macro to copy data from Excel sheet and creates a Pipe delimited text file.
View 10 Replies View RelatedI am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
Here's what I'm attempting to do: For each column, X,Y, Z, I am attempting
to count nonblanks. However, the data was imported from Access and Oracle,
and Excel treats what appear to be blank cells as nonblanks. I've tested
this theory by highlighting a couple of "blank" cells and deleting them, and
my count changes. So, can I get Excel to put a value into my "blank" cells,
so then I could filter it out, or create a formula that would only count
dates in my columns (which is what I'm after).
This is what I'm looking at:
A B C
1 2/4/2006 2/6/2006 ("blank")
2 ("blank") 12/13/2005 1/7/2006
3 2/20/2006 1/15/2006 ("blank")
In each column if I use a COUNTA I'll get a total of 3, instead of 2 for A,
3 for B and 1 for C.
I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.