VBA to use IF to check for blank cell before running process

[K1600]

Can anyone help?

I have the below process which looks in a sheet (Admin Users) for a value taken from a text box (TxtPIN) and then completes some user form fields with the data from adjoining cells.

The setup I have currently works fine however I need to add an extra level to it so that if having found the value from TxtPIN in the sheet if the cell 6 to the right of the found entry is empty then the process will return a MsgBox and stop running. I will also need to do exactly the same on a separate user form where the cell 6 to the right is not empty.

I have tried a number of ideas but cannot get it to fail if the cell is empty (of not empty in the second user form). Just for info, the sheet (Admin Users) is in a separate workbook to the one that the code is in.

Thanks in advance.

Dim wbk As Workbook
Dim i As Long
Dim sh As Worksheet
Dim rng As Range

Set wbk = Workbooks.Open("path removed as contained company info.xlsx", WriteResPassword:="Password", IgnoreReadOnlyRecommended:=True)
Set sh = wbk.Sheets("Admin Users")
Set rng = sh.Range("A:A").Find(TxtPIN.value, , xlValues, xlWhole)
rng.Offset(0, 6).value = TxtLoggedInAs
rng.Offset(0, 7).value = TxtReason
rng.Offset(0, 10).value = TxtRemarks
rng.Offset(0, 11).value = CboAuth1
rng.Offset(0, 12).value = CboAuth2
rng.Offset(0, 13).value = CboAuth3
rng.Offset(0, 14).value = CboAuth4
rng.Offset(0, 15).value = CboAuth5
rng.Offset(0, 16).value = CboAuth6
rng.Offset(0, 17).value = CboAuth7
rng.Offset(0, 18).value = CboAuth8
rng.Offset(0, 19).value = CboAuth9
rng.Offset(0, 20).value = CboAuth10
rng.Offset(0, 21).value = CboAuth11
rng.Offset(0, 22).value = CboAuth12
rng.Offset(0, 23).value = CboAuth13
rng.Offset(0, 24).value = CboAuth14
rng.Offset(0, 25).value = CboAuth15
rng.Offset(0, 26).value = CboAuth16
rng.Offset(0, 27).value = CboAuth17
rng.Offset(0, 28).value = CboAuth18
rng.Offset(0, 29).value = Date + Time 'Adds date/time stamp
rng.Offset(0, 30).value = TxtLoggedInAs

'Saves and closes master data workbook
wbk.Save
wbk.Close

 

[DanteAmor]

Not tested, try this:

Set rng = sh.Range("A:A").Find(TxtPIN.value, , xlValues, xlWhole)
if rng.Offset(0, 6).value = "" then
   msgbox "empty cell"
   exit sub
end if
rng.Offset(0, 6).value = TxtLoggedInAs

 

[Fluff]

How about

Set rng = sh.Range("A:A").Find(TxtPIN.Value, , xlValues, xlWhole)
If rng Is Nothing Then
   MsgBox TxtPIN & " not found"
   Exit Sub
End If
If rng.Offset(, 6) = "" Then
   MsgBox "Cell is empty"
   Exit Sub
End If
rng.Offset(0, 6).Value = TxtLoggedInAs

 

[K1600]

Thanks both for the speedy response. Both suggestions are around what I had tried, I have just tried both of them but neither work. I've tried it both with a PIN that has a clear cell in it's offset range and one that is not clear and sill the macro continues to run.

Any thoughts?

 

[Fluff]

Are you looking to check if column F or G is blank?

 

[K1600]

Are you looking to check if column F or G is blank?

It's in G, I did try juggling my offset around in case I had the wrong one but still I can't seem to get it to work.

 

[Fluff]

In that case col G for the found row is not empty.
Do you have a formula in col G? If so what is it?

 

[K1600]

I'm testing it against 2 PIN numbers, one has an entry in col G and the other is totally empty. There are no formulas in this sheet at all.

I don't know if it is relevant but col G is formatted to 'General'.

 

[Fluff]

If you add this message box as shown, what does it say?

Set Rng = sh.Range("A:A").Find(TxtPIN.Value, , xlValues, xlWhole)
If Rng Is Nothing Then
   MsgBox TxtPIN & " not found"
   Exit Sub
End If
MsgBox Rng.Row & vbLf & "|" & Rng.Offset(, 6).Value & "|"
If Rng.Offset(, 6) = "" Then
   MsgBox "Cell is empty"
   Exit Sub
End If
Rng.Offset(0, 6).Value = TxtLoggedInAs

 

[K1600]

If you add this message box as shown, what does it say?

Set Rng = sh.Range("A:A").Find(TxtPIN.Value, , xlValues, xlWhole)
If Rng Is Nothing Then
   MsgBox TxtPIN & " not found"
   Exit Sub
End If
MsgBox Rng.Row & vbLf & "|" & Rng.Offset(, 6).Value & "|"
If Rng.Offset(, 6) = "" Then
   MsgBox "Cell is empty"
   Exit Sub
End If
Rng.Offset(0, 6).Value = TxtLoggedInAs

I think I might have sussed it. I think I was expecting it to give me the error earlier in the process than it actually does. I need to just do a bit of tweaking but then I think it will work.

The responses when I added the new line was
4
|1234|

& when I tried the second PIN I got

6
||

For the opposite userform I need it to do the opposite and to error if the cell is not empty. Which bit would I change/add to do that?