Free Microsoft Excel 2013 Quick Reference

Seeking an alternative to blank ("") cell in IF arguments

I usually find it convenient to use "" in formulas such as the following:

=IF(B12>0,B12/C4,"")

The problem of course is that an error will be returned if the cell left
blank with "" is used in subsequent calculations. Is there an alternative to
using zero instead of "" that still makes it obvious that the cell is
intentionally left unpopulated but that does not result in #VALUE# errors?


Post your answer or comment

comments powered by Disqus
I am not good in excel.

Can some one please help me to convert (calculated) zero to blank cell in a column? The calculated means zero is not typed in but is calculated from other cells by using formula.

Due to this calculated zero, most of the replies from all experts does not seem to be working. It was my fault as i did not make it clear in the beginning.

I have attached the sample spreadsheet.

Appreciate your help.

thanks.

Note to experts - Please try to reply again. Thank you.

Hey all,

I'm trying to run down through a very long/variable Column Y and add a SUM formula of the cells in Columns J:X to all blank cells in which the row also has an account number in Column A. (I've attached a truncated example of the spreadsheet). Basically, if there is a blank spot in the totals column that also has an account in column A, I need to sum a bunch of the values in between.

Here's a code I've written so far. I tried to add an active cell formula to this to allow the macro to work on any column but had even worse results. Any help would be greatly appreciated.


	VB:
	
 FillEmpty() 
    Dim rCell, rColumn As Range 
    Dim lLastRow As Long 
     
    lLastRow = Range("Y65536").End(xlUp).Offset(0, 0).Row 
     
    Set rColumn = Range("Y1:Y" & lLastRow) 
     
    For Each rCell In rColumn 
        If IsEmpty(rCell) Then 
            If rCell.Offset(0, -25).Value  Empty Then 
                rCell.Formula = "=SUM(J:X,rCell.row)" 
            Else 
                 
            End If 
        Else 
             
        End If 
    Next rCell 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you,

Marcus

I want to use VBA to check to see if a formulas has any references to blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to test
for blanks? Am I wandering into Regular Expression territory with this one?
Or can I do it with VBA?

Thanks

I want to use VBA to check to see if a formulas has any references to blank
cells.

So if my formulas is

=A1+SUM(B2:B30)/Average(C1:C30)+Opcost

I want to know if either A1 is blank, any of the array items in the SUM or
Average are blank, if named cell Opcost is blank etc

Is there a way to evaluate the formula propertly of the cell object to test
for blanks? Am I wandering into Regular Expression territory with this one?
Or can I do it with VBA?

Thanks

Calling all gurus!

Purpose: a drop-down box with no blanks between selections

Detail: I am trying to create an uninterrupted (no blank cells) list based on a list with intermittent blank cells.

Question: What formula (no VBA please) could I use to grab in sequence, data, without grabbing the blank cells?

Example:

(1) List (this list will dynamically change based on previous selections)
Apples
Oranges

Bananas

Cherries
Strawberries

Honey Dew

(2) I want it to read:
Apples
Oranges
Bananas
Cherries
Strawberries
Honey Dew

So that I can link this list to a drop-down for a seamless list.

Hope this helps - let me know if you need more details! Thanks!

I am trying to ignore blank cells in a certain column. Columns A and B contain data. Column A contains number between 2 and 11, and column B contains measured data.
The formula I am using is as follows:

