I have a worksheet with running macros which hides & unhide rows based on input of data in yellow highlighted cells (see attached). I'm trying to make all cells mandatory. The below code works but I run into problems when certain cells made mandatory are hidden. For example if I select "Expense" as Type of Expenditure in the attachment cell C10 & C11 are hidden so if its mandatory I won't be able to move forward to next cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Me.Unprotect "dawnwh81"
Dim myCell As Range
Dim myRange As Range
I am currently trying to adapt the following code which is in VBA (Sheet1). It currently ensures mandatory cell entry of cell C2 before saving, and if this is not done then it would not save the item into the relevant directory. I would like to adapt this code so that cells B2 to R2 are all mandatory, and if they are not filled in a message box with the cell title (which would be in B1 to R1) would pop up. If the cells are not filled in then it will not save (the same function it carries out for cell c2 currently). The code is attached to a command button, i have tried to make the additional cells mandatory by playing around with the If Trim(.cells(2, 3).value) part of the code with the AND function etc however i have had not had any luck.
My requirement is something like this. I have a workbook where end-users can fill data and send it to our system. They might enter any rows of data, it can be just 1 or even 100. I want to force users to fill in values in few mandatory fields : columns in B,C,D,E. Now I need a VBA code that should throw a message when it finds that the value in cells B,C,D,E are blank.
Since I am not sure how many rows of data the user can fill, I dont know how to code in VBA the above scenario. I cant give a range as the total rows of input data is not available and static value.
We have spreadsheets which record our sales team's prospects and orders. In order for the sales director's summary spreadsheet to be correct, the month which the order came in has to be filled in and correct.
What I would like to be able to do is when column K of a line is turned to "100 - Purchase Order In", a message to pop up saying something along the lines of "Is the Month In correct?"
Looking through previous answers, I think that this is something along the lines of what I need:
Sub Print_Out()
With Range("d6") If .Value = "" Then .Select MsgBox ("Make sure you enter your surname") Exit Sub End If End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub
However, I don't know enough about VB Code to know how to change it for my purposes...
I trying to require data entry in Cell 2 if Cell 1 contains text. Normally Cell 1 is blank but once the user enters data (its a text field) I want Cell 2 to then become mandatory for data entry. (Cell 1 is actually C5 and Cell 2 is actually DK5. So formula I am thinking if is: If C5 contains data then DK5 must also contain data. If C5 is blank, the DK5 is also blank.
I am working in excel and for some reason when I hit the enter key it stays in the same cell. I never had this problem before, what did I do and how do I fix it?
Inputting a value in a cell and need to use vba to transfer it to another sheet in the same workbook. Need it to go to the first empty cell in a column upon clicking a button.....then change the value in the original sheet and repeat (upon clicking button again).
how to filter rows of data, in ascending or descending order, whilst still retaining formatting (in my case cell borders) and conditional formatting?
At present, when I filter rows in ascending / descending order, the cell formatting & conditional formatting stays in it's original position, rather than moving with the cell. I can't find a way to resolve this.
I have a cell the has the date and time in this format 2/23/2009 8:11:00 AM; I need to get the time into another cell where I can then use another formula to see how often a persons name shows up within a particular time range, like =COUNTIFS(DATA!$G:$G,Sheet3!$A2,DATA!$N:$N,">=18:00,
I have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red blue yellow green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
Source tab contains vital information about some clients.
In the aggregated tab (Cell C10) I created a formula that pulls the Inflows from the source in a very specific array. So for client 1, this works fine. Now, if i copy my formula to the client 2 (Cell C14), it obviously wont go and look in the correct array in my source.
What i need to do is to be able to copy/paste my formula
[Code].....
(from cells C10 to CC10) to cells C14 to CC14, but when copied, the look up array changes to:
Formula: [Code] ....
I will have to fill this formula to at least 100 entries down, so i need to make it work with ease
The good thing is that all look up values in the source increase by a fixed number of rows (12). I tried playing with index/rows formula.. no luck..
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data A001
Data A002
Add next number (A003) when data entered in cell to left
I want to colour in the relevant cells for each ref depending on the numbers in the stage column. So for the first line, the cells in columns 1,2,4&6 need to be shaded in. For the second line, the cells in columns 1,2,3,4,&5 need to be shaded in. I already have a lot of conditional formatting set on the sheet so need to do this with a macro.
I have tried to write something but am having issues with it finding the 1 and shading in the correct column but then jumping out of the loop and on to the next cell.
I am looking for advice on how to move cells containing data (columns D to K in EXAMPLE A), upwards so that the information lines up with data already existing in columns A to C (to be included as part of an overall VBA routine).
In the upper example data in cell D2 needs to move upto D1, E3 to E1, F4 to F1, G7 to G1 etc and K9 to K1. Now, data on following rows is to move upto row 2 eg: E5 to E2, F6 to F2, G8 to G2.
The first 'block' of data starts at row 1 and finishes in this case at row 9. The next 'block' of data starts at row 10 and finishes at row 18. Row 19 shown is the start of the next 'block'. These 'blocks' may have upto 20 rows ....
I would like to auto populate a cell (A2) due to the input results from another (A1). For example, if I enter A1 with "100%" then I want A2 to auto populate with "Complete". And just the same for other percentages; if A1 was input with any range from 1% to 99% it would populate A2 with "In Progress". And if A1 was to equal 0% then A2 would be "Not Started".
i'm trying to ask my spreadsheet to fill a cell with either 'YES' or 'NO' depending on the value of one cell. I've succeeded in getting it to enter 'YES' but can't figure out how to tell it to choose between the two options. This is the formula so far
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
I have 3 columns Z, AA, AB. The heading for Z1 is “A”, AA1 is “B” and AB1 is “C” In column Z2:Z2000, there is a mix of A, B’s and C’s. I want A to stay in Z1 column, B’s to goto AA1 and C’s to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button
Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to “Dec (today’s date) DB (81).xls” The number 81 is the total count of A, B’s C’s, this # will change depending on how may A, B’s and C’s there are. I really hope there is a way of doing all this
Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.
Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).
The commas are not necessary, but is there any way to get this done other than manually entering them?
I was able to input barcode reader data into A1, and automatically get the date inserted into B1. I wish the next activecell to be C1, instead of A2 as the above code is doing at the moment - so how do I alter the code above to make this happen?
I have cut and pasted a large amount of data onto a spreadsheet. The problem is that I have two rows of data that is associated with one another, so lets say A1 has a name and A2 contains that person's age. This process repeats in the same way in A3 and A4 and so on.
I need to move the data from A2 to B1 without using a simple function like =A2 in B1. The reason is that I will need to sort just the Names in column A once I get the the data moved. I am not sure how to do this.
I need to move data within a cell. Is there a formula that can accomplish this? Here is an example of what I'm trying to do..
Column A / TN14YP244f / TN15YP245j / TN16YP246h
What it would need to do is take the last character in each cell and move it to replace the P in that cell. It should also capitalize the letter. ie: / TN14YP244f would become / TN14YF244
The letters at the end of the cell are always different, however the letter they replace is always the same (P).
My direction keys currently move the screen instead of move from one cell to another. For instance, I will have cell A1 highlighted and then press the down key. Instead of going to A2, it stays at A1 but the screen moves down one cell
The attachment below is a basic example of what I am trying to accomplish. I am looking for VBA code to take data from E3 in "Historical1" sheets and move it to down into the table in column E that corresponds to the date in D3. The other "Historical" sheets will work the same way. Cells D3 and E3 in each of the "Historical" sheets equal back to cell on the "Entry" sheet. This is a very basic example of the ultimately bigger data base I am building. why I am utilizing a entry sheet to elevate the need to go to each sheet and enter data.
