PDA

View Full Version : [SOLVED] Loop to next different range



Juriemagic
07-03-2015, 01:30 AM
Hi good people,

I have found many answers for "loop" and "next" statements, but the ranges involved are mostly contiguous ranges. What if the cells are non adjacent? Please help me with this.

Only 4 cells, D12, D14, D16 and J12. I would like VBA to select D12 if empty, otherwise if D12 is not empty, then select D14 if empty, if the first 2 cells are not empty but D16 is, then select that cell. The same for J12. I know this must be ridiculously simple to do, but I seem to be having trouble with it. All and any help will be greatly appreciated!. Thanx guys..

If Range("d12") = " " Then
Range("d12").Select
SendKeys "%{down}"
End If
Exit Sub
Else
If Range("d14") = " " Then
Range("d14").Select
SendKeys "%{down}"
End If
Exit Sub
ElseIf Range("d16") = " " Then
Range("d16").Select
SendKeys "%{down}"
endnif
Exit Sub
ElseIf Range("j12") = " " Then
Range("j12").Select
SendKeys "%{down}"
End If
Exit Sub

This is what I have found and I have been trying to modify but I can't get this to work

p45cal
07-03-2015, 06:08 AM
For Each cll In Range("d12,d14,d16,j12").Cells
If cll = "" Then
cll.Select
Exit For
End If
Next cll
?
I don't know what ther sendkeys is about.

Juriemagic
07-03-2015, 06:28 AM
Thank you p45cal,

your code works lovely. I have created shapes to simulate drop down arrows (as I want them always visible), and have given them each a small sendkey macro. Thanx a lot!..