vba go to next unlocked cell in range

Hello Experts,

I would like to have my vba code search through the range("C4:G66") and go to the next unlocked unused cell.

I find ways to go through rows or columns - but I need it to do both.

Please Help!!
mike637Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
Is this what you want? Paste this in a module

Sub Sample()
    Dim rng As Range
    Dim cl As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    
    For Each cl In rng.Cells
        If cl.Locked = False And Len(Trim(cl.Value)) = 0 Then
            MsgBox cl.Address & " is unlocked and unused cell"
        End If
    Next
End Sub

Open in new window


Sid
mike637Author Commented:
Hello,

How do I get this code to go through the range and select the cell that is [not locked] and value = 0?

I have played with the code, but it is going to the last cell in the range [G66]

Thanks
SiddharthRoutCommented:
You mean this?

Sub Sample()
    Dim rng As Range
    Dim cl As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    
    For Each cl In rng.Cells
        If cl.Locked = False And Len(Trim(cl.Value)) = 0 Then
            cl.Select
            Exit For
        End If
    Next
End Sub

Open in new window

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

mike637Author Commented:
Hello SinddharthRout:

I am almost there - I just need it to scroll the column first  - instead of the row.

If I have data in C4 - It is going to D4.  I need it to scroll down to the next "free" cell in the column which would be C5 ( if C4 has data ). If Column C is complete, then it should go to column D.

Can this be done?

mike637
SiddharthRoutCommented:
You mean like this?

Sub Sample()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
            Exit For
        Next i
    Next j
End Sub

Open in new window

mike637Author Commented:
Hello SinddharthRout:


I am getting an error message at line 9

       Next i                   statement

Compile error:  Next without For
SiddharthRoutCommented:
Sorry.

Try this

Sub Sample()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
                Exit For
            End If
        Next i
    Next j
End Sub

Open in new window


Sid
SiddharthRoutCommented:
Wait, I think, I just spotted another error,

Sid
SiddharthRoutCommented:
Ok instead of

Exit For

use

Exit Sub

Like This

Sub Sample()
    Dim rng As Range
    
    Set rng = Sheets("Sheet1").Range("C4:G66")
    For j = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
                Exit Sub
            End If
        Next i
    Next j
End Sub

Open in new window


Sid

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mike637Author Commented:
Worked Excellent!!

Many Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.