searching cells for text using VBA while doing calculations

[Mixphonics]

Hello, I have a macro that I use to test quantities of items. I would like to add a capability to this macro so that I can see if the cell it is searching contains text and if it does, to skip that cell and go to the next item.

If it doesn't have text I would like it to check the quantity and see if it is greater than or equal to 400,000 and if so report an ITEM QUANTITY ALERT in the current active cell.

Here is the code I started working on:

" If Application.WorksheetFunction.IsText(LotQty) Then celltext = "true"
If celltext = True Then GoTo loopsequence
End If
If LotQty >= 400000 Then ActiveCell.Value = "LOT SIZE ERROR!"
End If"

I know the sentax is wrong in the above code and it wouldnt work but I am hoping someone can shine some light on this. I have the full code below:

Sub Macro()
'Global Variables
Dim QtyTests As Integer, ReqTests As Integer, InitQty As Integer, LotQty As String, Cork As String, Corktype As String
Dim result As Long, x As Integer, celltext


'starting value of variable x
x = 0




'select starting position of macro
Sheets("ETS Testing").Select
Range("H3").Select


Do
ReqTests = 0
InitQty = 0
ActiveCell.Offset(1, 0).Select
If ActiveCell.Offset(0, 4).Value = "" Then
x = x + 1
End If
'Bale Qty for each grade
If IsNumeric(ActiveCell.Offset(0, 4).Value) Then
InitQty = ActiveCell.Offset(0, 4).Value
Else
x = x + 1
End If
'Corkgrade & type for each lot/grade
Cork = ActiveCell.Offset(0, -1).Value
'define cork lot quantity
LotQty = ActiveCell.Offset(0, 3).Value
'define the range of the 20 possible bales tested at ETS's results
Range(ActiveCell.Offset(0, 7), ActiveCell.Offset(0, 26)).Select
QtyTests = Application.WorksheetFunction.CountA(Selection)
ActiveCell.Offset(0, -7).Select
result = InStr(Cork, "C3") & InStr(Cork, "C2") & InStr(Cork, "PRIMO") & InStr(Cork, "UNIQ")
If InStr(Cork, "2K6") Then GoTo loopsequence Else GoTo CTest


CTest:
If result = 0 Then Corktype = "natural" Else Corktype = "agglomerated"

If Corktype = "agglomerated" Then
If InitQty < 2 Then
ReqTests = 0
ElseIf InitQty <= 15 And InitQty >= 2 Then
ReqTests = 2
ElseIf InitQty <= 25 And InitQty > 15 Then
ReqTests = 3
ElseIf InitQty <= 90 And InitQty > 25 Then
ReqTests = 5
ElseIf InitQty <= 150 And InitQty > 90 Then
ReqTests = 5
ElseIf InitQty <= 280 And InitQty > 150 Then
ReqTests = 8
ElseIf InitQty >= 281 Then
ReqTests = 13
End If
End If


If Corktype = "natural" Then
If InitQty < 2 Then
ReqTests = 0
ElseIf InitQty <= 8 And InitQty >= 2 Then
ReqTests = 2
ElseIf InitQty <= 15 And InitQty > 8 Then
ReqTests = 3
ElseIf InitQty <= 25 And InitQty > 15 Then
ReqTests = 5
ElseIf InitQty <= 50 And InitQty > 25 Then
ReqTests = 8
ElseIf InitQty <= 90 And InitQty > 50 Then
ReqTests = 13
ElseIf InitQty <= 150 And InitQty > 90 Then
ReqTests = 20
End If
End If

If Application.WorksheetFunction.IsText(LotQty) Then celltext = "true"
End If
If celltext = True Then GoTo loopsequence
End If
If LotQty >= 400000 Then ActiveCell.Value = "LOT SIZE ERROR!"
End If


loopsequence:
If ReqTests <= QtyTests Then
If IsNumeric(InitQty) Then
ActiveCell.Value = "OK"
End If
Else
ActiveCell.Value = "ALERT!!!!"
End If



Loop Until x > 500




End Sub





Thank you anyone in advance for your help!

Mixphonics

 

[Mixphonics]

Hello again, any VBA programmers out there that might be able to help I would greatly appreciate it!

How would someone check a cell if it contains text or not with VBA code and if it does contain text return a value to a variable as true or false if it does not contain text.

Any help would be greatly appreciated.

Best regards,

Mixphonics