Search week number in column, move to next empty cell

[davedave24]

Hi, I'm trying search for a number (week number) in column A, then move to the next empty cell below that. I have the code but it's malfunctioning at the search point.

This is the function to determine the week number based on textDate. This works fine.

Public Function WeekNumberFromDate(DT As Date, StartDate As Date) As Long

WeekNumberFromDate = Int(((DT - StartDate) + 6) / 7) + Abs(Weekday(DT) = Weekday(StartDate))

End Function

When I hit the Enter button, that function is called and the week number is found and loaded into the integer WeekNumber (this particular function uses the first monday of the year as the start of week 1). This works fine, I've tested with a msgbox and it finds the right week number.

WeekNumber = WeekNumberFromDate(textDate, "04/01/10")

I then run the routine FindWeekNumber, which is from [link="[URL unfurl="true"]http://www.rondebruin.nl/find.htm"[/URL]]this page[/url]. CustomerName is a string based on the customer's name selected from a combobox (it then switches to the sheet named after that customer. This bit works fine)

Sub FindWeekNumber()
    'search Column A of the worksheet for the week number determined by the date in textDate
    Dim Rng As Range
    
    If Trim(WeekNumber) <> "" Then
        With Workbooks("All Deliveries 2010").Worksheets(CustomerName).Range("A:A")                 'select column A in the sheet named after the customer
            Set Rng = .Find(What:=WeekNumber, _
                            After:=ActiveCell, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then                          'if a positive result is returned (the week number has been found)
                Application.Goto Rng, True                      'set the active cell on the week number we searched for
                ActiveCell.End(xlDown).Select                   'find next empty cell below
                ActiveCell.Offset(1, 0).Select                  'move down 1 cell
                ActiveCell = textDate                           'write the data
            Else
                MsgBox "Nothing found"                          'do stuff here to add the week number grid
            End If
        End With
    End If
End Sub

The problem is that it moves to the wrong week number. If it's 38, it goes to 39; 39 goes to 40. Also if you enter the same date twice, it will overwrite, instead of going to the next row.

Essentially - 1) search column A for WeekNumber; 2) move the next empty row below

 

[SkipVought]

Hi,

Are you saying that you have a column of data that has EMPTY CELLS between other data in that column?

Like the empty 'cell' between 5 & 6 in the example below?
[tt]
3
4
5

6
7
[/tt]
How about posting an example your the data you're dealing with?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davedave24]

Hi, sorry, yes the column has empty cells, and dates already entered.. For example, in column A, the week numbers:

Row5 : 38
Row6 :
Row7 :
Row8 :
Row9 : 39
Row10: 28/09/10
Row11: 28/09/10
Row12:
Row13: 40
Row14: 05/10/10
Row15: 07/10/10
Row15: 41

As you can see there are 3 possiblities:
the next empty row after the week number is empty;
the next empty row after the week number is not empty - insert new row (will deal with that later)
the week number does not exist in the column - insert week number and copy the layout from another sheet (will deal with that later)

Right now I just need to find the week number, and go to the next empty row below it

 

[SkipVought]

YUK!!!

Get rid of your empty rows!

Structure your data as a Table (2007 & later) or a Data > List (2003 & earlier).

Add your new rows at the bottom in a new row. All your formulas will propogate to the new row! The simply sort your data into date sequence.

No VBA needed!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davedave24]

edit: nevermind guys, I have something that works. Thanks

Sub SearchWeek()
    Dim R As Range
    Dim C As Range
    Set R = Workbooks("All Deliveries 2010").Sheets(CustomerName).Range("A:A")
    Set C = R.Find(WeekNumber, , xlValues)
    With C
        Application.Goto C, True
        ActiveCell.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell = textDate
    End With
    MsgBox C.Address
End Sub

 

[SkipVought]

Just trying to help point out what can be annoying and sometimes fatal errors, that can be avoided by using Excel as it was designed to function.

Tables are designed to contain ONE kind of data, not multipes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}