Solved: how do i use a formula in a cell only if another cell on that row is not empty?

[oli123]

Seems like a simple problem that is quite hard to explain.

I have a workbook which processes information and involves a fair bit of automatically filling in data from other sources using macros.

I would like column at the far right of the data (e.g. column Y) to carryout the following formula "=NETWORKDAYS(a2,m2)-1" ONLY if there is actually data there. There could be any amount of data imported so it needs to use that formula if there is anything present in column A for that row.

If I just fill down the formula into every cell in column Y it obviously leaves #NAME errors on all the blank records for the entire spreadsheet. If i use the following conditional formula: "=IF(A2="","",NETWORKDAYS(a2,m2)-1) it works fine but makes the file size HUGE since it is in every cell for that column.

There must be a simple way around this, iv not used excel for a while so am very rusty...

any help much appreciate...

[CBrine]

oli,
Do you fill the formula in using vba, or are you doing it manually? If it's vba, why not check the cell A? and use that as a condition for adding the formula vs just using a fill down?


Sub addFormula()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1", ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp))
If cell <> "" Then
cell.Offset(0, 24) = "=NETWORKDAYS(" & cell.Address & "," & cell.Offset(0, 12).Address & ")-1"
End If
Next cell
End Sub


HTH
Cal

[Bob Phillips]

IF you find the bottom dynamically, you shouldn't have as many formulae



Dim iLastRow As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("Y2").Resize(iLastRow - 1).Formula = "=NETWORKDAYS(A2,M2)-1"

[lucas]

That's very elegant Cal....

[CBrine]

Thanks Lucas.

[lucas]

Hi Bob,
I found that yours requires some data in column A to work. So if there are blanks it doesn't deal with them....

[oli123]

Thanks to everyone who replied, Im sure that the dynamic list way will work fine and all suggestions sound good.

To answer the question above I copied the formula down manually and left it there, but I will just stick the code in my macro that is already running to populate that table...

I asked partly because I thought it is something that would be used quite often and thought that there might be an in built feature to do it (like some kind of conditional formatting), but obviously not, o well.

Cheers again.

[CBrine]

oli,
You could use conditional formating to hide the #name errors, by coloring them the same color as the sheet background. Sounds like it might be an option.

1. Just select the range
2. select Format....COnditional formating
3. In the dialog select formula is
4. Enter =Iserror("First cell of selection address")
5. Change the font color to the background color.
6. Press OK.

HTH
Cal

[oli123]

haha o dear something bizarre has happened...

i put in an altered version of the code someone very kindly set me up with aboveSub addFormula()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1", ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp))
If cell <> "" Then
cell.Offset(1, 21) = "=NETWORKDAYS(" & cell.Address & "," & cell.Offset(0, 18).Address & ")-1"
End If
Next cell
End Sub

It populates the spreadsheet ok in the right places as far as I can see but now, guess what? Excel denies all existance of the NETWORKDAYS function, comes back with #NAME errors and doesnt even have NETWORKDAYS listed in the function list. I have checked and the Analysis Toolpack is still installed, the Analysis pack for VBA wasnt but is now.

so what have i done? im confused

[CBrine]

oli,
I just ran the code on my system without the toolpack, and I get the #names error, but when I loaded the toolpak, I get a #value error, so I don't think it's the addin that is the issue.

I would suggest you check your references to make sure you are not missing anything.

1. Open the VBE editor
2. Select tools...references
3. Make sure none of them are marked as MISSING

HTH
Cal

[oli123]

erm, had a look, i have hundreds that are missing (well have empty check box anyway, it doesnt say 'missing' anywhere but i assume thats what you mean).

Is this because I just installed every addon available in the hope it might bring back my NETWORKDAYS function? im guessing it might be.

Do i really need to select them all, and is this going to affect the speed of excel or anything?

also, Im still confused as to why NETWORKDAYS is no longer available on my insert function list. Really confused.

[CBrine]

No,no,no,
Unchecked is OK.:thumb MISSING would actually show up in the beside the box if the reference was actually missing. When that happens some of your functions start to react very strangely.

Try typing the formula in manually, and see if it works then. If it doesn't work then, I would suggest you unload and load the addin again. Then try the function manually again.

HTH
Cal

[mdmackillop]

NetWorkDays requires the Analysis ToolPack (Tools/Add-Ins)
A missing reference will say "missing" adjacent to the names. Uninstalled references are not missing.

[oli123]

thanks for clearing that up, i dont have any missing then.

