'End(xlDown)' acting weird

Question

I suspect that the .end(xlDown) is acting a bit weird.

Dim rfound As Range

Set rfound = Columns("B:B").Find(What:=Me.ComboBox1.Value, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

If ComboBox1.Value <> "" And WorksheetFunction.CountIf(Range("B:B"), _
ComboBox1.Value) > 0 And rfound.Offset(0, 1).Value <> "" Then
rfound.Offset(0, 1).End(xlDown).Offset(1, 0).Value = TextBox1.Value

Upon CommandButton1_click, the code will search in the column B for any matched criteria and then OffSet to the right cell provided my conditions are all met. However, it prompt me a message Run-time error '1004': Application defined or object-defined error.

I have no clue where is the problem. For illustration:

enter image description here

By using .end(xlUp), refer to my last image above, if I want to insert 'Product' and 'Description' into 'Company A', it is not achievable. — 4 Leave Cover, Dec 29 '12 at 09:40
Well you edited your question after we had pointed out what was wrong with your code - we can hardly guess you were going to modify beforehand. If you expect ongoing assistance then I think its reasonable that you treat those who help you appropriately. — brettdj, Dec 29 '12 at 10:35
I guess I shouldn't have edit my question so eagerly. I will change back my question to the state where it is left without edit. Yes indeed I should revise myself when asking any question, thanks for the reminding @brettdj — 4 Leave Cover, Dec 29 '12 at 16:10

score 9 · Accepted Answer · answered Dec 27 '12 at 13:12

9

Your current code

Presumably finds B2 as rfound (Note: It would be better to test if rfound exists after the Find with `If Not rfound Is Nothing Then)
Then C2 is rfound.Offset(0, 1)
rfound.Offset(0, 1).End(xlDown) find the last cell in column C as all other cells are blank
rfound.Offset(0, 1).End(xlDown).Offset(1, 0) tries to enter a value in the cell one row below the very last row - no can do.

Look up from the bottom instead, ie rather than

Then rfound.Offset(0, 1).End(xlDown).Offset(1, 0).Value = TextBox1.Value
use
Then Cells(Rows.Count, rfound.Offset(0, 1).Column).End(xlUp).Offset(1, 0) = TextBox1.Value

answered Dec 27 '12 at 13:12

brettdj

54,857
16
114
177

Do you have time to look at this issue in depth? I may post my code here if you want. Thank you. – 4 Leave Cover Dec 27 '12 at 13:51
Yes Sir, I did. But there is another part of my code that will insert another NEW data into column B,C and D that uses `.end(xlUp)`. – 4 Leave Cover Dec 27 '12 at 14:22

score 1 · Answer 2 · answered Dec 27 '12 at 15:24

My three cents...

The beauty is not in writing complex codes but breaking it up in easy to understand lines so that it is easier to understand what the code does. Also it helps in debugging it if an error occurs...

You already know that you have to write to Col C so why make it more complex by using Offset
Test if rfound exists as brettdj suggested
Use xlUp to find the last row.

See this example. (UNTESTED)

Sub Sample()
    Dim rfound As Range
    Dim lRow As Long

    With ThisWorkbook.Sheets("Sheet1")
        Set rfound = .Columns(2).Find(What:=Me.ComboBox1.Value, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)

        If Not rfound Is Nothing Then
            If ComboBox1.Value <> "" And _
            WorksheetFunction.CountIf(.Range("B:B"), ComboBox1.Value) > 0 And _
            rfound.Offset(, 1).Value <> "" Then

                '~~> Find the next available row in Col C
                lRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1

                '~~> Directly write to the cell
                .Cells(lRow, 3).Value = TextBox1.Value
            End If
        Else
            MsgBox "Not Found"
        End If
    End If
End Sub

`lRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1` will look for the last cell before blank in that column. Which I do not want this to happen. — 4 Leave Cover, Dec 29 '12 at 04:59
In that case, you can use `xlDown` but ensure that you do a proper error handling to check if you have not reached the end of rows which is what is happening at the moment. :) — Siddharth Rout, Dec 29 '12 at 05:16
Yes in the end I did manage to solve my problems thanks to all the ideas given by you all. Once again thank you very much! — 4 Leave Cover, Dec 29 '12 at 05:35

'End(xlDown)' acting weird

2 Answers2

Linked