Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,441
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to select range Shawn Excel Discussion (Misc queries) 1 June 2nd 05 05:14 PM
VBA Code to Select Range BethB Excel Programming 3 June 1st 05 12:52 AM
Select a range in code JCanyoneer Excel Programming 3 December 20th 04 11:23 PM
select range from code Tommy[_8_] Excel Programming 1 September 2nd 04 12:12 AM
How do I select this range using code? TBA[_3_] Excel Programming 2 January 14th 04 10:28 PM


All times are GMT +1. The time now is 12:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"