Error 1004: Activate Method of Range class failed

[Radnanigan]

Hello,

VBA newbie here. The goal of this script is to locate missing data, take user input, and copy a subsequent row of data on another sheet. When I test the script by inputting the missing inventory, I am facing two problems:

1. The MissingDate variable becomes "12:00 am" for its respective cell on the "User Input" page (least important problem).
2. The 1004 error occurs at the second Cell.Select section and does not allow the script to finish the copying function at the end of the For loop.

I suspect the 1004 error is being caused by the "Worksheets("Data").Activate line but I cannot understand why. Can someone please explain?

Thanks for your help!


Sub MissingInput()


Dim UserInputInv As Variant
Dim MissingInv As Variant
Dim MissingDate As Date
Dim DateRange As Range


Worksheets("User Input").Activate
Set DateRange = Range("B16:B128")
DateRange.Select


For Each Cell In Range("B16:B128")
If Cell.Value = "" And Cell.Offset(RowOffset:=0, ColumnOffset:=-1).Value < [Today()] Then
Cell.Select
ActiveCell.Offset(RowOffset:=0, ColumnOffset:=-1).Select
Cell.Value = MissingDate
Do While True
UserInputInv = InputBox("Enter inventory for " & MissingDate)
If IsNumeric(UserInputInv) Then
MissingInv = CDbl(UserInputInv)
Exit Do
End If
Loop
ActiveCell.Offset(0, 1).Value = MissingInv

Worksheets("Data").Activate
Range("A16:A128").Find (MissingDate)
Cell.Select
Range(ActiveCell.Offset(-1, 2), ActiveCell.Offset(-1, 3)).Select
Selection.Copy
ActiveCell.Offset(0, 2).PasteSpecial (xlPasteValues)
End If
Next

Worksheets("User Input").Activate
Range("C11").Select




End Sub

 

[footoo]

Selection of a cell can only be done on the active sheet.
Your variable "Cell" is on sheet "User Input".

Try changing this :

Range("A16:A128").Find (MissingDate)
Cell.Select

To this :

[COLOR=#333333]Range("A16:A128").Find (MissingDate)[/COLOR][COLOR=#333333].Select[/COLOR]

It would be better to have your variable as "cel" instead of "Cell".
Also, use Option Explicit to ensure all variables are declared

 

[Fazza]

Welcome,

Suggest (1) you read up on debugging, and (2) you always use 'option explicit'. This can be set in the VBE: tools, options, require variable declaration.

Option Explicit enforces variable declaration.
if you type option explicit at the top of your module and then compile (ALT-D-L) with the posted code then "Cell" is not declared.
"Cell" is a poor name. I've replaced it by a new name, declared as DIM rngInputCell As Excel.Range

Stepping through the code using F8, "MissingDate" is never assigned a value.
So that is why you're seeing the value you're seeing.

Then rnginputCell (your Cell) is a cell in the range "B16:B128" on worksheet "User Input".
When worksheet "Data" is activated, you get the error 2 (your message) because that cell can't be activated when a different worksheet is active.

The code is probably very close to working. Just keep going, it gets easier to code with practice.

regards, Fazza

 

[Fazza]

PS. Be careful using .Find because it remembers some settings & these can cause the .Find to not work as you expect when you don't explicity make the settings what you want.

That won't make much sense. But say the last time find was used it had to find the whole, item. Say, "apple". Now you have a cell "pineapple" and search for apple, it won't be found. Or the other way, it does find pineapple when you only want to find (whole word) "apple" & no partial finds. HTH

 

[Radnanigan]

Thanks Fazza!

I've made the Option Explicit and Dim rngInputCell changes,however, I'm still having troubles. Perhaps more clarification is needed.
The For loop is intended to locate a date where there is noinventory and continue the user-input process in chronological order if thereare other similar dates. Ideally, theuser-input process would require inventory input for the “User Input” sheet andstore the no-inventory date (as a Date data type – “MissingDate”?) to locatethe same date on the “Data” sheet.
The last section would use the “MissingDate” variable toimplement the Copy and Offset functions on the “Data” sheet. If I assign a value to “MissingDate” (e.g.9/1/18), then that date is used to implement the Copy scheme, instead of theno-inventory date.
How do I assign the no-inventory date as “MissingDate” to beused in the “Data” sheet?
Thank you so much!

 

[Fazza]

The code wasn't go to work once the original questions were answered. Just that the questions would be answered.

From a quick reading of the further description, what is wanted is not clear. I'll have a look again when I have some more time. It may be next week. If I don't understand what is required, I may post again.

Suggest you do some debugging in the meantime & fix what you can.

And you'll find you'll get better assistance if you post more information - like sample data that can be copied & posted into Excel. Then what is wanted will be easier to understand than it is currently. If it isn't really easy for people then you really limit your audience of helpers.

regards