Active Cell and Cell Offsetting Problem

[Peter Daniels]

Hello,

I am trying to wirte a simple code that takes a cell value in a sheet, then checks if the active cell is equal to that, and if not offsets it down the column, and checks again until it is equal. After that the active cell is offset to the right and contents from another sheet pasted over the current data, however I have encounterd a problem.

The code as it is:

Sub WriteData()

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("Employee Records").Activate

Range("B3").Select

Do

If (ActiveCell) = "Data Editor!D5" Then

ActiveCell.Select

Else

ActiveCell.Offset(1, 0).Select

End If

Loop Until (ActiveCell) = "Data Editor!D5"

ActiveCell.Value = xStaffNumber

ActiveCell.Offset(0, 1) = xSurname

ActiveCell.Offset(0, 2) = xForename

ActiveCell.Offset(0, 3) = xTelephone

ActiveCell.Offset(0, 4) = xJobLevel

ActiveCell.Offset(0, 4) = xBaseHours

ActiveCell.Offset(0, 5) = xBaseRate

ActiveCell.Offset(0, 6) = xExtraHours

ActiveCell.Offset(0, 7) = xExtraRate

Application.ScreenUpdating = True

End Sub

When I run it I encounter errors about a application or object defined error. Anyone know what is causing this and I can fix the code to get it working?

Thanks

[mangesh_yadav]

Instead of using just ActiveCell, try referencing it with its parent as follows:
Workbook.Sheets("Employee Records").ActiveCell

- Mangesh

[Leith Ross]

Hello Peter,

The most likely cause of run time error 1004 "Application or Object defined Error" is the program is unable to match the cell contents "Data Editor!D5". If no match is found you search the next column, and eventually run out of columns to search. Excel always uses A1 style referencing when refering to other worksheets or workbooks. Change the search parameter to "Data Range!$D$5". I assume "Data Range" is in the same workbook.This should cure the problem.

Hope this helps,
Leith Ross

[tony h]

A couple of observations:
1. rather than looping through each of the ceels activating them using a search maybe preferable eg:

ActiveSheet.Range("B:B").Find(What:="Data Editor!D5", LookAt:=xlWhole, After:=Range("B2"), MatchCase:=False).Activate

2. Your code suggests that you are looking for the value "Data Editor!D5" not to the contents of the cell. Do refer to the contents of the cell use:

Range("Data Editor!D5")

[Peter Daniels]

Many thanks for the help, I have decided to use a searching method as the reason I used the first method was that I wasn't sure what the code was for a search.

My code now reads:

Sub WriteData()

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("Employee Records").Activate

ActiveSheet.Range("B:B").Find(What:=Range"Data Editor!D5", LookAt:=xlWhole, After:=Range("B2"), MatchCase:=False).Activate

ActiveCell.Value = xStaffNumber

ActiveCell.Offset(0, 1) = xSurname

ActiveCell.Offset(0, 2) = xForename

ActiveCell.Offset(0, 3) = xTelephone

ActiveCell.Offset(0, 4) = xJobLevel

ActiveCell.Offset(0, 4) = xBaseHours

ActiveCell.Offset(0, 5) = xBaseRate

ActiveCell.Offset(0, 6) = xExtraHours

ActiveCell.Offset(0, 7) = xExtraRate

Application.ScreenUpdating = True

End Sub

But I now get the error

Run Time Error 91

Object Variable or With Block Variable not set

I am not incredibly expeirnced in VB coding so I am not sure what has gone wrong with the search function.

[tony h]

A couple of problems.
1. What:=Range"Data Editor!D5",
needs to have brackets as in
What:=Range("Data Editor!D5"),
. I presume you must have had the brackets in to get it to run in the first place and get the error.

2. you will get an error 91 if there is no match. To handle this you will need to set up a variable to hold the result of the search as follows:

Sub WriteData()
Dim r As Range

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("Sheet1").Activate

Set r = ActiveSheet.Range("B:B").Find(What:=Range("Sheet2!D5"), LookAt:=xlWhole, After:=Range("B2"), MatchCase:=False)

If r Is Nothing Then
MsgBox "Search failed"
Else
r.Activate
'now do all your stuff

End If

End Sub

[Peter Daniels]

Thanks for clearing that up, but I have unfortunately encountered another error my code now reads:

Private Sub WriteData()

Dim r As Range

Application.ScreenUpdating = False

ActiveWorkbook.Sheets("Employee Records").Activate

Set r = ActiveSheet.Range("B:B").Find(What:=Range("Data Editor!$D$5"), LookAt:=xlWhole, After:=Range("B2"), MatchCase:=False)

If r Is Nothing Then
MsgBox "Search failed"
Else
r.Activate

ActiveCell.Value = xStaffNumber

ActiveCell.Offset(0, 1) = xSurname

ActiveCell.Offset(0, 2) = xForename

ActiveCell.Offset(0, 3) = xTelephone

ActiveCell.Offset(0, 4) = xJobLevel

ActiveCell.Offset(0, 4) = xBaseHours

ActiveCell.Offset(0, 5) = xBaseRate

ActiveCell.Offset(0, 6) = xExtraHours

ActiveCell.Offset(0, 7) = xExtraRate

End If

End Sub


And I get the error;

Runtime 1004

'Range' of object '_Global' failed

And th search line highlighted. I cant figure out why the code isn't working, it does as far as I can see search column B downwards looking for cell D5 in the other sheet.

Thanks.