VBA: Msgbox to alert that there are no negative values in the final col for validation

[SL0444]

Hi,

I am relatively new to VBA so still struggling to fully understand the syntax. I have been working on an automation macro & I am quite close to completion I just have a step that is appearing more difficult than I would've thought.

I am trying to provide a pop-up that alerts the user that there are no negative values in the final column. I have attempted two ways of going about this, firstly I tried to acquire the column letter of the final col with data to then loop through that as follows:

Sub FindNeg()

Dim Ws As Worksheet
Dim LastColumn As Long
Dim myCol As String

LastColumn = Ws(2, Ws.Columns.Count).Cells.End(xlToRight).Column

myCol = ColumnLetter(LastColumn)

For Each I In Range("myCol:myCol")
If I.Value > 0 Then
MsgBox ("No negative Values")
End If
Next I

End Sub

using this function I found online

Function ColumnLetter(ColumnNumber As Long) As String
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False), "$")(0)
End Function

My second attempt feels more successful as I am not obtaining any errors but I am still not getting any pop-ups despite there being no negative values in the column. Here is the second attempt:

Sub Macro3()

For I = Range("A1").End(xlDown).End(xlToRight).End(xlUp) To Range("A1").End(xlDown).End(xlToRight)
For Each C In I
If C > 0 Then
MsgBox ("There are no negative values!")
Else
End If
Next C
Next

End Sub

If anyone could provide more insight into why either of the two may not be working that would be great! The first of the two obtains a run-time error '91': Object variable or with block variable not set (in regards to the LastColumn variable.

Whilst the second attempt doesn't demonstrate any error value it just doesn't provide the required popup.

 

[mole999]

could you check for MIN value in the column, and If lowest is greater equal to zero do it then ?

 

[SL0444]

I have tried to do that but get a similar reaction to the my second attempt. The code is as follows:

Sub negval()

Dim FirstCol As Range
Dim LastCol As Range

Set FirstCol = Range("a1").End(xlDown).End(xlToRight).End(xlUp)
Set LastCol = Range("A1").End(xlDown).End(xlToRight)

For I = FirstCol To LastCol
If WorksheetFunction.Min(I) >= 0 Then MsgBox ("There are no negative values!")
Next

End Sub

I am not sure whether my issue is the definition of I being completely accurate.

 

[mole999]

so what would be an obvious last row and column number

 

[mole999]

Sub Macro6()
Dim Rng
Dim dblmin
'Set range from which to determine smallest value
Set Rng = Sheet1.Range("A1:Z100")
dblmin = Application.WorksheetFunction.Min(Rng)
If dblmin >= 0 Then MsgBox "There are no negative values! ", vbOKOnly, "Min"
End Sub

 

[SL0444]

I am only trying to look in the final column which is dynamic thus I cannot assume the final letter column. I am trying to find something that will adapt to the dynamic change and locate the final column and look for either negative or the minimum value in that column only.