Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
I have a sheet containing details of cattle.
Column A has the eartag, columns C, D and E have the date moved on, off and death. Columns I and J have a date range (two months). I want to check whether: 1. Date on falls within the date range, put result in column K 2. Date off falls within the date range, put result in column L 3. Date of death falls within the date range, put result in column M I have managed to do it using the following but is there an easier way, possibly not involving entering formula. Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set movedon = .Range("C2:C" & Range("A" & Rows.Count).End (xlUp).Row) Set movedoff = .Range("D2:D" & Range("A" & Rows.Count).End (xlUp).Row) Set died = .Range("E2:E" & Range("A" & Rows.Count).End (xlUp).Row) For Each cell In movedon cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-2],RC[-8] <=RC[-1]),""Yes"",""No"")" Next cell For Each cell In movedoff cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-3],RC[-8] <=RC[-2]),""Yes"",""No"")" Next cell For Each cell In died cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-4],RC[-8] <=RC[-3]),""Yes"",""No"")" Next cell ..Range("K2:N" & Range("A" & Rows.Count).End(xlUp).Row).Copy ..Range("K2").Activate Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Range("A1").Activate End With Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
Gareth,
You could use conditional formating to color the cells in K, L, & M by just using your formulas to set the conditions. Set it up once and forget it. No formulas needed in the cells. And it will work on the entire column. steve "Gareth" wrote in message ... I have a sheet containing details of cattle. Column A has the eartag, columns C, D and E have the date moved on, off and death. Columns I and J have a date range (two months). I want to check whether: 1. Date on falls within the date range, put result in column K 2. Date off falls within the date range, put result in column L 3. Date of death falls within the date range, put result in column M I have managed to do it using the following but is there an easier way, possibly not involving entering formula. Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set movedon = .Range("C2:C" & Range("A" & Rows.Count).End (xlUp).Row) Set movedoff = .Range("D2:D" & Range("A" & Rows.Count).End (xlUp).Row) Set died = .Range("E2:E" & Range("A" & Rows.Count).End (xlUp).Row) For Each cell In movedon cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-2],RC[-8] <=RC[-1]),""Yes"",""No"")" Next cell For Each cell In movedoff cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-3],RC[-8] <=RC[-2]),""Yes"",""No"")" Next cell For Each cell In died cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-4],RC[-8] <=RC[-3]),""Yes"",""No"")" Next cell .Range("K2:N" & Range("A" & Rows.Count).End(xlUp).Row).Copy .Range("K2").Activate Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Range("A1").Activate End With Application.ScreenUpdating = True End Sub Thanks in advance. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
Gareth,
To put "Yes" or "No" in the cells you'll need formulas. To use one color for Yes and another color for No you can use conditional formating. Of course you can use a worksheet change event to evaluate you're entries and enter the text. But you'll still need to put the formulas in the code. steve "Gareth" wrote in message ... Steve I wasn't clear enough in my first post, K, L and M have to be 'Yes' or 'No'. Gareth -----Original Message----- Gareth, You could use conditional formating to color the cells in K, L, & M by just using your formulas to set the conditions. Set it up once and forget it. No formulas needed in the cells. And it will work on the entire column. steve "Gareth" wrote in message ... I have a sheet containing details of cattle. Column A has the eartag, columns C, D and E have the date moved on, off and death. Columns I and J have a date range (two months). I want to check whether: 1. Date on falls within the date range, put result in column K 2. Date off falls within the date range, put result in column L 3. Date of death falls within the date range, put result in column M I have managed to do it using the following but is there an easier way, possibly not involving entering formula. Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set movedon = .Range("C2:C" & Range("A" & Rows.Count).End (xlUp).Row) Set movedoff = .Range("D2:D" & Range("A" & Rows.Count).End (xlUp).Row) Set died = .Range("E2:E" & Range("A" & Rows.Count).End (xlUp).Row) For Each cell In movedon cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-2],RC[-8] <=RC[-1]),""Yes"",""No"")" Next cell For Each cell In movedoff cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-3],RC[-8] <=RC[-2]),""Yes"",""No"")" Next cell For Each cell In died cell.Offset(0, 8).Value = "=IF(AND(RC[-8]=RC[-4],RC[-8] <=RC[-3]),""Yes"",""No"")" Next cell .Range("K2:N" & Range("A" & Rows.Count).End (xlUp).Row).Copy .Range("K2").Activate Selection.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Range("A1").Activate End With Application.ScreenUpdating = True End Sub Thanks in advance. Gareth . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |