'shift Cells Right' Depending On Value Of Cell

Jan 4, 2010

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.

View 9 Replies


ADVERTISEMENT

Cut / Paste And Shift Cells Depending On Value In A:1

May 11, 2006

I am trying to make a macro to take a set of data and shift the data range down depending on the value in the A:1 cell. For example if the data in column A starts with 5, 6, 7, 8 and so on.... I would like to copy/ paste and shift the column down 5 cells so I can start A:1 at 0.....so that the data can begin as 0,1,2,3,4,5.....etc.

View 3 Replies View Related

Shift Cells Up If Cell Equal 0

Dec 27, 2008

In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.

Here is the code I tried, but nothing happened:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub

View 9 Replies View Related

Delete And Shift Cells Based On Cell Values

May 23, 2007

I have a spread sheet that has RX codes as field headers and ones or zeros as values. I want to find all cells which contain "0" and delete those cells while shifting the remaining cells left. The range of cells is aj1:dr17365 and the sheet name is "May 07 Rx HCCs"

View 8 Replies View Related

Insert Formula Depending On Cells Depending Of Variables?

Jan 27, 2014

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.

View 3 Replies View Related

Highlighting Cells Depending On A Value In Another Cell

Aug 19, 2006

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 ****

View 9 Replies View Related

Listing Cells On Another Worksheet Depending On Cell Value

Jan 7, 2009

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

View 3 Replies View Related

Restrict Cells Depending On Input In Another Cell?

Jan 28, 2013

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 Related

Locking Cells Depending On What Is Entered In Another Cell

Sep 22, 2009

I'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 Related

How To Change A Cell Colour Depending An A Different Cells Value

Oct 22, 2009

How 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.

View 14 Replies View Related

Blank Out Certain Cells Depending On Cell Contents?

Oct 21, 2013

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.

View 9 Replies View Related

VBA-change The Colour Of 4 Cells Depending On The One Cell

May 20, 2009

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

View 9 Replies View Related

Code To Move The Cells Right Depending On The Value On One Of The Cell

May 4, 2007

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 Replies View Related

Lookup Wage Calculations (calculate Pay Per Shift Dependant On The Type Of Shift)

Dec 1, 2009

I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.

I have attached a screen shot of the current page,

In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.

The shift pay is the column i am having trouble with.

I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.

View 3 Replies View Related

Calculate Pay For Shift Work With Different Rates Based On Shift Hours

Apr 11, 2008

a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!

examples:

start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333

start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000

start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000

Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................

View 8 Replies View Related

Inserting A Numerical 1 Or 0 Into A Cell Depending On The Values Of Two Other Cells

Jul 24, 2008

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

View 11 Replies View Related

Cell Color Fill Depending On Condition Of 2 Other Cells

Jun 23, 2009

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.

View 3 Replies View Related

Count Cells Depending Upon Multiple Cell Values

Aug 2, 2007

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

View 9 Replies View Related

Insert Cut Cells - Paste And Shift Cells Down

Aug 18, 2014

Any shortcut for "insert cut cells"? I want to paste and shift cells down instead of pasting over existing data.

View 1 Replies View Related

Delete Cells And Shift Up

Nov 7, 2009

I have data in cells A1:FM1326 I want to delete all of the cells that say FALSE and shift the remaining cells up.

View 5 Replies View Related

Delete Cells, Shift Everything Else Below It Up

Feb 6, 2007

I have a problem in making a delete function/sub and i'm applying it to a listbox in userform ... i'm trying to delete a row of data in the listbox which refers to cells (A12:D12) ... and at the same row (row=12), there are other data cells (E12:H12) ... after deleting the cells all other data below the deleted cells will shift up taking over the deleted cells ...

View 9 Replies View Related

Insert A Value And Shift Cells Value Down

Feb 6, 2009

If I put a length in (G13) such as 4.44 & position from (H13) is 1. Then I want that length of (G13) to move its value (4.44) into (C15) the 1st cell to add to is (C15)

Then Move (C15) to (C16) 11.12 becomes C16
and move (C16) to (C17) and so on all the way down the C column.

When it hits the last joint (C374) position 360. I want a messageBox that says you can not insert anymore.

Basically shift the existing values down untill (C374)
I have only column C to shift the calue from (G13)

I do NOT want to delete rows!

Below is how it looks before macro:
GH12LENGTHPOSITION134.441

C14LENGTH1511.121612.221711.561811.861913.112012.872113.062211.992312.03

Below is how it will look after macro: The RED moves to (C15)
C14LENGTH154.441611.121712.221811.561911.862013.112112.872213.062311.992412.03GH12LENGTHPOSITION134.441..........................


View 9 Replies View Related

VBA To Shift Cells To The Left

Apr 30, 2009

how can i get this

Sheet1

ABCDEFGHIJKL7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N

View 9 Replies View Related

Shift Cells Down Depends On Condition Of Others

Oct 20, 2009

Whenever "online" appears in the "Desc" column I need to shift the matching row down (shaded area). for example, "online" appears in H2, hence, C2:E2 need to be shifted down to C3:E3 leaving C2:E2 blank. Next, H3 has "online" there, C3:E3 need to be shifted down one row so the result will look like the data on the "result" tab.

View 5 Replies View Related

Shift Cells Up A Row To Align With Column Using VBA

Jan 13, 2012

I need to shift all the cells in columns B, C, D, E & F up a row to align with the names in column A.

I have code to delete the empty rows.

Start:

Bank Holiday 1 on a Week End

ABCDEF1YASMIN AKHTAR (YA1) 2 59038.753MARINA BELAIDI (MB2) 4 12325.55MARGARET CANT (MC6) 6 33002.25

Result Required:

Bank Holiday 1 on a Week End

ABCDEF1YASMIN AKHTAR (YA1)59038.752 3MARINA BELAIDI (MB2)12325.54 5MARGARET CANT (MC6)33002.25

View 3 Replies View Related

Delete Range Of Cells And Shift Up

Jan 30, 2013

I am trying to delete a group of four cells and have the remaining block shift up. My code is below and crashes on the red. What is the right code to get this to work?

Code:

Do
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = Range("G1").Value Then
Range(ActiveCell:ActiveCell.Resize(0, 3)).Delete Shift:=xlUp
Exit Do
End If
Loop

View 1 Replies View Related

VBA Insert Copied Cells Shift Down

Sep 25, 2013

I'm using a macro to copy cells (2 columns and between 1 and 5 rows [19 - 23 on s/s]) from one workbook to another but if I use

Rows("2:2").Select
Selection.Insert Shift:=xlDown

then every cell in the inserted rows are populated.

this is what I currently have

Windows("agent master.xlsm").Activate
Range("B19:C23").Select
Selection.Copy
Windows("distribution master.xlsm").Activate
Sheets("L38").Select
Range("A2").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False

but that doesnt work either. It just gives me an empty cell in A2.

View 1 Replies View Related

Delete Row Macro (shift Cells Up Then)

Jul 1, 2007

I am looking for a macro that can delete whole rows (shift cells up then). How i would like it to perform is if there is nothing in cell(s) B then delete the whole row?

View 9 Replies View Related

Insert-shift Cells Down Macro

Jan 29, 2009

I want to be able to insert-shift cells down based on criteria in another cell. For instance, I would like a macro that would look at column B for saturday and sunday and then insert-shift cells down on the corresponding cells in column D...

View 9 Replies View Related

Make Cells Shift Down Automatically

Jun 18, 2009

i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved