| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get an empty cell with a formula
I have a routine that runs when I save the workbook. It looks along rows 3 -
23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
If I understand you correctly then this logic should work:
If Application.CountA(Range("A" & i & ":AF" & i)) 0 And (Application.CountA(Range("D" & i & ":G" & i)) < 4 Or Range("E" & i).Value = "") Then Cheers, Dave "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
Use CountBlank instead.
If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
Thanks for your help but both sugetions above won't work. However I have
found a solution. Since column E will be filled in automaticaly the code for the Counta will always be 1 or greater so all I need to do is change the first par from 0 to 1 i.e. instead of If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then I use: If Application.CountA(Range("A" & i & ":AF" & i)) 1 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then My code now works. Thanks for your help. "Tom Ogilvy" wrote: Use CountBlank instead. If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
Since countblank will see the result of "" produced by your formula as a
blank, it is unclear why you say it won't work, but I would assume the problem lies in implementation rather than in the suggestion. Glad you figured out your own problem. -- Regards, Tom Ogilvy "Keith" wrote: Thanks for your help but both sugetions above won't work. However I have found a solution. Since column E will be filled in automaticaly the code for the Counta will always be 1 or greater so all I need to do is change the first par from 0 to 1 i.e. instead of If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then I use: If Application.CountA(Range("A" & i & ":AF" & i)) 1 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then My code now works. Thanks for your help. "Tom Ogilvy" wrote: Use CountBlank instead. If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
Hi Tom,
I did try your code. the whole for next loop now looks like this For i = 3 To 23 If Application.CountBlank(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then flag = True Exit For End If Next i But this still dosn't work. As I said I did find a solution, but I didn't want you to think that I hadn't tried your method. Thanks Again Keith "Tom Ogilvy" wrote: Since countblank will see the result of "" produced by your formula as a blank, it is unclear why you say it won't work, but I would assume the problem lies in implementation rather than in the suggestion. Glad you figured out your own problem. -- Regards, Tom Ogilvy "Keith" wrote: Thanks for your help but both sugetions above won't work. However I have found a solution. Since column E will be filled in automaticaly the code for the Counta will always be 1 or greater so all I need to do is change the first par from 0 to 1 i.e. instead of If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then I use: If Application.CountA(Range("A" & i & ":AF" & i)) 1 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then My code now works. Thanks for your help. "Tom Ogilvy" wrote: Use CountBlank instead. If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
If the code you used was the code you showed, then you didn't try out my
suggestion. My suggestion was: If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then to determine if the row was not all blank and then, in that case Dx:Gx was not filled. As I said, the problem was in your implementation, not the suggestion. -- Regards, Tom Ogilvy "Keith" wrote: Hi Tom, I did try your code. the whole for next loop now looks like this For i = 3 To 23 If Application.CountBlank(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then flag = True Exit For End If Next i But this still dosn't work. As I said I did find a solution, but I didn't want you to think that I hadn't tried your method. Thanks Again Keith "Tom Ogilvy" wrote: Since countblank will see the result of "" produced by your formula as a blank, it is unclear why you say it won't work, but I would assume the problem lies in implementation rather than in the suggestion. Glad you figured out your own problem. -- Regards, Tom Ogilvy "Keith" wrote: Thanks for your help but both sugetions above won't work. However I have found a solution. Since column E will be filled in automaticaly the code for the Counta will always be 1 or greater so all I need to do is change the first par from 0 to 1 i.e. instead of If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then I use: If Application.CountA(Range("A" & i & ":AF" & i)) 1 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then My code now works. Thanks for your help. "Tom Ogilvy" wrote: Use CountBlank instead. If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How can I get an empty cell with a formula
Tom
You are quite right. My sincere apologies. I don't know what the time was with you yesterday but for me it was getting to the end of a rather stressful day. Your solution does work. Thanks again Keith "Tom Ogilvy" wrote: If the code you used was the code you showed, then you didn't try out my suggestion. My suggestion was: If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then to determine if the row was not all blank and then, in that case Dx:Gx was not filled. As I said, the problem was in your implementation, not the suggestion. -- Regards, Tom Ogilvy "Keith" wrote: Hi Tom, I did try your code. the whole for next loop now looks like this For i = 3 To 23 If Application.CountBlank(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then flag = True Exit For End If Next i But this still dosn't work. As I said I did find a solution, but I didn't want you to think that I hadn't tried your method. Thanks Again Keith "Tom Ogilvy" wrote: Since countblank will see the result of "" produced by your formula as a blank, it is unclear why you say it won't work, but I would assume the problem lies in implementation rather than in the suggestion. Glad you figured out your own problem. -- Regards, Tom Ogilvy "Keith" wrote: Thanks for your help but both sugetions above won't work. However I have found a solution. Since column E will be filled in automaticaly the code for the Counta will always be 1 or greater so all I need to do is change the first par from 0 to 1 i.e. instead of If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then I use: If Application.CountA(Range("A" & i & ":AF" & i)) 1 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then My code now works. Thanks for your help. "Tom Ogilvy" wrote: Use CountBlank instead. If Application.CountBlank(Range("A" & i & ":AF" & i)) < 32 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then -- Regards, Tom Ogilvy "Keith" wrote: I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunatly I now need column E to be automaticaly entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it?I have a routine that runs when I save the workbook. It looks along rows 3 - 23 and columns A - AF. If there is an entry in any of the cells in a row then the cells for that row in columns D - G must have a value. The mail part of the code looks like this: If Application.CountA(Range("A" & i & ":AF" & i)) 0 And Application.CountA(Range("D" & i & ":G" & i)) < 4 Then where i is the value in a for next loop going from 3 - 23. This routine works great. Unfortunately I now need column E to be automatically entered based on a Vlookup and the value in column D. The formula for E is: =IF(D3<"",VLOOKUP(D3,HospitalCity,2),"") This works as well but for some reason column E now has a count of 1. Is there any way that I can have the Counta of column E equal 0 if there is no entry in it? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for empty cell | Excel Worksheet Functions | |||
| format a cell with a formula so an empty reference cell shows blan | Excel Discussion (Misc queries) | |||
| formula, move to previous cell when the current cell=0 or empty | Excel Discussion (Misc queries) | |||
| How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
| Can I empty the cell who has formula in it | Excel Worksheet Functions | |||
Linear Mode
