Ignoring Formulas When Searching For Next Empty Cell

[ExcelAtEverything]

Hello! The following was created to find the next empty cell within a range in column B, and then paste in info which was copied from O6:P6. The only problem is that it's not working correctly because I have cells in column B which contain formulas (fitted with an IFERROR prefix to keep some cells empty when there is no info to fill them). How can I edit this to ignore cells containing formulas in column B when looking for the next empty?

Sub AddExtraStoreToLocMasterList() '
' AddExtraStoreToLocMasterList Macro
' This macro copies O6:P6 to cipboard, then finds first blank cell in column B Loc Master List range, and the paste specials there.
'
Range("O6:P6").Select
Selection.Copy
Range("B4").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O6:P6").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("O6").Select
End Sub

 

[ExcelAtEverything]

Sorry, that doesn't work either. When I run it, all that visibly happens is that the contents of the cells O6:P6 get erased. Nothing else occurs anywhere on the sheet.

 

[offthelip]

try this modification which takes account of there being no spaces in column B until the end of the data

Sub AddExtraStoreToLocMasterList()
inarr = Range("O6:P6")
lastrow = Cells(Rows.Count, "B").End(xlUp).Row 
bvalues = Range(Cells(1, 2), Cells(lastrow, 2))
Bformula = Range(Cells(1, 2), Cells(lastrow, 2)).Formula
Found =false
For i = 4 To lastrow 
  If bvalues(i, 1) = "" And Bformula(i, 1) = "" Then
     Range(Cells(i, 15), Cells(i, 16)) = inarr
     found =true 
     Exit For
  End If
Next i
if not(Found) then 
     Range(Cells(lastrow+1, 15), Cells(lastrow+1, 16)) = inarr
end if
Range("O6:P6") = ""
Range("O6").Select
End Sub

 

[ExcelAtEverything]

Edited answer: When I run that, it keeps placing every result way down at O204:P204.

 

[offthelip]

At least that shows the code is doing something!! Try this version:

Sub AddExtraStoreToLocMasterList()
inarr = Range("O6:P6")
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Bformula = Range(Cells(1, 2), Cells(lastrow, 2)).Formula
Found = False
For i = 4 To lastrow
  If Bformula(i, 1) = "" Then
     Range(Cells(i, 15), Cells(i, 16)) = inarr
     Found = True
     Exit For
  End If
Next i
If Not (Found) Then
     Range(Cells(lastrow + 1, 15), Cells(lastrow + 1, 16)) = inarr
End If
Range("O6:P6") = ""
Range("O6").Select
End Sub

 

[ExcelAtEverything]

That macro doesn't do anything. Literally nothing happens.

 

[ExcelAtEverything]

Wait, sorry offthelip! It works, but still slightly off. So, last night after my last reply here, I made a couple of changes on the sheet with regards to where things are located. Once I remembered that, I adjust a couple of the things in your macro. It now works... but its just maybe 1 adjustment away from being correct.

Below is the code as it stands now. The only issue that remains is this: The number is being plugged into the list at the far bottom just under my range/table (at B204:C204), instead of at the next empty row, which is far above that.

Sub AddExtraStoreToLocMasterList()
inarr = Range("M4:N4")
lastrow = Cells(Rows.Count, "B").End(xlUp).Row

Bformula = Range(Cells(1, 2), Cells(lastrow, 2)).Formula
Found = False
For i = 4 To lastrow
If Bformula(i, 1) = "" Then
Range(Cells(i, 15), Cells(i, 16)) = inarr
Found = True
Exit For
End If
Next i
If Not (Found) Then
Range(Cells(lastrow + 1, 2), Cells(lastrow + 1, 3)) = inarr
End If
Range("M4:N4") = ""
Range("M4").Select
End Sub

 

[offthelip]

I suspect the reason my code doesn't work the way you want it because I have made some incorrect assumption about what your data looks like. So please can you post the full worksheet on this forum. That is probably the only way I can help you.

 

[ExcelAtEverything]

offthelip, thank you so much. However, I've decided to make an adjustment. All of the difficulty made me start thnking of alternate methods, and I've found one which eliminates the need for this function. I really do appreciate the help.

 

[offthelip]

Good idea

 

[ExcelAtEverything]

Lol! Thx again bro!