Finding not only the first blank cell, but all others as well in a column

[marcusja2002]

I've look just about everywhere and am having a tough time finding what I am looking for.

I've been able to find the last empty cell in a column:

iRow = ws.Cells(Rows.Count, 15).End(xlUp).Row + 1

I've been able to find code to find the first blank cell in a column:


Dim FirstCell As String
Dim i As Integer
FirstCell = "A1"
Range(FirstCell).Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "" Then
Exit Do
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

What I cant find / figure out is end result to have iRow = the first empty cell in a column (15).

I also need the "next" button I create to be able to use the current cell chosen by iRow to move to the next blank cell. Each time I hit the next button it lets me move to the next blank cell. I dont want it to start over again at the first blank cell.

Any and all help is greatly appreciated.

Thank you in advance.

 

[Fluff]

Untested but try

[COLOR=#ff0000]Dim NxtBlankCl As Range[/COLOR]

Private Sub cmbreview_Click()


Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")

'Find the last non-blank cell in column P (Leadman Initials)
'this stopped working all of the sudden

If NxtBlankCl Is Nothing Then Set NxtBlankCl = ws.Cells(1, 15).SpecialCells(xlBlanks)(1)
iRow = NxtBlankCl.Row

With ws
Me.txtjo.Value = .Cells(iRow, 1).Value
Me.txtdte.Value = .Cells(iRow, 2).Value
Me.cmbbxmodel.Value = .Cells(iRow, 3).Value
Me.txtsrl.Value = .Cells(iRow, 4).Value
Me.txttchn.Value = .Cells(iRow, 5).Value
Me.txthtvlt.Value = .Cells(iRow, 6).Value
Me.txtwt.Value = .Cells(iRow, 7).Value
Me.txtfailedsystem.Value = .Cells(iRow, 8).Value
Me.textfailuredescription.Value = .Cells(iRow, 9).Value
Me.txttimetofix.Value = .Cells(iRow, 10).Value
Me.txtMECABBYPASS.Value = .Cells(iRow, 11).Value
Me.txtRprcmnt.Value = .Cells(iRow, 12).Value
Me.txtfix.Value = .Cells(iRow, 13).Value
Me.txtdynotechinitials.Value = .Cells(iRow, 14).Value
End With
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Lucky you no new failures, you're done for the day"
End If
End Sub

The Dim statement in red needs to go at the very top of the module

 

[marcusja2002]

It didnt work until I put the below code in the userform initialize section. It then worked perfectly if I had that tab open. Once I chose to run the program with a different tab open it stopped working and gave the error message.

Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")


Dim r As Long
Dim c As Long

' Get row and column of activecell
r = ActiveCell.Row
c = ActiveCell.Column

Range(Cells(1, 15), Cells(1, 15)).SpecialCells(xlBlanks)(1).Select

Exit Sub

It works well if that tab is open, but once I chose a different tab it stops functioning.

 

[Fluff]

Have you tried the code in post#21?

 

[Joe4]

Once I chose to run the program with a different tab open it stopped working and gave the error message.

You are declaring a worksheet variable, but then not doing anything with it.
If you want this to run on the "DynoRepairData" sheet, try activating that sheet (otherwise it will just run on whatever sheet is active, since you aren't use the "ws" sheet reference in any of your range references). One way to do it is activate the sheet you want the code to run on:

[COLOR=#333333]Dim ws As Worksheet[/COLOR]
[COLOR=#333333]Set ws = Worksheets("DynoRepairData")
[/COLOR]ws.Activate

 

[marcusja2002]

Thank you both for your help. The below code seems to be alittle messy but between the two it seems to get everything to function properly. So as long as my operators dont wig out with the flashes back and forth, this should get me where I'm going. Thanks for both of your help.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")
Dim df As Worksheet
Set df = Worksheets("DynoFailureReportForm")
ws.Activate
Dim r As Long
Dim c As Long

' Get row and column of activecell
r = ActiveCell.Row
c = ActiveCell.Column

Range(Cells(1, 15), Cells(1, 15)).SpecialCells(xlBlanks)(1).Select
df.Activate
Exit Sub

Private Sub cmbreview_Click()


Dim NxtBlankCl As Range
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")

'Find the last non-blank cell in column P (Leadman Initials)


If NxtBlankCl Is Nothing Then Set NxtBlankCl = ws.Cells(1, 15).SpecialCells(xlBlanks)(1)
iRow = NxtBlankCl.Row

With ws
Me.txtjo.Value = .Cells(iRow, 1).Value
Me.txtdte.Value = .Cells(iRow, 2).Value
Me.cmbbxmodel.Value = .Cells(iRow, 3).Value
Me.txtsrl.Value = .Cells(iRow, 4).Value
Me.txttchn.Value = .Cells(iRow, 5).Value
Me.txthtvlt.Value = .Cells(iRow, 6).Value
Me.txtwt.Value = .Cells(iRow, 7).Value
Me.txtfailedsystem.Value = .Cells(iRow, 8).Value
Me.textfailuredescription.Value = .Cells(iRow, 9).Value
Me.txttimetofix.Value = .Cells(iRow, 10).Value
Me.txtMECABBYPASS.Value = .Cells(iRow, 11).Value
Me.txtRprcmnt.Value = .Cells(iRow, 12).Value
Me.txtfix.Value = .Cells(iRow, 13).Value
Me.txtdynotechinitials.Value = .Cells(iRow, 14).Value
End With
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Lucky you no new failures, you're done for the day"
End If
End Sub

 

[Joe4]

So as long as my operators dont wig out with the flashes back and forth

You can get rid of a lot of that by doing the following:

Place this line of code at the top of your code (under the "Private Sub..." line):

Application.ScreenUpdating = False

and then place this line at the end of your code (just above the "End Sub" line):

Application.ScreenUpdating = True

By the way, in your first Procedure, I think you have an "Exit Sub" when you need an "End Sub".
You must end a procedure before your start a new one below it.

 

[marcusja2002]

Well found a new hickup. I thought my next was working fine, but i figured out for some reason its moving to the next blank cell while on the DynoRepairData sheet, but its still entering data from the previous blank row. not sure how to even it out again.


Private Sub cmdnxt_Click()

Dim r As Long
Dim c As Long
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")
Dim df As Worksheet
Set df = Worksheets("DynoFailureReportForm")
ws.Activate
' Get row and column of activecell
r = ActiveCell.Row

On Error GoTo err_fix
Range(Cells(r + 1, 15), Cells(Rows.Count, 15)).SpecialCells(xlBlanks)(1).Select
iRow = r
df.Activate
'Find the last non-blank cell in column P (Leadman Initials)


With ws
Me.txtjo.Value = .Cells(iRow, 1).Value
Me.txtdte.Value = .Cells(iRow, 2).Value
Me.cmbbxmodel.Value = .Cells(iRow, 3).Value
Me.txtsrl.Value = .Cells(iRow, 4).Value
Me.txttchn.Value = .Cells(iRow, 5).Value
Me.txthtvlt.Value = .Cells(iRow, 6).Value
Me.txtwt.Value = .Cells(iRow, 7).Value
Me.txtfailedsystem.Value = .Cells(iRow, 8).Value
Me.textfailuredescription.Value = .Cells(iRow, 9).Value
Me.txttimetofix.Value = .Cells(iRow, 10).Value
Me.txtMECABBYPASS.Value = .Cells(iRow, 11).Value
Me.txtRprcmnt.Value = .Cells(iRow, 12).Value
Me.txtfix.Value = .Cells(iRow, 13).Value
Me.txtdynotechinitials.Value = .Cells(iRow, 14).Value
Me.txtleadmaninitials.Value = .Cells(iRow, 15).Value
Me.txtstage.Value = .Cells(iRow, 16).Value
Me.txtop.Value = .Cells(iRow, 17).Value
Me.txtdecptfailure.Value = .Cells(iRow, 18).Value
Me.txtroot.Value = .Cells(iRow, 19).Value
Me.txtsolution.Value = .Cells(iRow, 20).Value
Me.txtvndrname.Value = .Cells(iRow, 21).Value
Me.txtcmptpart.Value = .Cells(iRow, 22).Value
End With
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Lucky you no new failures, you're done for the day"
End If
Exit Sub
err_fix:
MsgBox "No more blank cells in middle of range"
End Sub

 

[marcusja2002]

Dont know what happened, transfered the information over to a new test area, because things are starting to break down and now when I run the below code it picks the first blank found anywhere, not just column 15

Private Sub cmbreview_Click()


Dim NxtBlankCl As Range
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DynoRepairData")

'Find the last non-blank cell in column P (Leadman Initials)


If NxtBlankCl Is Nothing Then Set NxtBlankCl = ws.Cells(1, 15).SpecialCells(xlBlanks)(1)
iRow = NxtBlankCl.Row

With ws
Me.txtjo.Value = .Cells(iRow, 1).Value
Me.txtdte.Value = .Cells(iRow, 2).Value
Me.cmbbxmodel.Value = .Cells(iRow, 3).Value
Me.txtsrl.Value = .Cells(iRow, 4).Value
Me.txttchn.Value = .Cells(iRow, 5).Value
Me.txthtvlt.Value = .Cells(iRow, 6).Value
Me.txtwt.Value = .Cells(iRow, 7).Value
Me.txtfailedsystem.Value = .Cells(iRow, 8).Value
Me.textfailuredescription.Value = .Cells(iRow, 9).Value
Me.txttimetofix.Value = .Cells(iRow, 10).Value
Me.txtMECABBYPASS.Value = .Cells(iRow, 11).Value
Me.txtRprcmnt.Value = .Cells(iRow, 12).Value
Me.txtfix.Value = .Cells(iRow, 13).Value
Me.txtdynotechinitials.Value = .Cells(iRow, 14).Value
End With
If Trim(Me.txtjo.Value) = "" Then
Me.txtjo.SetFocus
MsgBox "Lucky you no new failures, you're done for the day"
End If
End Sub

 

[Fluff]

2 things
1) this line

[COLOR=#333333]Dim NxtBlankCl As Range[/COLOR]

needs to go at the very top of the module (ie before any macros)
2) Do you have any merged cells?

 

[marcusja2002]

Changed the code to the top of everything.

There are no merged cells.

I almost feel like I need to start all over again with my code. Very frustrated I guess this is how you learn...