0

(EDITED, SEE BELOW FOR PREVIOUS MESSAGE) Based on the linked posts and suggestion comments, I changed my code to include this line (I've tried with x1Up and x1Down)

Dim row As Integer
row = Worksheets("Quickbooks").Range("H2").End(x1Down).row
Worksheets("Quickbooks").Range(Worksheets("Quickbooks").Cells(2, "J"), Worksheets("Quickbooks").Cells(row, "J")).Value = 1

I'm getting an "Application-defined or object-defined error" now, and I'm not sure what is causing it. Do you have any ideas?

------PREVIOUS POST------------------------------------------------------------------------------------ I'm new to VBA and having a hard time with implementing the last cell/row/column functions in code. In this line of code I'm trying to delineate a range with one of these functions. I want the range to go from cell J2 to cell Jend. J is filled with just the value "1" and Jend is determined by column H. When column H has no more values, J should stop too. I get errors when I try this and other variations of it. How would you recommend fixing it/writing it? And, maybe more generally, any tips or rules of thumb for using these last cell functions in range declarations? Thank you!

Worksheets("Quickbooks").Range(Cells(2, "J"), Cells(Rows.Count, "H").End(xlUp).Select) = 1
CBlue
  • 25
  • 5
  • `Worksheets("Quickbooks").Range(Worksheets("Quickbooks").Cells(2, "J"), Worksheets("Quickbooks").Cells(Worksheets("Quickbooks").Rows.Count, "H").End(xlUp)).Value = 1` – BigBen Jul 19 '21 at 18:04
  • Or `With Worksheets("Quickbooks")`, `.Range(.Cells(2, "J"), .Cells(.Rows.Count, "H").End(xlUp)).Value = 1`, `End With`. – BigBen Jul 19 '21 at 18:16
  • @BigBen when I use your first suggestion, columns H, I, and J are set to 1, instead of just J...any idea why this might be? It's the only line of code that changes. When I remove it, to shows me my normal outputs... – CBlue Jul 23 '21 at 19:02
  • @BigBen I get the same issue when I try the With End With – CBlue Jul 23 '21 at 19:06
  • Yeah sorry I didn't actually read the body of your question. Use `With Worksheets("Quickbooks")`, `Dim lastRow As Long`, `lastRow = .Cells(.Rows.Count, "H").End(xlUp).Row`, `.Range("J2:J" & lastRow).Value = 1`, `End With`. – BigBen Jul 23 '21 at 19:07
  • @BigBen how would you do this without the with/end with statement? Thank you! – CBlue Jul 26 '21 at 15:39
  • What's wrong with using `With`? It's a lot easier. – BigBen Jul 26 '21 at 15:42
  • I tried this to make a different range flexible and it worked: Dim lrowTC As Long lrowTC = Worksheets("TC Site Report").Range("B" & Rows.Count).End(xlUp).row Set TCSiteReport = Worksheets("TC Site Report").Range("B2:B" & lrowTC). But then when I did this, Dim LrowIQ As Long LrowIQ = Worksheets("QuickCalcs").Range("I" & Rows.Count).End(x1Up).row Set itemQuickDescrip = Worksheets("Quickbooks").Range("I2:I" & LrowIQ) I get an application defined or object defined error. I'm not sure why as they seem to be the same thing. – CBlue Jul 26 '21 at 16:12
  • `xlUp` - the letter `l` as in `lambda`. – BigBen Jul 26 '21 at 16:18

0 Answers0