Reference a column with variable rows with vba

[Rdhostetler]

hello
i have some code that is supposed to look at a specified sheet and figure out how many active rows it has and then look at column D and check each cell in that column to see if any are over 55. if any cell is over 55 it will give a message box as you can see... with this code it is giving me the message box even though i dont have any over 55. any help would be appreciated.

thanks

Private Sub check_screen_width()


'check screen widths on order
Dim width As Long


numrows = Sheet17.Range("d1", Sheet17.Range("d1").End(xlDown)).Rows.Count


For width = 1 To numrows
Application.ScreenUpdating = True


If Sheet17.Cells(width, 4).Value >= 55 Then MsgBox "You have screens on the order above the 55 inch maximum width! Please manually open the screen order and change these screens to mulled screens with adapters", vbOKOnly, "Over Width Screens on this order!"
Application.ScreenUpdating = False




Next width






End Sub

 

[Ark68]

I only had errors with your code. (line 4, so I approachedit differently)

Try this ...

Private Sub check_screen_width()

'check screen widths on order
    Dim width As Long
    Dim numrows as integer

   numrows = ThisWorkbook.Worksheets("Sheet17").Cells(ThisWorkbook.Worksheets("Sheet17").Rows.Count, "D").End(xlUp).Row
   Application.ScreenUpdating = True
   For width = 1 To numrows
       If ThisWorkbook.Worksheets("Sheet17").Cells(width, 4).Value >= 55 Then 
             MsgBox "You have screens on the order above the 55 inch maximum width! Please manually open the screen order and change these screens to mulled screens with adapters", vbOKOnly, "Over Width Screens on this order!"
       End if
   Next Width
   Application.ScreenUpdating = False
End Sub.

 

[Rdhostetler]

Thanks for that code! Let me try it in the morning and I’ll let you know!

Thanks!!

 

[Rdhostetler]

hello
i tried the code and i get a error saying it is out of range on this line
numrows = ThisWorkbook.Worksheets("Sheet17").Cells(ThisWorkbook.Worksheets("Sheet17").Rows.Count, "D").End(xlUp).Row
any idea why this would be?

thanks

 

[Rdhostetler]

just checking if you saw my reply and if you have any more suggestions?

thanks

 

[Fluff]

Are you sure that the values in column D are numbers rather than text?

 

[Rdhostetler]

Are you sure that the values in column D are numbers rather than text?

there is 1 cell with text in D1 (its a heading) and then the rest are numbers and the numbers can have up to 4 decimal points...
thanks

 

[Fluff]

Ok, how about

Private Sub check_screen_width()
   Dim Wdth As Long
   
   Application.ScreenUpdating = True
   For Wdth = 2 To Sheet17.Range("D" & Rows.Count).End(xlUp).Row
      If Sheet17.Cells(Wdth, 4).Value >= 55 Then MsgBox "You have screens on the order above the 55 inch maximum width! Please manually open the screen order and change these screens to mulled screens with adapters", vbOKOnly, "Over Width Screens on this order!"
   Next Wdth
End Sub

You should avoid using VBA keywords (such as Width) as names of variables.

 

[Rdhostetler]

Ok, how about

Private Sub check_screen_width()
   Dim Wdth As Long
   
   Application.ScreenUpdating = True
   For Wdth = 2 To Sheet17.Range("D" & Rows.Count).End(xlUp).Row
      If Sheet17.Cells(Wdth, 4).Value >= 55 Then MsgBox "You have screens on the order above the 55 inch maximum width! Please manually open the screen order and change these screens to mulled screens with adapters", vbOKOnly, "Over Width Screens on this order!"
   Next Wdth
End Sub

You should avoid using VBA keywords (such as Width) as names of variables.

Kudos to you! that works! thank you very much!!

 

[Fluff]

You're welcome & thanks for the feedback