0

I have this code:

'Declare variables
Dim wb As Workbook
Dim rv As Range 'rv = row value
Dim wsn As String 'wsn = worksheet name
Dim ws As Worksheet
Dim rws As Worksheet 'record sheet worksheet
Dim ri As Long 'row index used for rv
'Set record workbook and worksheet
Set wb = ActiveWorkbook
Set rws = wb.Worksheets("Record sheet")

'refer to concatenate column
Dim lastr As Long
Dim r As Range
lastr = rws.Cells(Rows.Count, 1).End(xlUp).row
Set r = rws.Range(Cells(2, 7), Cells(lastr, 7))

But when it reaches this code:

Set r = rws.Range(Cells(2, 7), Cells(lastr, 7))

I get a 400 error. I've spent ages trying to figure out whats causing it and i don't know what I'm doing wrong. I've tested my rws variable and it works by going to the correct worksheet when running a "rws.select" test.

  • What value has `lastr` when stopped on error? I mean, moving the cursor over it... – FaneDuru Jan 24 '22 at 13:39
  • Try Dimming r as a variant, and then do r = Array(rws.Range(Cells(2, 7), Cells(lastr, 7))) and then have a look at what was captured in the locals window. – freeflow Jan 24 '22 at 13:40
  • lastr = 64 when error occurs –  Jan 24 '22 at 13:41
  • After doing r = Array(rws.Range(Cells(2, 7), Cells(lastr, 7))) , im now getting run-time error'1004':Method 'Range' of object '_Worksheet' failed –  Jan 24 '22 at 13:44
  • Update: it only works if i put rws.select before running those final few codes. i dont know why though –  Jan 24 '22 at 13:49
  • 1
    the `Cells` were defaulting to the active sheet you can't have a single range across multiple sheets. – Warcupine Jan 24 '22 at 13:51
  • 2
    Always qualify your objects like you did qualify the worksheet with `wb.`, you need to qualify the ranges, cells, rows, columns, and whatnot with `rws.`: `rws.Rows.Count` and `rws.Cells(...)` – VBasic2008 Jan 24 '22 at 13:54
  • Thank you, ill look more into qualifying variables –  Jan 24 '22 at 14:10

0 Answers0