Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
I want to go down a column M and look at each cells value and if it is
blank, I want to look at the cells value immediately to the right N and check if it is blank. It they are both blank null, I want to enter the word Funded in the cell in column M. If either cell has a value/text in it I dont want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Hi Todd
remove the word "NOT" from the IF statement If n.Value = "" And n.Offset(0, 1).Value = "" Then Cheers JulieD "Todd" wrote in message ... I want to go down a column "M" and look at each cells value and if it is blank, I want to look at the cells value immediately to the right "N" and check if it is blank. It they are both blank "null", I want to enter the word "Funded" in the cell in column "M". If either cell has a value/text in it I don't want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Sub FixStatus()
Dim i As Long Dim LastRow As Variant LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 1 to LastRow If Cells(i,"M").Value = "" And Cells(i,"N").Value = "" Then Cels(i,"N").Value = "Funded" End If Next n End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Todd" wrote in message ... I want to go down a column "M" and look at each cells value and if it is blank, I want to look at the cells value immediately to the right "N" and check if it is blank. It they are both blank "null", I want to enter the word "Funded" in the cell in column "M". If either cell has a value/text in it I don't want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Slight correction
Sub FixStatus() Dim i As Long Dim LastRow As Long LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 2 to LastRow If Cells(i,"M").Value = "" And Cells(i,"N").Value = "" Then Cels(i,"N").Value = "Funded" End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Sub FixStatus() Dim i As Long Dim LastRow As Variant LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 1 to LastRow If Cells(i,"M").Value = "" And Cells(i,"N").Value = "" Then Cels(i,"N").Value = "Funded" End If Next n End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Todd" wrote in message ... I want to go down a column "M" and look at each cells value and if it is blank, I want to look at the cells value immediately to the right "N" and check if it is blank. It they are both blank "null", I want to enter the word "Funded" in the cell in column "M". If either cell has a value/text in it I don't want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Bob,
Thanks, but I miss stated that Column "N" must be null, instead it must have somthing in it. So "M" must be null and "N" must have a number or text in it to put funded in "N". Sorry and thanks for any help. "Bob Phillips" wrote: Sub FixStatus() Dim i As Long Dim LastRow As Variant LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 1 to LastRow If Cells(i,"M").Value = "" And Cells(i,"N").Value = "" Then Cels(i,"N").Value = "Funded" End If Next n End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Todd" wrote in message ... I want to go down a column "M" and look at each cells value and if it is blank, I want to look at the cells value immediately to the right "N" and check if it is blank. It they are both blank "null", I want to enter the word "Funded" in the cell in column "M". If either cell has a value/text in it I don't want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Hi Todd
(still putting funded in M not N) try Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value < "" Then n.Value = "Funded" Else End If Next n End Sub Cheers JulieD "Todd" wrote in message ... Bob, Thanks, but I miss stated that Column "N" must be null, instead it must have somthing in it. So "M" must be null and "N" must have a number or text in it to put funded in "N". Sorry and thanks for any help. "Bob Phillips" wrote: Sub FixStatus() Dim i As Long Dim LastRow As Variant LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 1 to LastRow If Cells(i,"M").Value = "" And Cells(i,"N").Value = "" Then Cels(i,"N").Value = "Funded" End If Next n End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Todd" wrote in message ... I want to go down a column "M" and look at each cells value and if it is blank, I want to look at the cells value immediately to the right "N" and check if it is blank. It they are both blank "null", I want to enter the word "Funded" in the cell in column "M". If either cell has a value/text in it I don't want to do anything. I tried to work something out but with no success. Below is my try at the code. Thanks for any help! Sub FixStatus() Dim LastRow As Variant LastRow = Application.WorksheetFunction.CountA(Range("A:A")) For Each n In ActiveSheet.Range("M2", "M" & LastRow) If n.Value = "" And n.Offset(0, 1).Value = Not "" Then n.Value = "Funded" Else End If Next n End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statement
Bob has given you a nice code, just by oversight he put 'funded' in
worng column. Also if you want the other column not to be empty all you have to do is use a 'Not'. SO all you have to do is - Sub FixStatus() Dim i As Long Dim LastRow As Variant LastRow = Cells(Rows.Count,"M").End(xlUp).Row For i = 1 to LastRow If IsEmpty(Cells(i,"M"))And Not IsEmpty(Cells(i,"N")) Then Cels(i,"M").Value = "Funded" End If Next n End Sub *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF statement inside a SUMIF statement.... or alternative method | Excel Worksheet Functions | |||
Reconcile Bank statement & Credit card statement & accounting data | Excel Worksheet Functions | |||
Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |