VBA Matching Text in Cell to a Error Msg

[Tech1_uk]

Hi all,

I'm trying to do something very simple but I just can't get this to work.

I have a file that is import into a sheet and then the following is run to look for a specific text and then copy it to a new sheet.

' Selectdata
'
Dim lr As Long
Dim Fnd As Range

    lr = Worksheets("C_Data").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set Fnd = Worksheets("C_Data").Rows(1).Find("VOLTAGE 01", lookat:=xlPart)
        If Fnd Is Nothing Then MsgBox "VOLTAGE Not found in C Data", vbExclamation: Exit Sub
    
    Fnd.Resize(lr, 96).Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
ActiveSheet.Name = "Data"

This works well and does what I want it too but I want to add another check.

The data that is imported will look like this,

Monitor Item	BATT LEVEL (%)	VOLTAGE 01	VOLTAGE 02	VOLTAGE 03	VOLTAGE 04	VOLTAGE 05	VOLTAGE 06
0	48	3914	3914	3914	3910	3909	3914
0.05	48	3914	3914	3914	3910	3909	3914
0.1	48	3914	3914	3914	NO_VALUE	3909	3914
0.15	48	3914	3914	3914	3910	3909	3914
0.2	48	3914	3914	3914	3910	3909	3914
0.25	48	3914	3914	3909	3910	3909	3914
0.3	48	3914	3914	3909	3910	3909	3914

What I need to do is the "NO_VALUE" ( shown above in VOLTAGE 4 column) is displayed in any cell, then to display a error msg then exit.

I guess it should be run before the code above and before the new sheet is created.

Currently I've tried all sorts and can not get this to work.

Could someone be so kind to point me in the right direction as in theory this should be so simple!!!

Many thanks

Steve

 

[JoeMo]

Maybe:

Dim lr As Long
Dim Fnd As Range
    If Application.CountIf(ActiveSheet.UsedRange, "NO_VALUE") Then
        MsgBox "At least one cell contains NO_VALUE - exiting sub"
        Exit Sub
    End If
    lr = Worksheets("C_Data").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set Fnd = Worksheets("C_Data").Rows(1).Find("VOLTAGE 01", lookat:=xlPart)
        If Fnd Is Nothing Then MsgBox "VOLTAGE Not found in C Data", vbExclamation: Exit Sub
    
    Fnd.Resize(lr, 96).Copy
Sheets.Add After:=ActiveSheet
ActiveSheet.Paste
ActiveSheet.Name = "Data"

 

[Fluff]

How about

   Dim lr As Long
   Dim Fnd As Range

   With Worksheets("C_Data")
      Set Fnd = .Cells.Find("NO_value", , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         MsgBox "Error"
         Exit Sub
      End If
      lr = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
      Set Fnd = .Rows(1).Find("VOLTAGE 01", lookat:=xlPart)
   End With
   If Fnd Is Nothing Then MsgBox "VOLTAGE Not found in C Data", vbExclamation: Exit Sub
   
   Fnd.Resize(lr, 96).Copy
   Sheets.Add After:=ActiveSheet
   ActiveSheet.Paste
   ActiveSheet.Name = "Data"

 

[Tech1_uk]

Thanks Both,

The example from Fluff has worked a treat.

Many thanks

Steve

 

[Fluff]

Glad we could help & thanks for the feedback.