what is wrong with this code in that it works fine in one workbook, but gives me 'run-time error '1004' in a differeent workbook?

[kbishop94]

I have a "test workbook" that I use when I'm trying to figure out how to make something work... then after I fiddle with the code enough to (finally) get it to work the way i need to, I copy it over to my main workbook.
Well, i have checked every conceivable thing that I thought it could be (property settings, names, workbook settings, etc) and it still wont work in my main one(?)

Here is the code:

Private Sub CommandButton1_Click()


Dim fCell As Range
Dim ws1 As Worksheet
Dim vMax As Variant
Set ws1 = Worksheets("Sheet1")


[COLOR=#008000]' sets a range for column "A" on worksheet1 starting at row 18 and going down to the last entry that is in that column[/COLOR]
For Each fCell In Worksheets("Sheet1").Range("A18", Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))


[COLOR=#008000]'finds the cell with the highest value in the previously defined range of 'fCell'[/COLOR]
vMax = Application.WorksheetFunction.Max(Range(Cells(18, 1), Cells(fCell, 1)))


Next fCell


[COLOR=#008000]'posts the highest value cell in fCell range and places it in a text field on the userform[/COLOR]
Me.txtIncidentID.Value = vMax


End Sub

This is the line that is snagging it up after I copy it over to the main workbook (if I temporarily take it out, it runs):

vMax = Application.WorksheetFunction.Max(Range(Cells(18, 1), Cells(fCell, 1)))

The code has no problem running in my 'test workbook':
Working just fine on my test workbook...

But i get this errror after copying and pasting into my other (main) workbook....

What do I have wrong that it wont run with that line of code?? THank you

Keith

 

[JLGWhiz]

With ws1
vMax = Application.WorksheetFunction.Max(.Range(.Cells(18, 1), .Cells(fCell, 1)))
End With

Range and Cells not qualified.

What is wrong with the site. This thread is fighting me to do the post?

 

[kbishop94]

With ws1
vMax = Application.WorksheetFunction.Max(.Range(.Cells(18, 1), .Cells(fCell, 1)))
End With

Range and Cells not qualified.

What is wrong with the site. This thread is fighting me to do the post?

Thank you!

Now, 2nd question... so why would it work in one workbook but not the other?

 

[JoeMo]

I'm surprised it runs in any workbook. This syntax doesn't look right:

Application.WorksheetFunction.Max(Range(Cells(18, 1), Cells(fCell, 1)))

as fCell is dimensioned as a range. Did you mean Cells(fCell.Row,1)?

 

[kbishop94]

I'm surprised it runs in any workbook. This syntax doesn't look right:

Application.WorksheetFunction.Max(Range(Cells(18, 1), Cells(fCell, 1)))

as fCell is dimensioned as a range. Did you mean Cells(fCell.Row,1)?

I'm not defending or arguing for whatever code that was that i posted. I am very much a novice (a self taught/learning) novice at VBA. I couldnt tell you why, or how that it works... but just so you know that I am not seeing things... here it is working lol


https://youtu.be/v1COyn2QJFE

 

[kbishop94]

How should that range be formatted in a correct way that will work in my other workbook

 

[JLGWhiz]

Now, 2nd question... so why would it work in one workbook but not the other?

If Range and Cells references are not quaified with their parent objects, then VBA will use the ActiveSheet as the parent. When you were in the other workbook, you oould have been running the code with the ActiveSheet being the one needed to make the code work right. Or you might have changed th code at some point since running it in the other workook. Hard to tell frm here.

 

[kbishop94]

I still cannot get the code (the range property) to work(?) How should it be written?

I tried this:

Did you mean Cells(fCell.Row,1)?

and no it doesnt work either. Im sure I just have something simple in how it should be formatted/written incorrect, but I cant see it(?) THanks