Ignoring Formulas When Searching For Next Empty Cell

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
try this modification which takes account of there being no spaces in column B until the end of the data
VBA Code:
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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Edited answer: When I run that, it keeps placing every result way down at O204:P204.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows
At least that shows the code is doing something!! Try this version:
VBA Code:
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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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

1615247662342.png
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,820
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,146
Messages
5,640,392
Members
417,140
Latest member
whiteprose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top