Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
I am trying to use the code below to check dates in column C, D and E
against a date range in columns I and J and return "Yes" or "No" in column N. If C, D and E all contain dates it works fine, but there are some blank cells and cells containing the word "Error". Is there some way of checking whether there is a date in the cell before getting the result? For the cells containing "Error" I would like "Error" displayed in column N. Thanks in advance. Gareth Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row) For Each cell In rng If cell.Value cell.Offset(0, 7).Value Or cell.Offset(0, 1).Value < cell.Offset(0, 6).Value Or _ cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then cell.Offset(0, 11).Value = "Yes" Else cell.Offset(0, 11).Value = "No" End If Next cell End With Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
Not sure how to use these function's in my code, any help gratefully
received. Gareth "Orlando Magalhães Filho" wrote in message ... Hi Gareth, Did you already try to use IsDate() and IsError() function? HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Gareth" escreveu na mensagem ... I am trying to use the code below to check dates in column C, D and E against a date range in columns I and J and return "Yes" or "No" in column N. If C, D and E all contain dates it works fine, but there are some blank cells and cells containing the word "Error". Is there some way of checking whether there is a date in the cell before getting the result? For the cells containing "Error" I would like "Error" displayed in column N. Thanks in advance. Gareth Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row) For Each cell In rng If cell.Value cell.Offset(0, 7).Value Or cell.Offset(0, 1).Value < cell.Offset(0, 6).Value Or _ cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then cell.Offset(0, 11).Value = "Yes" Else cell.Offset(0, 11).Value = "No" End If Next cell End With Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with some code please
See this:
Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row) For Each cell In rng If IsError(cell.Value) Or Not IsDate(cell.Value) Then cell.Offset(0, 11).Value = IIf(IsError(cell.Value), "Error", "") Else If cell.Value cell.Offset(0, 7).Value Or cell.Offset(0, 1).Value < cell.Offset(0, 6).Value Or _ cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then cell.Offset(0, 11).Value = "Yes" Else cell.Offset(0, 11).Value = "No" End If End If Next cell End With Application.ScreenUpdating = True End Sub "Gareth" escreveu na mensagem ... Not sure how to use these function's in my code, any help gratefully received. Gareth "Orlando Magalhães Filho" wrote in message ... Hi Gareth, Did you already try to use IsDate() and IsError() function? HTH --- Orlando Magalhães Filho (So that you get best and rapid solution and all may benefit from the discussion, please reply within the newsgroup, not in email) "Gareth" escreveu na mensagem ... I am trying to use the code below to check dates in column C, D and E against a date range in columns I and J and return "Yes" or "No" in column N. If C, D and E all contain dates it works fine, but there are some blank cells and cells containing the word "Error". Is there some way of checking whether there is a date in the cell before getting the result? For the cells containing "Error" I would like "Error" displayed in column N. Thanks in advance. Gareth Sub CheckDateRange() Application.ScreenUpdating = False With Worksheets("Sheet1") Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row) For Each cell In rng If cell.Value cell.Offset(0, 7).Value Or cell.Offset(0, 1).Value < cell.Offset(0, 6).Value Or _ cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then cell.Offset(0, 11).Value = "Yes" Else cell.Offset(0, 11).Value = "No" End If Next cell End With Application.ScreenUpdating = True End Sub |
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) |