I basically need to switch between ranges of cells to data input alot. so i thought of making a fixed field to enter the data which transfers the data over to designated cells .
Attached is an example.
I am not too sure which one will work, the If statement captures the data but when i switch out , the entry is gone .
I think Match has trouble finding a value thats within a cell containing a lot of words.
Heres the example. I'm trying to find the word "Hello world" in cell B1, in Column A.
Column A
1 Lots of Text
2 Text Text. Hello world. text text
3 Text Text
Cell B1
Hello world
Column B2
Match("*"&B1&"*",A:A,0)
When theres only 1 sentence in Cell A2, the function works and returns 2.
However, when Cell A2 contains a few paragraphs the function returns #NA.
I don't necessarily need to use Match, if this is indeed a limitation does anyone of suggestions to work around, other than vlookup?
I have a column of data, sometimes there are 20 entries in the column, sometimes there are 700. I want a sum of the totals of that column after the last entry in that column. It changes every hour, up and down, so I can't just have a set cell. Is there a way to find the last entry in that column and sum up all the previous cells in that column and put the total at the end?
Does that make sense? If it were a set amount this would be easy.
I've tried setting a range, but when I equate the solution I get an error. Says it's out of range.
I have two vertical ranges that I need summarized into 2 adjacent vertical ranges.
"
A B C D
| SUMMARY
model qty| modelqty
1 4.12922.0000| 4.12952.2000
2 2.000012.1250| 2.000025.1250
3 4.12929.0000| 318.0000
4 318.0000|
5 4.1291.2000|
6 213.0000|
"
A1:A6 is my SKU's model number B1:B6 is my inventory C1:C6 should contain formulas that result in a summary of the models D1:D6 should contain formulas that result in a sum of the inventory count for each model
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
I have a macro that turns off filtering option when it runs. What I want is for to first warn the user that rows have been filtered and the Macro will not run, then once the filters are switch off, the macro will run. It does the last bit at the moment but switches off the filtering of protected cells and once the macro has run the sheet can no longer be filtered .
Sub NewRowSQT()
Dim ans As String
ans = MsgBox("Ensure you have selected a cell in column B immediately below the section you wish to add to or the Macro will FAIL!", vbYesNo)
[Code]....
I have a list of 20,000 addresses and because of the way the sheet gets populated by different departments, the COUNTRY CODE and POSTAL CODE columns get their data reversed.
Instead of 48193 US, it gets put in as US 48193.
Since i have some many, and they are not in order at all (I could probably sort by COUNTRY CODE and get 95% of the in order (except for those pesky Canadian codes), is there a macro way out of this?
Even if I select two adjacent cells and run the macro and have the contents switch. Or if there is a way to do the 95% all at once.
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
I have data in columns A to Z. Column A has the name of Bonds and columns B to Z have different returns for those funds. I would like to create a scatter plot chart that allows me to switch which column to pull the data from in addition to column A which will always have the name of the Bonds.
For example in addition to the name in column A I would like the chart to plot data from Columns C and F on the x and y axis respectively.
I'm thinking of copying column A to a new tab and then having a drop down box in columns B and C to copy over values from the 2 columns I select. How can I get a drop-down box to copy over values from another column?
I have a sheet full of time series data arranged as:
col A: data, col B: date, col C: data, Col D date, etc......
I would like to construct a macro to switch the order to Date, Data (col A: datE, col B: datA, col C: datE, Col D datA, etc.)
Is there an efficient way to do this without having to loop through N columns with repeated copy-paste commands?
how to do some complex stuff in Excel but sometimes I don't know how to do some really easy stuff. After I have named a data set of say a certain ten cells in one column, is there a function or a way to input the name and have that data populate a different ten cells? For instance if I have a huge amount of data all coded and I want to take equal but different portions and run them through a template, how can I make it so I just have to enter the a code for any of the data I have coded and have it populate momentarily where ever I need it to go? I'm sure this is very easy to do unless I have explained it inadequately.
View 7 Replies View RelatedI am looking for the formulae in E9.
I may change the value in E8 as 1.5 or 2.5 or 4 or 6 I may scrolldown C9 as it set out there. I need to know the farmulae in E9.
I have a spreadsheet that has a couple of columns that ask for "Move In Date" and the other "Move Out Date". These dates are used in other calculations so there can be only one or the other in each row. I have users that mistakenly either leave both blank or both popluated. Is there a way to stop them with a message telling them that a date needs to be entered or deleted, maybe with data validation?
View 1 Replies View RelatedI have a spreadsheet whee teammates can inut the date in one cell and the exact time in another cell - how do I lock the cell with the time so it can't be changed?
View 1 Replies View Related I'm trying to create financial statements on a sheet with quarterly and annual data. Is it possible to create like a button to change inputs on a cell
Income Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com (2).jpgIncome Statement for Abercrombie Fitch Company Class A ANF from Morningstar.com2 (2).jpg
whats the best way to insert a button like in the jpg example
for example i have this data in rows B and D:
austriaaustria
icelandaustria
austriaiceland
austriagermany
icelandaustria
germanyiceland
germanygermany
germanyiceland
germanyiceland
germanyiceland
germanyiceland
germanyiceland
austriaiceland
austriaiceland
and need to input in rows A (date in this example) and C(number in this example) which are adequate for each country so to look like this in rows A,B,C,D:
19 20 austria1,25austria
20 30 iceland1,25austria
19 20 austria2,2iceland
19 20 austria6,6germany
20 30 iceland1,25austria
22 00 germany2,2iceland.......
I would like to create an spreadsheet that prompts me to input data into a cell (eg; 'C1' - 'Quantity Ordered') as soon as another cell on the same row becomes populated (eg; 'A1' - 'Item Code'). I need this function to apply for several rows (25 rows in total), so that if data is then entered into cell 'A2', I get a prompt to enter data into 'C2', all the way up to a prompt for data in cell 'C25' when cell 'A25' is populated.
As it is imperative that I never forget to populate the relevant cells; I would also like to 'enforce' the prompt - perhaps by preventing the worksheet from being saved until the relevant cells have had data entered into them, or perhaps by using some kind of form, rather than a messagebox.
I want to pass an array to a function, and am unclear how to do it. For example, the following declaration does not work:
Public Function xyz(a() as Variant) as Double
There is an additional requirement that I would like the output also to be a range of the same dimensions, typically one column and 100 rows. Is this possible?
A further twist is that the function I am writing calculates two values for every element in the input array and I need the output of them both.
An inelegant solution would be to write two separate functions, but I was wondering if it is possible to get two array outputs from a single function.
I have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I have a worksheet that I will be allowing other to access so that I can get them to input certain data. I will be protecting the sheet to avoid having them inadvertantly overwrite any of my formulae. So, I have a matrix on the left for them to input data into with calculations to the right that they cannot change. Can I prevent them from moving cells within the input matrix
View 1 Replies View RelatedI'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.
I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.
Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.
I have a VBA function with the header:
cubspline(Xval As Double, XRange As range, YRange As Range) As Double
The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.
I would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.
NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached
What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies View RelatedI have been working on my spreadsheet for sometime now, so far when I run into a code problem I can figure it out using someone eles's post. However, I can't seem to figure this one out. I need to send data from a userform to specific cells on my spreadsheet based upon the users selection in combobox 1, and textbox 1.
Example: User selects customer name from Combobox1, and part number auto loads into textbox1 from the data sheet.
There are then 11 combobox's that can be clicked as the userform is updated. Once the user is finished, I need the answers from each combobox to transfer to the worksheet next to the referenced Combobox1 and textobox1.
I used the code that RoyUk posted to him, but have only been able to get the first combobox to copy to the sheet, the rest stay blank.
(Here is the code so far)
Private Sub CommandButton2_Click()
Dim ce As Range, srcRng As Range
Dim sYear As String, sMonth As String
sYear = UserForm3.ComboBox1.Text 'When combobox1 is loaded, use as reference#1
sMonth = UserForm3.TextBox1.Text 'When textbox1 is loades, use as reference #2
Set srcRng = Range("c2", Range("c65536").End(xlUp)) 'Search range on worksheet
For Each ce In srcRng
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedSo I have a macro that colors the selected cells in a certain color. Now im looking for a code that will switch that color every 7th cell, the cells will only be selected horizontally. For example:
if the selected range is 10 cells, 7 of them should be in one color and 3 of the should be in another. But if the selected range is 15 cells the first 7 should have one color and the next 7 should have another and then the last one should have the first color again, and so on..
I have a user form that has several tab pages on it when the form opens it checks to see if the user has entered there default info if it is empty it needs to go to the default tab so the user can enter there info. How do I do this using vba in excel 2000 I have not been able to find any code that will do this if I use the set focus option for a text box on the tab page it comes up with an error saying the item is not visible! The user forme is names = UserDataInputForm. The tab pages I have are
1st = InputDailyTimesPage
2nd = OverTimePage
3rd = DataPage
4th = DefultDataPage
the form opens using the 1st tab page by default
in the user forms inisilisation code I have this check