Find data in a column or insert it and adding time in the same row at a specifik date
Posted by Asta Beason on May 22, 2001 11:33 AM
Maybe this is a too big question- but I´m trying it anyway since I want to learn more!
I have in an example created a list in workbook A.
In workbook B I have created a userform where you can add a date a number of hours and in a listbok choose an item from the list added from workbook A.
When i click Insert in this userform I would like VBA to do following.
1. Search column if the chosen listitem is registered, if not I want to add a new row and put the list item in the new row.
2. When I find the item or add it, I want to put in the hours registered in the userform on the date chosen in the userform. Everything added in a form of schedule where the date is the columlabel and the hour should be written where the item and the datecolumn meet.
Is this even understadable.
If anyone atleast would like to give me a hint how to start (the userform and so on is finished it is 1 and 2 that is my problem) I would be ever so grateful!!
Asta
Posted by Dave Hawley on May 22, 2001 1:55 PM
Hi Asta
This code looks for the ListBox item in Column A and the date heading in Row 1. Then places the hours at the intersection of the Listbox Row and the date Column.
Private Sub CommandButton1_Click()
Dim rFound As Range
Dim sLookFor As String
Dim iCol As Range
sLookFor = ListBox1.Value
With Sheet1
On Error Resume Next
Set rFound = .Range("A:A").Find _
(What:=sLookFor, After:=.Range("A1"), LookIn:=xlWhole)
If rFound Is Nothing Then
Set rFound = .Range("A65536").End(xlUp).Offset(1, 0)
rFound.Value = sLookFor
End If
sLookFor = Textbox1.Text
Set iCol = .Rows(1).Find _
(What:=sLookFor, After:=.Range("A1"), LookIn:=xlWhole)
If iCol Is Nothing Then
MsgBox "Cannot find date heading"
Exit Sub
End If
End With
Intersect(rFound.EntireRow, iCol.EntireColumn).Value = Textbox2.Value
Set rFound = Nothing
Set iCol = Nothing
End Sub
Dave
OzGrid Business Applications
