VBA Excel
Posted by James Butler on January 19, 2002 6:14 PM
I have the following code in a macro that runs when the ctrl+i key combo is pressed. The problem is the SendKeys "{DOWN}", true method does not do anything.
Any suggestions?
Dim findTodayDate As Object, _
firstAddress As String, _
RunYear As String, _
RunMonth As String, _
RunDay As String, _
Today As String, _
count As Integer
RunYear = Right$(Year(Date), 2)
RunMonth = PadLeft(Month(Date), 2, "0")
RunDay = PadLeft(Day(Date), 2, "0")
' PadLeft is a general procedure in a standard module that attaches a zero
' on the left of month or day if they are a single digit.
Today = RunMonth + "/" + RunDay + "/" + RunYear
Windows("JANBIL2002.xls").Activate
Range("D1").Select
With Worksheets(2).Range("D1:AI1")
Set findTodayDate = .Find(Today, LookIn:=xlValues)
If Not findTodayDate Is Nothing Then
firstAddress = findTodayDate.Address
Do
Range(findTodayDate.Address).Select
Loop While Not findTodayDate Is Nothing And findTodayDate.Address <> firstAddress
End If
' *** Here is were the problem is this SendKeys method is not doing what it is
' suppose to be doing, which is make the selection go down a cell.
SendKeys "{DOWN}", True
End With
Posted by Robb on January 19, 2002 7:04 PM
James
I would never use SendKeys unless absolutely necessary.
Why not use instead:
Selection.Offset(1,0).Select
Any help?
Regards
Robb
With Worksheets(2).Range("D1:AI1") Set findTodayDate = .Find(Today, LookIn:=xlValues) If Not findTodayDate Is Nothing Then firstAddress = findTodayDate.Address Do Range(findTodayDate.Address).Select Loop While Not findTodayDate Is Nothing And findTodayDate.Address <> firstAddress End If ' *** Here is were the problem is this SendKeys method is not doing what it is ' suppose to be doing, which is make the selection go down a cell. SendKeys "{DOWN}", True End With
Posted by James Butler on January 19, 2002 7:08 PM
Thanks Robb, that solved the problem. This is a great bunch of people here and very knowledgeable. Glad I found this site.
Thanks again! James I would never use SendKeys unless absolutely necessary. Why not use instead:
With Worksheets(2).Range("D1:AI1") Set findTodayDate = .Find(Today, LookIn:=xlValues) If Not findTodayDate Is Nothing Then firstAddress = findTodayDate.Address Do Range(findTodayDate.Address).Select Loop While Not findTodayDate Is Nothing And findTodayDate.Address <> firstAddress End If ' *** Here is were the problem is this SendKeys method is not doing what it is ' suppose to be doing, which is make the selection go down a cell. SendKeys "{DOWN}", True End With
