Find next clear cell - Multi Range

[FoRmEd]

The code shown below is meant to take info from a textbox on a userform and enter it on the next clear cell in a Range, the Range is column B from 3:43, and then, if those are full, it moves to column I and uses that Range from 3:43. I found this code and changed it to work with mine but it doesnt work...at all. Any education would be appreciated.

Dim i As Long, MTRow As Long
i = 3
With Sheets("PHZ1")
    Do
        i = i + 1
        If WorksheetFunction.CountA(Rows(i)) = 0 Then
            MTRow = b & i
            Exit Do
        End If
    Loop
End With
Range("B" & MTRow).Offset(5, 0).Select
    Me.textCIS.Copy
    Sheets("PHZ1").Activate
    ActiveSheet.Paste
 
  'copy the data to the database
     
        If Me.optCLSD Then
            PHZ1.Cells(RowNum, 3) = "1"
        Else
            PHZ1.Cells(RowNum, 3) = ""
        End If
        If Me.optAddt Then
            PHZ1.Cells(RowNum, 4) = "1"
        Else
            PHZ1.Cells(RowNum, 4) = ""
        End If
        If Me.optRoute Then
            PHZ1.Cells(RowNum, 5) = "1"
        Else
            PHZ1.Cells(RowNum, 5) = ""
        End If
        If Me.optFLIP Then
            PHZ1.Cells(RowNum, 6) = "1"
        Else
            PHZ1.Cells(RowNum, 6) = ""
        End If

 

[FoRmEd]

Anyone have any ideas?

 

[Tom Urtis]

This would answer the question you are asking:

Dim NextRow&, myColumn&
With Sheets("PHZ1")
If WorksheetFunction.CountA(.Range("B3:B43")) < 41 Then
myColumn = 2
NextRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
Else
myColumn = 9
If WorksheetFunction.CountA(.Range("I3:I43")) = 0 Then
NextRow = 3
Else
NextRow = .Cells(Rows.Count, 9).End(xlUp).Row + 1
End If
End If
.Cells(NextRow, myColumn).Value = textCIS.Value
End With

The question that bears being asked of you is the relevance in your code of all those option buttons, which have no association with the question about placing the textbox value into the next cell in column B or in column I.

 

[FoRmEd]

I figured, one thing at a time, and included it by accident. The userform has those option buttons and if one is clicked then the corresponding cells to the right of the clear cell in column b or I would receive the corresponding "1".So now I will try and figure out how to make that work as well.

 

[FoRmEd]

Applied the code and it wont start in the "B3", it just skips the first column and starts on "I3" I've tried tinkering with it but Im getting no where

 

[Tom Urtis]

Try this then, assuming you are clicking CommandButton1 to make this happen. Works for me no problem.

Private Sub CommandButton1_Click()
Dim NextRow&, myColumn&
With Sheets("PHZ1")
If WorksheetFunction.CountA(.Range("B3:B43")) < 41 Then
myColumn = 2
If WorksheetFunction.CountA(.Range("B3:B43")) = 0 Then
NextRow = 3
Else
NextRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
End If
Else
myColumn = 9
If WorksheetFunction.CountA(.Range("I3:I43")) = 0 Then
NextRow = 3
Else
NextRow = .Cells(Rows.Count, 9).End(xlUp).Row + 1
End If
End If
.Cells(NextRow, myColumn).Value = textCIS.Value
End With
End Sub

 

[FoRmEd]

Thank you, that fixed it. Any ideas that you can share about making the option buttons work? Basicly what happens:

cmdAdd_Click
Then the code you provided works
but there are 4 columns to the right of the cell that is found to be blank
-need the option buttons to insert the users choice to the right of the blank cell

        If Me.optCLSD Then
            PHZ1.Cells.Offset(0, 1) = "1"
        Else
            PHZ1.Cells.Offset(0, 1) = ""
        End If
        If Me.optAddt Then
            PHZ1.Cells.Offset(0, 2) = "1"
        Else
            PHZ1.Cells.Offset(0, 2) = ""
        End If
        If Me.optRoute Then
            PHZ1.Cells.Offset(0, 3) = "1"
        Else
            PHZ1.Cells.Offset(0, 3) = ""
        End If
        If Me.optFLIP Then
            PHZ1.Cells.Offset(0, 4) = "1"
        Else
            PHZ1.Cells.Offset(0, 4) = ""
        End If

 

[Tom Urtis]

All these option buttons do the same thing...why do you have so many. Also, are you saying you click a command button, then click an option button? Something looks strange about the logic of what you are doing.

 

[FoRmEd]

The option buttons represent the course of action the user took to solve the case. So they would enter the case #(CIS) in the textbox and select the applicable option button then click submit(cmdAdd) your code initiates

 

[Tom Urtis]

See if this does what you ant:

Private Sub cmdAdd_Click()
Dim NextRow&, myColumn&
With Sheets("PHZ1")
If WorksheetFunction.CountA(.Range("B3:B43")) < 41 Then
myColumn = 2
If WorksheetFunction.CountA(.Range("B3:B43")) = 0 Then
NextRow = 3
Else
NextRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
End If
Else
myColumn = 9
If WorksheetFunction.CountA(.Range("I3:I43")) = 0 Then
NextRow = 3
Else
NextRow = .Cells(Rows.Count, 9).End(xlUp).Row + 1
End If
End If
With .Cells(NextRow, myColumn)
.Value = textCIS.Value
.Offset(0, 1) = ""
.Offset(0, 2) = ""
.Offset(0, 3) = ""
.Offset(0, 4) = ""
Select Case True
Case optCLSD = True: .Offset(0, 1) = "1"
Case optAddt = True: .Offset(0, 2) = "1"
Case optRoute = True: .Offset(0, 3) = "1"
Case optFLIP = True: .Offset(0, 4) = "1"
End Select
End With
End With
End Sub