Method ‘Range’ of object ‘_Worksheet’ failed on using Range("Reference!D3")

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!



Read more here: https://stackoverflow.com/questions/66324489/method-range-of-object-worksheet-failed-on-using-rangereferenced3

Content Attribution

This content was originally published by Ashley Rose at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: