look for blank cell Above starting cell instead of below

**stuartglass** · 12-15-2010, 02:53 PM

Hi there, I have a macro that determines the starting cell in a column, and if that cell is not blank, it will find the first blank cell below it. I want it to also find the first blank cell above it._Here is what I have so far:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
rangeString = Range("D6").Value 'this determines the starting cell for the search
If Sheets(Cells(1, 10)).Range(rangeString) = "" Then
Sheets(Cells(1, 10)).Range(rangeString).Offset(0, -1).Copy _
Sheets("Coding").Range("C7")
Else: Sheets(Cells(1, 10)).Range(rangeString).End(xlDown).Offset(1, -1).Copy _
Sheets("Coding").Range("C7") 'this is it finding the first blank cell below the start 
'and copying and pasting a corresponding value from another column
End If

I then have similar coding underneath but instead of xlDown, using xlUp, but rather than finding the first blank cell above the start point, it goes straight to the top of the column.
Is there either a quick fix, or a totally different way of doing it, eg a loop of, is cell blank yes/no, if no look at cell above and try again. if no blanks cells above then return "none found"
Much appreciated

**stuartglass** · 12-16-2010, 06:29 PM

still no reply, ive have now dusted off my vba book and come up with this alternative, but this literally does nothing, but has no bugs.

rangeString = Range("D6").Value 'starting point
z = Sheets("Coding").Cells(6, 5).Value 'the number of times the loop will run (hopefully)
'it is determined by the row of the starting cell
Do Until Sheets(Cells(1, 10)).Range(rangeString) = "" Or z = 0
Sheets("Coding").Cells(6, 5).Value = (Sheets("Coding").Cells(6, 5).Value - 1)
z = Sheets("Coding").Cells(6, 5).Value
Exit Do
If Sheets(Cells(1, 10)).Range(rangeString) = "" Then
Sheets(Cells(1, 10)).Range(rangeString).Offset(0, -1).Copy _
Sheets("Coding").Range("C11")
Else
Sheets("Coding").Range("C11") = ""
End If
Loop

please help

**shg** · 12-16-2010, 06:36 PM

Would you explain what the value in D6 might be, and what you would expect to find?

**stuartglass** · 12-18-2010, 09:08 AM

the value in D6 is B15, but it changes based on where the starting value is supposed to be.
basically its a diary booking system. the Value in D6 matches the time slot in the diary that you want to try and book too. If that time is not available, ie if that cell in the diary is not blank, then it looks before and after to find the nearest available time.

**stuartglass** · 12-21-2010, 06:58 AM

Eventually working it out myself, after about the 4th total rewrite. In case anyone is interested here it is.

Sub CommandButton1_Click()
Application.ScreenUpdating = False
Dim y As Integer
Dim z As Integer
y = Sheets("Coding").Cells(5, 5).Value 'determines the staring row
z = Sheets("Coding").Cells(1, 10).Value '10 different pages so this determines which one to use
Sheets(z).Select
Cells(y, 2).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C7")
Else
Do Until IsEmpty(Selection) = True
Selection.Offset(1, 0).Select
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C7")
Exit Do
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C7")
Loop
End If
Cells(y, 4).Select 'the code repeats itself again, there are 4 diaries per page
If IsEmpty(Selection) = True Then
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C8")
Else
Do Until IsEmpty(Selection) = True
Selection.Offset(1, 0).Select
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C8")
Exit Do
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C8")
Loop
End If
Cells(y, 6).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C9")
Else
Do Until IsEmpty(Selection) = True
Selection.Offset(1, 0).Select
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C9")
Exit Do
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C9")
Loop
End If
Cells(y, 8).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C10")
Else
Do Until IsEmpty(Selection) = True
Selection.Offset(1, 0).Select
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C10")
Exit Do
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C10")
Loop
End If
Cells(y, 2).Select 'now its starting again but looking for slots before the starting slot if the starting one is full, again will repeat for 4 diaries
If IsEmpty(Selection) = True Then
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C11")
ElseIf Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C11").Value = "full"
Else
Do Until (IsEmpty(Selection) = True) Or (Sheets("Coding").Cells(6, 5).Value = 0) 'moniters the row number so that if it gets to the start of diary but still hasnt found anything it will stop
Selection.Offset(-1, 0).Select
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C11")
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(6, 5).Value) - 1)
Exit Do
If Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C11").Value = "full"
Else
Selection.Offset(0, -1).Copy _
Sheets("Coding").Range("C11")
End If
Loop
End If
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(5, 5).Value) - 3)
Cells(y, 4).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C12")
ElseIf Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C12").Value = "full"
Else
Do Until (IsEmpty(Selection) = True) Or (Sheets("Coding").Cells(6, 5).Value = 0)
Selection.Offset(-1, 0).Select
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C12")
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(6, 5).Value) - 1)
Exit Do
If Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C12").Value = "full"
Else
Selection.Offset(0, -3).Copy _
Sheets("Coding").Range("C12")
End If
Loop
End If
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(5, 5).Value) - 3)
Cells(y, 6).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C13")
ElseIf Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C13").Value = "full"
Else
Do Until (IsEmpty(Selection) = True) Or (Sheets("Coding").Cells(6, 5).Value = 0)
Selection.Offset(-1, 0).Select
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C13")
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(6, 5).Value) - 1)
Exit Do
If Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C13").Value = "full"
Else
Selection.Offset(0, -5).Copy _
Sheets("Coding").Range("C13")
End If
Loop
End If
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(5, 5).Value) - 3)
Cells(y, 8).Select
If IsEmpty(Selection) = True Then
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C14")
ElseIf Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C14").Value = "full"
Else
Do Until (IsEmpty(Selection) = True) Or (Sheets("Coding").Cells(6, 5).Value = 0)
Selection.Offset(-1, 0).Select
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C14")
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(6, 5).Value) - 1)
Exit Do
If Sheets("Coding").Cells(6, 5).Value = 0 Then
Sheets("Coding").Range("C14").Value = "full"
Else
Selection.Offset(0, -7).Copy _
Sheets("Coding").Range("C14") 'all in all it leaves me with 8 diary slots to choose from, (from C7-C14) they could all be the same, some could say "full" etc
End If
Loop
End If
Sheets("Coding").Cells(6, 5).Value = ((Sheets("Coding").Cells(5, 5).Value) - 3) 'resets the row counter
Sheets("Coding").Select
End Sub

Now that I've sorted that out, I now need to work out how to make it find the NEXT slot, if the one it thows up as the first available is not suitable for the customer and they were prefer a later/earlier etc time. So I may be back soon.

**stuartglass** · 12-21-2010, 07:25 AM

dang its not working. The "Do Until" Loops are not doing what they should. They are not looping until the first blank cell, they are only looping once. I know this because when the starting cell is not blank and the one after it is not blank either, it is telling me that time, the one after the start, rather than continuing to look.

please help.