CODEWITHSUNDEEP
vbaexcelmacros
JayF
JayF

Reputation: 11

VBA 1004 error pulling data from other sheet

Bit of a strange Object/User Defined 1004 error that I'm having.. maybe I'm just overlooking something.

I'm looking to implement a code very similar to one that I have used on another sheet. Essentially the sheet is a dashboard, the only cell used by the user is "b1" in the activesheet as this allows the user to choose from a dropdown menu of choices. Upon the change the information should reference ClientDB sheet to find the correct row, then pull the information from it.

Ive used very similar code previously with no hitch.. Just wondering if there is something I may be overlooking?

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DBWS As Worksheet, cws As Worksheet, sws As Worksheet
Dim aa As Integer, bb As Integer, cc As Integer, dd As Integer

Set DBWS = ActiveWorkbook.Sheets("ClientDB")
Set cws = ActiveWorkbook.Sheets("ClientBoard")

aa = DBWS.Columns("a").Find(what:=cws.Range("b1").Value, after:=DBWS.Range("a1")).Row
bb = Range("a" & aa).Row


'contact
Range("b3").Value = DBWS.Cells(bb, "H").Value
'number
Range("b4").Value = DBWS.Cells(bb, "I").Value
'email

Range("b5").Value = DBWS.Cells(bb, "J").Value

'etc.. etc.. etc..

Extra Note: If I run an Error Handler to bypass the 1004 error it will eventually populate after a few errorhandler cycles

Any help would be greatly appreciated!

Thanks!

Jay

Upvotes: 1

Views: 65

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149325

As @SickDimesion mentioned, you have to change Columns("a") to DBWS.Columns("a:a") or to DBWS.Columns(1)

After doing that change, you may still get an error because, you are directly trying to get the row. What if the .Find didn't return a match?

Consider changing your code to this

Dim aCell as Range

set aCell = DBWS.Columns(1).Find(what:=cws.Range("b1").Value, _
                                 after:=DBWS.Range("a1"))

If Not aCell Is Nothing Then
    aa = aCell.Row
Else
    Msgbox "Value Not Found"
    Exit Sub
End If

Upvotes: 1

SickDimension
SickDimension

Reputation: 912

Consider DBWS.Columns("a")... change to DBWS.Columns("a:a")...

ais not a valid reference.

Upvotes: 1

teylyn
teylyn

Reputation: 35970

You're not qualifying the context for

bb = Range("a" & aa).Row

Excel does not know which sheet this Range refers to. Same for the other unqualified Range statements.

Upvotes: 0

Related Questions