I am going through a large block of data with formulas, where I am selecting each cell and manually highlighting the formula and pressing F4 to sorround the cell letter calls with $, example $A$11. I am doing this so I can ultimatley copy the formulas as a group into another group of cells with screwing them up. This is very time consuming so I am looking for an improved method, possibly using vb code?
View 9 RepliesHow to get a Splash to show on WB open without seeing the Excel worksheets briefly flash before the splash loads.
(Note you will still see the Excel loading window prior to the splash appearing but I don't worry about that).
At my company I work with a lot of text-filled Excel files. In order to get a proper word count we're currently using the primitive method of "paste into Word and run a word count". I'd like to be able to do this from within Excel, and I found the macro below but have run into a snag.
The problem I have is that it ignores carriage returns and count the last word in the first line and the first word in the following line as one word. In the files I work with this can throw off the count by thousands of words. So if I run this macro against a cell containing a single text string:
<My name is Amejin and
I like pizza and beer> - 10 words
The macro reads it as:
<My name is Amejin andI like pizza and beer> - 9 words
So, essentially I need a method of changing carriage returns into spaces so that the macro will return the proper count.
I have a range of purchase order rows, with the formulas stored in the first row (TemplateRow) which is hidden. The users may add any number of rows to this range, depending upon the number of different products being purchased.
Code:
'Copy the template row into the first newly inserted row
Rows(TemplateRow).Copy Destination:=Rows(insertionPoint)
'fill down from the inserted row down to the last new row
With Rows(InsertionPoint & ":" & NumberOfInsertedRows.Rows.Hidden = False
.FillDownEnd With
The problem is copying the formulas down to the new rows can take terribly long (minutes) in scenarios of thousands of products. Is there a faster method of copying down my formulas?
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
View 4 Replies View RelatedI am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies View RelatedI'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
Is it possible to hide formulas from the formula bar while still having the formulas active?
View 8 Replies View RelatedI have a spreadhseet with various functions on it and what I am trying to do is this.
Cell E4 returns a >35 or <35 true or false value
Cell G4 is either blank or has "Yes" text type into it.
What I am trying to do is get cell F4 to return certain arguments.
E4 = >35 and G4 is blank I want it to state "Email Hiring Manager"
E4 = ,35 and G4 is blank I want it to state "Wait"
I have a basic IF formula that returns this
=IF(E4>35,"Email Hiring Manager","Wait")
Then if cell G4 is populated with a Yes the formula needs to overwirte the origonal if with the return arguments of
=IF(G4="Yes","Email Agency","Email Hiring Manager")
If yes then what would be Email Hiring Manager (yes will only be input if E4 is greater than 35) will be overwritten with "Email Agency"
Can this be done with two If formulas or does there need to be 3 or more to count if other IF formulas are actually returning a value?
If you have a cell with the value ="2*c2+3" NB: (Notice the ""), then to make excel convert the formula in another cell to =2*c2+3 (notice the removal of ""), so that it can calculate the value of the cell instead of showing a textstring?
I have formulas in a column and they are working unless I edit them to include another function, more cells, whatever, then they display as formulas instead of the result. I've gone to Tools --> Options --> View and the Formulas box is not checked. As well automatic calculation is on not manual.
View 4 Replies View Relatedwith opening a workbook at a specific time and/or running a macro at a specific time.
View 12 Replies View RelatedI realy don't understand the FileExists method at all. My VBA Editor doesn't seem to recognise the keyword FileExists ?
Allot of examples will write FileExists(Dir(sFilePath)) in their explanation but I don't know what the Dir is referring to. Does this mean the directions to the file or is this referring to the directory and Dir is actually a keyword for direcory?
I don't think I've found a single simple example which explains this properly?
I have some code which renames files. The key line being
View 2 Replies View RelatedIs there a way to use the find method on 1 row only?
View 2 Replies View RelatedI'm having some trouble fully understanding the Intersect Method. The help files use examples that check if a range Intersects another, if the do then display a message box; I think I understand that.
The usage I don't understand is like this
Set MyRange = Range("C:C", "D:D", "E:E")
Code here
Intersect(CopyRange, MyRange).copy
I am trying to copy some code from the Excel help directory but I am getting an error saying "Compile Error, Can't find project or library".
What am I missing here?
fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
MsgBox "Save as " & fileSaveName
End If
What's the easiest way to return the address of a cell without the dollar signs?
Example:
msgbox activecell.Address
' Returns $D$8
I'd like to see the address written as "D8" because I will be autofilling later.
I am trying to invoke an excel VBA macro from excel from a button or in some
way directly by the user (that is by not having to invoke macro from excel menu)
that can be invoked from a Macintosh or PC version of excel. That is, it should
not used active X controls but in some other way that is best practice. Is there
any way to invoke a macro by button or menu item that is cross-platform?
I havent work with excel for years and now I have to do this as soon as possible:
There is a Donto's formula for calculating votes.
I have a conditional formatting or some other method (open to suggestions) which will identify those line items which need his attention.
I'm attaching a spreadsheet. Note that my items which I'd like flagged are not necessarily one right after the other in the column - i.e. there will be some which are skipped and not needed to be 'rated'.
I have a range object representing a data set, and I want to return only the first row of data from the range. The range starts at cell A2:
21 22 23 24 25
31 32 33 34 35
41 42 43 44 45
I.e. the value 21 is the data in cell A2 (row 2, column 1) and so forth.
'Given that WS1 is a worksheet object representing Sheet 1
'Given that WS2 is a worksheet object representing Sheet 2
I am running a vba code to add about 200 sheets...my code generates a sheet in one file and then pastes it in another file....after generating nad adding 38 sheets i get the error copy method of excel failed
i tried clearing the clipboard and resuming the code but that didn,t work
I am trying to sum the cells in a column from a variable starting row to a variable ending row. My problem is that I can not figure out how to use the "Range" method with variable values.
Below is what my code would look like if I was summing from I2 to I6 (hard coded)
I have a working macro that duplicates the active worksheet with values only. It basically cleans out formulas and data validation from the data. The code is:
View 2 Replies View RelatedI need a macro to run at a certain time on Wednesday's, and then a certain time on the other days.
I would like to use a cell to reference that time, but right now, I am just hard coding it, but I an not get it to run.
Ultimately what I am looking for is something like this: ....