0

Im struggling to find the issue with my code as it gives me a Run-time error 1004: Application defined or Object defined error. I want to set the value of cell B5 in the sheet titled 'Design' to 34. My code is shown below 

Public Sub test()
Dim book1 As Workbook, recordsht As Worksheet, m As Integer

Set book1 = Application.Workbooks("planning design 2.xlsm")
Set recordsht = book1.Worksheets("Records")

m = 5
recordsht.Range(Cells(m, "B"), Cells(m, "B")).Value = 34

End Sub

The value of m is variable hence why I want to use the Range.Cells method.

West
  • 2,350
  • 5
  • 31
  • 67

3 Answers3

2

Try changing

recordsht.Range(Cells(m, "B"), Cells(m, "B")).Value = 34

To

recordsht.Cells(m, 2).Value = 34

Where m is the row number, and 2 is the second column, eg B.

The reason you got that error is because .Cells(,) expects two number arguments, but you were passing two Range objects instead!

3-14159265358979323846264
  • 3,250
  • 2
  • 15
  • 38
  • Thanks that worked! I've been using that formula in that format though for the Activesheet and it was working fine. Only when I try it on a different sheet it doesnt work. – West Jul 15 '18 at 14:52
  • 1
    The reason for the error is that `Cells(m, 2)` refers to the `ActiveSheet.Cells(m, 2)`. `recordsht.Range(recordsht.Cells(m, "B"), recordsht.Cells(m, "B")).Value = 34` although redundant is valid. – TinMan Jul 15 '18 at 16:07
2

You can use

Public Sub test()
Dim book1 As Workbook, recordsht As Worksheet, m As Long

Set book1 = Workbooks("planning design 2.xlsm")
Set recordsht = book1.Worksheets("Records")

m = 5
recordsht.Range("B" & m) = 34

End Sub

Assuming the xlsm is open.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • 1
    Oh I've never thought of this way of giving arguments to the Range object. Cheers – West Jul 15 '18 at 14:57
1

I created a workbook with your workbook name and worksheet name and your code worked perfectly. You need to insure that:

  1. the workbook is open when the code is run
  2. the spelling of the workbook name is consistent with the code.
  3. the spelling of the worksheet name is consistent with the code.
  4. there are no Protection issues.

enter image description here

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Useful reminder of all the things to check especially that I should run the code first. – QHarr Jul 15 '18 at 14:47
  • The code works with the accepted answer so it wasn't a naming/protection issue. I'm not sure why mine aint working as yours though – West Jul 15 '18 at 14:54