{=VAR(AVERAGE(IF(A1:A100=2,B1:B100,"")),......}

This formula goes on using 2 to 11 in the logic test statement and takes the variance. But, if one of the cells is blank in the B column, an error occurs in the IF statement evaluation, leading to an error as the final answer.
This workbook will be used by others, and there is no way to predetermine which cells will be blank when data is pasted into the worksheet.

Thanks.

I need to find a way to find a blank cell in a range of cells. The catch is, the range changes constantly. So one time it might be A1 to D6 and another it might be A1 to G60. Is there a way I can limit a Find command to seek out only the range on the sheet.

I can have it do an activecell (A1) currentregion selection, but I am not sure how to tell VBA to seach just the highlighted region for a blank cell after that.

Can anyone help me, please?

Now I know i could select ctrl down arrow to select the last cell and then
arrow down again to select the next cell.
If I record this it suggests I am selecting specific cells.
My issue with that is that column A will have a different number of rows
occupied by text and whatnot.
Is there a command or something that would select the first blank cell in
column A?

Thank you in advance. You guys are always willing to give an answer.

G

The following array formula works great when the cells in the specified ranges are not blank. But if I were to include blank cells in the ranges I come up with an #N/A error. I don't know how to include something in the function to add the blank cells as 0.

{=SUM(IF((Sev=1)*(Comp=1),1,0))}

Any help is greatly appreciated!! Thank you!!!!!

Here's my problem.

I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:

Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59

In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.

My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:

=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY")))

This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.

So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?

Hi

How do i replace a blank cell in column "C" & "D" with zero, if there is a corresponding value in column "A". I have attached an example worksheet

Found this code, but it cannot get it to look at column A for a value (i.e. FindLastRow) and if Column C & D have blank cells to replace with zero

Sub FillColBlanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
   col = activecell.column
   'or
   'col = .range("b1").column

   Set rng = .UsedRange  'try to reset the lastcell
   LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
   Set rng = Nothing
   On Error Resume Next
   Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
                  .Cells.SpecialCells(xlCellTypeBlanks)
   On Error GoTo 0

   If rng Is Nothing Then
       MsgBox "No blanks found"
       Exit Sub
   Else
       rng.FormulaR1C1 = "=R[-1]C"
   End If

   'replace formulas with values
   With .Cells(1, col).EntireColumn
       .Value = .Value
   End With

End With

End Sub


I am trying to post the results of one cell to the next blank cell in a different row. For example; I need the results in S3 to post to the next blank cell in row P. I tried the worksheet_change code below but that will only work if I make a manual entry in S3 and does not work if I use a formula in S3 to perform the calculation. My formula in S3 is =MAX(L5:L505). When I hit F9 to recalculate I need the results of S3 to post in row P each time. Perhaps there is a formula that I can use as opposed to VBA.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address  "$S$3" Then Exit Sub 
     
    Application.EnableEvents = False 
    Range("P65536").End(xlUp).Offset(1, 0).Value = Target.Value 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all, I'm hoping someone out there can help me with a problem. I'm using a VBA userform to input data to an Excel spreadsheet. The user inputs data to comboboxes and textboxes. The user is allowed to leave some fields blank but I want to highlight any blank cells in red to remind the user. The blank cells should only be highlighted if there has been data entered in the other cells in the same row i.e. cells in rows that contain no data should remain blank. Any ideas?

I know this type of question has been asked before but each answer seems a bit different to what I want.

Folks,

I am looking for a way to find blank cells in a column(s) and delete the rows corresponding to the cell number. I found this code in one of the previous threads on ozgrid but it just doesnt work for me because it is slowing me down. Please help (I am working on around 60000 rows in Excel 2007)


	VB:
	
 DeleteBlankARows() 
    With Application 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
        Dim r As Long 
        For r = Cells(Rows.Count, 11).End(xlUp).Row To 1 Step -1 
            If Cells(r, 11) = "" Then Rows(r).Delete 
        Next r 
        .Calculation = xlCalculationAutomatic 
        .ScreenUpdating = True 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am also looking for a way to do the reverse viz. for a particular column, I'd like to delete the row corresponding to the non-blank cell in the column. Please help.

This loop is overwriting the first cell in range and not writing to the first blank cell in range.


	VB:
	
 NOTE 
    If Trim(cnote).Value = "" Then 
        cnote.Value = notes.Value 
        Exit For 
    Else 
    End If 
Next cnote 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I take out the exit for , it copies to all cells in range

thanks

Here is the code I have at present


	VB:
	
 simplecopypaste() 
    Workbooks.Open Filename:="G:DATAWork for JCdata source.xls" 
    Range("B2:E2").Select 
    Selection.Copy 
    Windows("Data Destination.xls").Activate 
    Sheets("Sheet1").Select 
    Range("A2").Select 
    ActiveSheet.Paste 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This should open my file, find the relevant data and copy it to the worksheet that contains the macro. At present I can have it paste to the first cell however in future the first cell will have data and as such i need it to paste to the first blank cell in column A. any ideas on how to do this, do i need to get excel to scan from the bottom cell up until it finds a cell with data then paste below it? if so how do i do that.

I would like to be able copy some data from on worksheet to another. It should be pasted in the first blank cell in column A. The kicker is that:

A. There will be an autofilter active so it needs to know to go the first blank cell not the first blank visible cell (sense?)
B. This will be in a loop so if you set a variable like

	VB:
	
rLastRow = Range("A65536").end(xlup) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it will change in the loop so it will just be overwriting itself.

I'm having major problems with my brain functions this morning so any help is appreciated.

Hi,

I am trying to write a macro code that sorts certain rows based on if those rows have blank cells in column G. I have figured out the code to the point where it selects the rows where column G is blank, but then when I want it to sort these rows, it references a set cell. The problem with this is that I want to be able to reuse the macro without having to update it. The rows with blank cells in column G change on a weekly basis (one week it might be row 12 where the blanks start, the next week it might be row 18 where the blanks start).

Is it possible to have the sorting not reference a set cell?


	VB:
	
Application.Calculation = xlCalculationManual 
Dim rng6 As Range, ix6 As Long 
Set rng6 = Intersect(Range("G3: G700 "), ActiveSheet.UsedRange) 
If rng6 Is Nothing Then 
    MsgBox "nothing in Intersected range to be checked/removed" 
    Goto done6 
End If 
For ix6 = rng6.Count To 1 Step -1 
    If Len(Trim(Replace(rng6.Item(ix6).Formula, Chr(160), ""))) _ 
    = 0 Then rng6.Item(ix6).Delete (xlUp) 
Next 
done6: 
Application.Calculation = xlCalculationAutomatic 
 
On Error Resume Next 
Columns("G:G").SpecialCells(xlCellTypeBlanks).EntireRow.Select 'SELECT ENTIRE ROWS
ActiveSheet.UsedRange 'WHERE BLANK CELLS EXIST
 'IN COLUMN G
Selection.Sort Key1:=Range("S9"), Order1:=xlAscending, Key2:=Range("C9") _ 
, Order2:=xlDescending, Key3:=Range("D9"), Order3:=xlDescending, Header:= _ 
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _ 
xlSortNormal 
Range("A1").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you!!

My spreadsheet compares 2 sets of data that are imported into excel. The only thing I cannot figure out is how to add ="" to the blank cells in my data range. The trick is that the data range is not the same number of rows every time. I am thinking a macro that says this will do the trick: If for each row Column A is not empty and if Column H is empty, then add ="" into Column H.

Thanks in advance.

Hi,

I've tried countless time to try and solve my problem, however I'm finally admitting defeat. Can someone please take a look at my problem and offer advice on how to solve this.

Basically I have data in column B (for example of this) and i want to copy each cell to the next blank cell below in column A.

So far everything I've tried has led me to only being able to copy to a preset offset value.

I've been trying to select each cell individually (B2), then offset it to the previous column, if that's not blank, then try the next row, until there's a blank, then copy, and go to the next cell (B3) and so on.

The attached show a before and after of what I'm trying to achieve.

I'm also looking to move further data (6 connecting columns) in the same way. Can you also please specify any slight changes I may need to implement other than the range.

Thanks in advance for all your help.

I'm writing a macro that copies a selection from one sheet and pastes it on a different sheet. However, I need it to paste it in the first BLANK cell in column "A". What would be the string for that? It would have to make that sheet the active sheet and then make the first cell in column A the active cell. However, I can't figure out how to get it to look for the first empty cell. I would guess it would be an "If Then" procedure, correct?

Any help would be greatly appreciated.

Hi

I would like to write a macro that would copy formula in B2 and then copy it down row B until it finds that a cell in row A is blank. This report changes all the time so I never know how much data is in row A. So I want the macro to stop copying the formula down row B when it finds a blank cell in Row A.

I tried the Do Until but it did not do anything unless I had something wrong. I recorded the intial copy B2 to B3 to see what it wrote. I tried an if statement also.

Any help would be apprieciated. I have Excel 2003.

Hi

I would like to copy the results of a calculation in a macro into the first
blank cell in column C. Note that this is NOT the cell below the ast row of
the colum.
There are more various blank and non-blanks below the first blank in column C.

How do I select the first blanc cell in Column C then?
The part of the macro below works perfectly except for the fact that it does
not copy the value in the first blanc cell but in the last cell. Adding a
line + 1 did not work until now.

Your help is really appreciated, Many thanks upfront.

If COMPANY = "SA" Or COMPANY = "sa" Then
' Select the SA 2006_2007 sheet for input
Sheets("2006_2007 BE Invoices Out").Select
Range("C1").Select
'Instruction below Brings me to the last non-blank cell, but need the
line below !!!
Range(Selection.Address).End(xlDown).Select

Sheets("Parameters").Select
Range("E16").Select
Selection.Copy
Sheets("2006_2007 BE Invoices Out").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Hi,

Is the only way to select the first non blank cell in a range to use an
if statement.

e.g If D1 is the first cell in range A1 to F1 with info I want to
return 1 for A to for B and so on. So D1 would return 4

VBA Noob

--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564240


No luck finding an answer? You could always try Google.