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

 

[Fluff]

With the 2nd pin did you then get message "Cell is empty"

 

[K1600]

I've managed to sort it. I have used your code (Fluff) but put it as part of a 'After Update' in my userform rather than after the command button has been pressed. I've got it working both with and without the cells being empty also.

Once again, thanks for your help, it's much appreciated.

 

[K1600]

With the 2nd pin did you then get message "Cell is empty"

Sorry, I just realised that you sent this before I replied with my update.

No, it still wasn't working but I think it was my fault. The code was running after the command button was pressed where as I needed it 'After Update' of a text box in the user form. Because that's where I thought I had put it, I wasn't clicking the command button because in my head it should already have worked by then..... ?‍

 

[Fluff]

Glad you sorted it & thanks for the feedback.