Cbrine, as i say NETWORKDAYS just isnt recognised anymore, it doesnt work in the sheet when typed normally and isnt in the functions list.

I am off home now so will try uninstalling and reinstalling tommorow. I have also tried other workbooks that use NETWORKDAYS and they all dont have it anymore, so it definately is a global excel issue not just with that one spreadsheet.

If anyone has any other ideas where the function might have got to that would be great.

[CBrine]

oli,
That's really strange, since the networkdays function appears in the function list, even when the analysis toolpak is not installed??? It just doesn't work without the addin. I'm not sure where the problem is, but it sounds like your addin may have gotten corrupted somehow? Never seen this myself but I guess it's possible.
Let me know how the uninstall/reinstall goes tomorrow.

Cal

[oli123]

ok, all i had to do was deselect Anaylsis Pack from Addins, close excel, open it again and reselect and NETWORKDAYS cam back.

Must have been some silly excel problem, its ok now, thanks.

Will just test that the VBA code works now I have the function back, if thats ok im all sorted, will let you know.

[oli123]

o god, everytime i run the import i made with the added code it makes NETWORKDAYS disappear.

Here is the current code im using:
Sub addFormula()
Dim cell As Range
For Each cell In ActiveSheet.Range("A1", ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp))
If cell <> "" Then
cell.Offset(1, 21) = "=NETWORKDAYS(" & cell.Offset(1, 0).Address & "," & cell.Offset(1, 18).Address & ")-1"


End If
Next cell
End Sub


its sooo weird i have literally no idea why this is happening

[CBrine]

Can you post a copy of the code you are using to import as well? I've run my code with the addin added and not added, and it doesn't seem to effect it? Maybe try running your code by itself and see if the network days stops working. We need to try and isolate it to the point your losing the addin.

[oli123]

Can you post a copy of the code you are using to import as well? I've run my code with the addin added and not added, and it doesn't seem to effect it? Maybe try running your code by itself and see if the network days stops working. We need to try and isolate it to the point your losing the addin.

Hi Cbrine, here is my code, its VERY amateurish and longwinded cus I dont have a book of vba commands at the moment so have to rely on bits and bobs copied from the web:

Sub RetrieveFileName()
Dim sFileName As String
Dim LastCell As Range
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisWB As String
Dim PODwb As Workbook
'Show the open dialog and pass the selected _
file name to the String variable "sFileName"

sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
' ThisWB = Workbook.Name
'got file name, now open
Workbooks.Open (sFileName)
sFileName = ActiveWorkbook.Name

'Range("A2:Q17").Select

Range("A1").Select
Selection.CurrentRegion.Select ' select all cells in this sheet
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


' copy cells selected in the new sheet on last line

Windows(sFileName).Activate
Selection.Copy
Windows("DHL late breakdown template.xls").Activate
Worksheets("AllData").Activate
Range("A2").Select
ActiveSheet.Paste

Call addFormula

End Sub


Sorry about that mess, I know that alot of that code may be unneccesary and there is a lot of bad practice in there (like not changing the name of the import to " data import" and leaving as just "RetrieveFileName").

[oli123]

update -

just ran my code by itself, everything was fine.

than ran both routines ("retrievefilename" (which actually opens it too) and addformula") and everthing was still fine.

Went and checked out NETWORKDAYS again, it hasnt disappeared this time and still works. Now here is the interesting bit: there are now 2 NETWORKDAYS commands in my functions list, 1 as normal and 1 in lowercase that is identical in every other way.

Is my version of Excel corrupt or something? Im happy that NETWORKDAYS is back but still baffled as to what has been happening to it.

[CBrine]

oli,
Yeah, that sounds weird? I'm not sure what would cause something like that. Well.....That's strange, the other day when I checked for networkdays without the addin, I could swear it was there, now it only seems to appear when I have the addin???
Oh well, it must be something to do with the addin vs any of the code. My only suggestion at this point is to cross your fingers and hope it's gone away:-)

[oli123]

oli,
Yeah, that sounds weird? I'm not sure what would cause something like that. Well.....That's strange, the other day when I checked for networkdays without the addin, I could swear it was there, now it only seems to appear when I have the addin???
Oh well, it must be something to do with the addin vs any of the code. My only suggestion at this point is to cross your fingers and hope it's gone away:-)

lol yeah i think i am just going to have to hope for the best. Well Thanks for your help, i will try and mark this solved since the original problem was solved and this new weirdness seems to be a one off problem...