I have attached the sample file where I have noted what I want as a comment. I need to check for the value " Function Name: RF Pick" if this is there then the code should move 2 rows down check whether that row has the value " Each" if its there just move one row down and move the cells as I need in the output.
View 7 RepliesOne of the worksheets contains all of the data and the rest are empty. I need to do is move entire rows of from the main worksheet in to worksheets named the same as the value in column C of the row.
for example one row may have TEST in column C so i want to move it to the worksheet called TEST. The next row might have TESTING in column C so that would go to the worksheet called TESTING. and so on.
So, I just started checking Excel Macros. I'm working on a quite large data base where I need to search for a given part number and then move N columns to the right and display the text inside that cell on a message box.
Here's what I got 'til now:
Private Sub M0016216_Command_Button_Click()
Cells.Find(What:="M0016216", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Select
[Code] ........
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'm working with a column that contains dates. The dates are pulled from a system that doesnt always have dates in the format
mm/dd/yyyy => 09/26/2014
but instead sometimes
9/26/2014
or
4/7/2014
i.e., 0's are missing.
I'm looking for a macro that inserts these missing 0's
**So I suppose the criteria should be that if the cell is 9 characters long (including the "/"s), then it adds a 0 as first character**
and
***the cells that are only 8 characters, in that case there would have to be insert 0 again as character 1, and 0 as character 4 (if 0 is already added as first)***
I'll attach a file that contains dates : test.xls‎
I have an excel spreadsheet where column "D" will have one of fifteen different names inserted. According to which name is present, the cell needs to be a specific color. Any ideas on what would be the best code to use to read the entire column and format the cells that have names?
View 9 Replies View RelatedI m trying to bypass some code if a certain condtion is meet. If Cell A1 has a zero in it I want to skip the following code and continue one with the rest of the macro. This is the code I have.
j = 0
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
cell_a = Range("A1").Value
If Not cell_a = 0 Then
Rows("1:" & cell_a * 2).Select
Selection.Insert Shift:=xlDown
Range("A1").Select
For i = 1 To cell_a * 2
Range("a" & i).Select
ActiveCell.Value = j
j = j + 0.5
Next i
I put the If Not/Then statement in there to try and figure out what do. But I don't know what to put after the THEN statement. Basically I want run the code after the THEN statement if there isn't a zero in cell A1 and skip the code if A1 contains a zero.
code which highlights a cell depending on the criteria of another cell.
Example.
In B1 I have the formula =NOW() (we all now what this means).
In B2 I have a completion date.
I would like A2 to fill with a specified colour.
Can this be done so that it works down the whole sheet, if the date is in B5 then only A5 will highlight so basically only the A cell on the same row will change depending on the date in B on the corresponding row?
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
I have 'sheet 1' (data collection sheet) and 'sheet 2' (form filling sheet)
I've recorded a macro which sends data from 'sheet 2' to 'sheet 1' (linked to a submit button on 'sheet 2')
I want the macro to allow the next form filled information to be transferred onto the next line down on 'sheet 1'.
The current code is:
[Code] ......
I want code to move the cursor to A788
But keep A788 in the top left corner on screen.
Is there easy code to help me accomplish this?
Also, Is there code that will take you to the first cell it finds closest to today's date.
Dates are in Column B.g
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:
Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub
The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell:
Selection.Insert Shift:=xlToRight
Range("B2085").Select
ActiveCell.FormulaR1C1 = "Click for detail image"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Note that "B2085" above is the cell number that the script stops on to execute this code.
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), that’s all I’m interested in ( multiple locations).
What I’ve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 50). The warehouse runs up one aisle and then down the next and so on. So I need to be able to highlight cells up one column and then down the next and then up, then down and so on.
Multiple locations change on a daily bases, so need a quick way of updating 4000 locations.
New to excel don’t know if this is possible or not. Learning as I go.
Need some assistance.
Sample below.
Only interested if a number is returned, “******” can be ignored as will hide all columns expect ones with locations. So:
BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049.
BB780 returned value of 2, so would like to highlight BB780, BB789.
POSSIBLE?
BA090 2 BA100 **** BB749 **** BB739 ****
BA079 **** BA089 **** BB760 **** BB750 ****
BA070 **** BA080 **** BB769 **** BB759 ****
BA059 **** BA069 **** BB780 2 BB770 ****
BA050 **** BA060 4 BB789 **** BB779 ****
BA039 **** BA049 **** BB800 **** BB790 ****
BA030 4 BA040 **** BB809 **** BB799 ****
BA019 **** BA029 **** BB820 **** BB810 ****
BA010 **** BA020 4 BB829 **** BB819 ****
I have a list of names in column B and either a 1 or 0 in column A as below:
0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy
On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.
0 A
1 Bob
2 Billy
3
4
I have two cells, A1 = Yes/No and B1 = Date. I want to restrict someone inputting a date into B1 until A1 is marked as Yes...? I've tried data validation, but seem to be hitting a wall with it...
View 2 Replies View RelatedI'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)
View 5 Replies View RelatedHow to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I have attached a spread sheet.
I have a spreadsheet that can do more than one calculation on each row
ITEM
FLOW
(l/s)
[Code]....
The values in row A can only be selected from a named range drop down. If no entry is there (i.e. blank) then i'd ideally like the cells to return from black to white.
i need to change the colour of 4 cells depending on the one cell.
i have found some vba codes on the net but they are all based on numbers and i need it on text.
so if a1 ="alert" make A1 to A4 red
if a1="passed" make a1 to a4 green... and so on
i cant used conitional formatting as i need more than 3 colours
I'd like help writing a single formula that assigns either a numerical 1 or 0 to a cell based on values from two other cells.
By way of background, I'm working with three columns. Cells in column F contains values from 1 to 4. Cells in column G contain values from 5 to 11. Cells in column H are currently blank.
I need a single formula that will insert either a numerical 1 or a 0 into column H according to the following conditions.If F3 = 1 and G3 = 5 or 6 or 7 then let H3=1 otherwise let H3=0
If F3 = 2 and G3 = 7 or 8 or 9 then let H3=1 otherwise let H3=0
If F3 = 3 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
If F3 = 4 and G3 = 9 or 10 or 11 then let H3=1 otherwise let H3=0
Afternoon everyone i am having abit of trouble working on an excel 2007 spreadsheet. In cell I1 i have a tab called Color. i want the cells below I1 to be filled with one of 3 colors green, yellow, or red depending on whats in cell F "Status" (closed or open - in progress) and cell G ECD for estimated completion date.
Green - i need it to fill green if status is closed. Yellow - need it to fill yellow if ECD is any date greater than today and if status is open. Red - need it to fill red if ECD is todays date or older and status is open.
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,
Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000
I've been trying to figure out how to use a formula that would enable me to copy/copy & move a range of cells right one column if a certain criteria is met.
Basically, if a criteria is met, I want to 'copy' everything from a row to another row, but offset it by one column (so that what was in, let's say, a1 would be in b2).
I've been tinkering with index(), if() and offset() in various combinations but think I must be missing something.
I have come across a format issue I need to deal with it at work.
I have a list of data like this:
A
1234567
8956423
4561237
7531596
8524561
In Column A, I have about 2,000 lines of information. What we want to do at work is create this information to be held in one cell at the top of the excel and in numerical order.
Like this:
A
1234567, 4561237, 7531596, 8524561, 8956423.
I have the following code that will go down through column "L", look for the value "ATS", and if found will move it to the right 1 cell. this works find,
My need is to also move the 2 cells directly below the cell containing "ATS", then clear the contents of the "Original" 3 cells before going to find the next instance of "ATS".
Sub Move_Values()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("L4", Range("L5000").End(xlUp))
For Each cell In myrange
If InStr(cell.Value, "ATS") > 0 Then
cell.Offset(0, 1).Value = cell.Value
cell.ClearContents
End If
Next cell
End Sub
I have 2 col A & F. F col associate with date values & Col A has some color cells.I want to move down those color cells to a selected non-color cell row when run a macro.
I have code which perform to move color cells down to selected cell but problem is, if i have A4 & A6 color cells and i selected F2 cell & run macro then color cell A4 & A6 move down to cell A2 but cell A3 move to place A6. The sequence is
A1A2A4A6A5A3A7A8 & so on but i want cell A3 just push down the order & sequence will be
A1A2A4A6A3A5A7A8 & so on My code is as
Code:
Public Sub Move_colored_cells_rows()
' Instead of moving the entire row , 10 columns will be moved
Const NUMBER_OF_COLUMNS = 10
[Code]....
I would like to move the data in one column up by 1/2 a row without moving the other columns/rows in the spreadsheet
Here is an example I made quickly in Photoshop - the above image is what I currently have in Excel, and the lower image is what I would like to achieve, with the column shift that I would like circled in red
(the empty space under the column heading is just an artifact from the edit, it doesn't have to be there)
I have a cell comment on a series of columns and want to show only one column at a
time. For this reason I hide the ones not used, but the cellcomment doesnt move and
is still at the original place. How I move the comment when the original colums between are hidden ?
I have an excel file that contains data from bank transactions.
In column A at irregular intervals is a cell with " User Group:" It depends on how many transactions there are in a batch, could be 1 or up to say 200 as to when the next "User Group" cell is found.
After that also in column A is "Item" then "Lodgement Ref" then at the end "Batch Totals"
i.e.
User Group:
Item
Lodgement Ref
Batch Totals:
User Group:
Item
Lodgement Ref
Lodgement Ref
Lodgement Ref
Batch Totals:
I want to find each instance of "Item" then move that cell and every cell over to column CK down to and including "Batch Totals" i.e. A3401 to CK3410 to CM 3400 so it lines up with the top of the transaction block.
There are over 60,000 lines so it's worth trying to find a solution as I can then use the methodology to make other refinements in the spreadsheet.