Error 1004 HELP

W

woody334

I have included a snippet. The line in question is throwing a
application or object defined error. Basically, the code should get th
last row in the worksheet and stuff a formula referenceing a cell i
that row in a different cell in another worksheet.


THNextRow = Worksheets("Total_hardware").Range("D65536").End(xlUp).Ro
+ 1

LinkText = "=" & "Total_hardware!" & THNextRow & ",3"

' the line below throws the error

Cells(1, 2).Formula = LinkTex
 
T

Tom Ogilvy

You have left out a single quote after hardware.

also, the ,3 on the end would make the formula invalid.

TheNextRow would just be a number, so you have no column Reference

If you build a legitimate formula, it should work.

Dim rng as Range
Set rng = Worksheets("Total_hardware").Range("D65536").End(xlUp)(2)
Cells(1,2).Formula = "=" & rng.Address(External:=True)
 
W

woody334

This line will produce a row NUMBER
THNextRow = Worksheets("Total_hardware").Range("D65536").End(xlUp).Ro
Click to expand...
+ 1

Understood. Works great. I fill that row with new info. Each lin
contains standard info about hardware.

Cells(THNextRow, 1).Value = TB_Serial.Value
...etc
Cells(THNextRow, 18).Value = CB_Location.Value

Then I create a new sheet to detail software and hardware changes we d
to that hardware. The detail sheet should pick up the serial number an
name from the Total_Hardware sheet to avoid problems - like differin
names when we change machine names.

The suggestion to do

Dim rng As Range
Set rng
Worksheets("Total_hardware").Range("D65536").End(xlUp).Row(3)


produces a 451 Error on the 2nd line. Property Let procedure no
defined and property get procedure did not return an object.

I appreciate the help and look forward to solving this
 
T

Tom Ogilvy

I can assure you that

Dim rng as Range
set rng = _
Worksheets("Total_hardware"). _
Range("D65536").End(xlUp).Row(3)

will not return an error if properly applied within an excel module.

Your error messages certainly sound peculiar.
 
W

woody334

Whoops my mistake.

Dim rng As Range
Set rng
Worksheets("Total_hardware").Range("D65536").End(xlUp).Row(3)

is NOT the same as

Dim rng as Range
Set rng = Worksheets("Total_hardware").Range("D65536").End(xlUp)(3
 
W

woody334

It's working !!!

Thanks for the help and please excuse my obvious oversight re : erro
451
 
Top