1

I am trying to run a code to populate cell F4 with the value from a reference sheet and input a formula in G4 to calculate the number of people that are in my room. All of this is based on the information (room type) provided in D4. I have included a loop to ensure if any "new rooms" are added to the sheet it will check the cells below. The code was working before I made some adjustments and it shows an error of:

Method 'Range' of object '_Worksheet' failed

I have checked that all referenced cells contain data, but I am not sure why else there is an error. Here is a small sample of the code I have written:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Range("D4").Select

    Do Until IsEmpty(ActiveCell)
        'People Density
        If Range("D4").Value = "Booking/Waiting" Then
            Range("F4").Value = Range("Reference!D3").Value
        ElseIf Range("D4").Value = "Cells without plumbing fixtures" Then
            Range("F4").Value = Range("Reference!D4").Value
        ElseIf Range("D4").Value = "Cells with plumbing fixtures" Then
            Range("F4").Value = Range("Reference!D5").Value
        End If
    
        '# of People Calculation - ASHRAE
        If Range("D4").Value = "Booking/Waiting" Then
            Range("G4").Formula = "=(E4/1000)*Reference!D3"
        ElseIf Range("D4").Value = "Cells without plumbing fixtures" Then
            Range("G4").Formula = "=(E4/1000)*Reference!D4"
        ElseIf Range("D4").Value = "Cells with plumbing fixtures" Then
            Range("G4").Formula = "=(E4/1000)*Reference!D5"
        End If
            
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

Thanks in advance for any and all help received!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Ashley Rose
  • 11
  • 1

1 Answers1

3

I suspect there are other issues with the code, or at least I do not understand the code purpose in its entirety.

But the specific issue can be resolved by correcting the reference to the other worksheet.

Simply use:

Range("F4").Value = Worksheets("Reference").Range("D3").Value

Instead of:

Range("F4").Value = Range("Reference!D3").Value

And do the same in all the 6 places you are using the Reference!.

See more examples for references to other sheets from Microsoft: https://learn.microsoft.com/en-us/office/vba/api/Excel.Range(object)

Dave
  • 61
  • 5