Error when trying to set up variable range

[Human_doing]

Hi all,

Can anyone please correct the below code which gives error: 'Run-time error '91': Object variable or With block variable not set. What the code is supposed to do is ask user to open an Excel file, count how many rows of data there are (which will vary each time) and insert the below formula in to each cell in column H for rows with data.

Private Sub CommandButton1_Click()
'Ask user to select Excel file
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the file with raw data for the report")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
'Set range for duplicate count
Dim Sh As Worksheet
Dim LR As Long
With Sh
LR = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("I2:I" & LR).Formula = "COUNTIF(H:H,H2)>1"
End With
 
End Sub

Thanks

 

[Sektor]

Change to this:

If NewFN = [B][COLOR="red"]"[/COLOR][/B]False[B][COLOR="Red"]"[/COLOR][/B]

 

[JackDanIce]

Private Sub CommandButton1_Click()
'Ask user to select Excel file
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the file with raw data for the report")
If NewFN = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
'Set range for duplicate count
Dim Sh As Worksheet
Dim LR As Long
With Sh
LR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
End Sub

Line's I've highlighted in red - where do you define what sheet you want set to the variable SH?

 

[Human_doing]

Hi, thanks for getting back to me so quickly. I've used this code before for when doing loops through numerous worksheets, maybe on this occasion as there is only one worksheet I could take out some of the code i.e. remove the red lines?

 

[JackDanIce]

If you take out the lines in red then your variable LR won't be assigned a value and will fail.

This may work, I'm making the assumption that when you open the workbook, the first sheet that is active is the sheet you want LR based on:

Private Sub CommandButton1_Click()

'Ask user to select Excel file
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Select the file with raw data for the report")

If NewFN = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
    Exit Sub
Else
    Workbooks.Open Filename:=NewFN
End If
 
 
'Set range for duplicate count
Dim Sh As Worksheet
Dim LR As Long
Set Sh = ActiveSheet
With Sh
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
 
End Sub

Changed part in red vs your original posting

 

[Human_doing]

Thank you so much that does work, help much appreciated.

Cheers

 

[JackDanIce]

You're welcome