Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select Range Code
Hi All,
I have the following in my sheet column A Date Column B code Column C thre EP3 time 00:00 thru 23:50 in 10 Minutes interval My user from has date, time from, timeto, type I am updating the data from a userform Range("A65536").End(xlUp).Offset(1, 0) = TxtDate Range("A65536").End(xlUp).Offset(0, TxtArvCol) = TxtCraType Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor In my above code i want make a modification to get the following: 1) check the date (columnA) and if the the date is the same for the next entry instead of adding a new record i want have the interior color in the same row. 2) if there is already an interior color code in any of the cell in the range Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)) then I have to add a new record and not in the same row. I hope I am clear in my explanation. Thanks Soniya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select Range Code
Soniya,
Not sure what you mean, with the colors, but you can use code like that below to find if the date has already been entered into your data table. HTH, Bernie MS Excel MVP Sub TryNow() Dim myCell As Range Dim myRow As Long On Error GoTo NoMatch: myRow = Application.Match(CLng(DateValue(TxtDate)), Range("A:A"), False) Set myCell = Range("A" & myRow) myCell.Offset(0, TxtArvCol) = TxtCraType Range(myCell.Offset(0, TxtArvCol.Value), _ myCell.Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor Exit Sub NoMatch: Range("A65536").End(xlUp).Offset(1, 0) = TxtDate Range("A65536").End(xlUp).Offset(0, TxtArvCol) = TxtCraType Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), _ Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor End Sub "Soniya" wrote in message oups.com... Hi All, I have the following in my sheet column A Date Column B code Column C thre EP3 time 00:00 thru 23:50 in 10 Minutes interval My user from has date, time from, timeto, type I am updating the data from a userform Range("A65536").End(xlUp).Offset(1, 0) = TxtDate Range("A65536").End(xlUp).Offset(0, TxtArvCol) = TxtCraType Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)).Interior.ColorIndex = TxtColor In my above code i want make a modification to get the following: 1) check the date (columnA) and if the the date is the same for the next entry instead of adding a new record i want have the interior color in the same row. 2) if there is already an interior color code in any of the cell in the range Range(Range("A65536").End(xlUp).Offset(0, TxtArvCol.Value), Range("A65536").End(xlUp).Offset(0, TxtDepCol - 1)) then I have to add a new record and not in the same row. I hope I am clear in my explanation. Thanks Soniya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to select range | Excel Discussion (Misc queries) | |||
VBA Code to Select Range | Excel Programming | |||
Select a range in code | Excel Programming | |||
select range from code | Excel Programming | |||
How do I select this range using code? | Excel Programming |