Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for IF formula
I have a large worksheet that I need a modified "IF" formula for. The
standard one won't work, and I think a macro is the way to go. ANy suggestions for a macro code to accomplish the following: If A1 contains a text value, leave that text value there and skip to A2 If A1 is blank, look at B1. If B1 contains any of 4 text values AND C1 is 0, then copy B1 to A1. If B1 does not contain one of those text values OR C1=0, then A1 should remain blank. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for IF formula
I am pretty sure this macro will do what you want...
Sub FixColumnA() Dim X As Long Dim LastCell As Long Dim CurrentCell As Range Dim AsciiOne As String Dim FourTextValues As String FourTextValues = AsciiOne & "TextValue1" & AsciiOne & "TextValue2" & _ AsciiOne & "TextValue3" & AsciiOne & "TextValue4" & _ AsciiOne With Worksheets("Sheet1") LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastCell If Len(.Cells(X, "A").Value) = 0 Then If InStr(1, FourTextValues, .Cells(X, "B").Value, _ vbTextCompare) 0 And .Cells(X, "C").Value 0 Then .Cells(X, "A").Value = .Cells(X, "B").Value End If End If Next End With End Sub Rick "richzip" wrote in message ... I have a large worksheet that I need a modified "IF" formula for. The standard one won't work, and I think a macro is the way to go. ANy suggestions for a macro code to accomplish the following: If A1 contains a text value, leave that text value there and skip to A2 If A1 is blank, look at B1. If B1 contains any of 4 text values AND C1 is 0, then copy B1 to A1. If B1 does not contain one of those text values OR C1=0, then A1 should remain blank. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for IF formula
The code I posted earlier could possibly not work correctly as I left out
something in the InStr function call meant to insure small text strings wouldn't accidentally match part of any one of the four TextValues being checked for in Column B. The code below corrects that omission... Sub FixColumnA() Dim X As Long Dim LastCell As Long Dim CurrentCell As Range Dim AsciiOne As String Dim FourTextValues As String FourTextValues = AsciiOne & "TextValue1" & AsciiOne & "TextValue2" & _ AsciiOne & "TextValue3" & AsciiOne & "TextValue4" & _ AsciiOne With Worksheets("Sheet1") LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastCell If Len(.Cells(X, "A").Value) = 0 Then If AsciiOne & .Cells(X, "C").Value & AsciiOne 0 And _ InStr(1, FourTextValues, .Cells(X, "B").Value, _ vbTextCompare) 0 Then .Cells(X, "A").Value = .Cells(X, "B").Value End If End If Next End With End Sub And I forgot to mention it originally, but you have to replace TextValue1, TextValue2, etc. with your actual text values to be checked for in Column B. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I am pretty sure this macro will do what you want... Sub FixColumnA() Dim X As Long Dim LastCell As Long Dim CurrentCell As Range Dim AsciiOne As String Dim FourTextValues As String FourTextValues = AsciiOne & "TextValue1" & AsciiOne & "TextValue2" & _ AsciiOne & "TextValue3" & AsciiOne & "TextValue4" & _ AsciiOne With Worksheets("Sheet1") LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastCell If Len(.Cells(X, "A").Value) = 0 Then If InStr(1, FourTextValues, .Cells(X, "B").Value, _ vbTextCompare) 0 And .Cells(X, "C").Value 0 Then .Cells(X, "A").Value = .Cells(X, "B").Value End If End If Next End With End Sub Rick "richzip" wrote in message ... I have a large worksheet that I need a modified "IF" formula for. The standard one won't work, and I think a macro is the way to go. ANy suggestions for a macro code to accomplish the following: If A1 contains a text value, leave that text value there and skip to A2 If A1 is blank, look at B1. If B1 contains any of 4 text values AND C1 is 0, then copy B1 to A1. If B1 does not contain one of those text values OR C1=0, then A1 should remain blank. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for IF formula
Thanks for your help Rick! It all worked perfectly.
"Rick Rothstein (MVP - VB)" wrote: The code I posted earlier could possibly not work correctly as I left out something in the InStr function call meant to insure small text strings wouldn't accidentally match part of any one of the four TextValues being checked for in Column B. The code below corrects that omission... Sub FixColumnA() Dim X As Long Dim LastCell As Long Dim CurrentCell As Range Dim AsciiOne As String Dim FourTextValues As String FourTextValues = AsciiOne & "TextValue1" & AsciiOne & "TextValue2" & _ AsciiOne & "TextValue3" & AsciiOne & "TextValue4" & _ AsciiOne With Worksheets("Sheet1") LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastCell If Len(.Cells(X, "A").Value) = 0 Then If AsciiOne & .Cells(X, "C").Value & AsciiOne 0 And _ InStr(1, FourTextValues, .Cells(X, "B").Value, _ vbTextCompare) 0 Then .Cells(X, "A").Value = .Cells(X, "B").Value End If End If Next End With End Sub And I forgot to mention it originally, but you have to replace TextValue1, TextValue2, etc. with your actual text values to be checked for in Column B. Rick "Rick Rothstein (MVP - VB)" wrote in message ... I am pretty sure this macro will do what you want... Sub FixColumnA() Dim X As Long Dim LastCell As Long Dim CurrentCell As Range Dim AsciiOne As String Dim FourTextValues As String FourTextValues = AsciiOne & "TextValue1" & AsciiOne & "TextValue2" & _ AsciiOne & "TextValue3" & AsciiOne & "TextValue4" & _ AsciiOne With Worksheets("Sheet1") LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 1 To LastCell If Len(.Cells(X, "A").Value) = 0 Then If InStr(1, FourTextValues, .Cells(X, "B").Value, _ vbTextCompare) 0 And .Cells(X, "C").Value 0 Then .Cells(X, "A").Value = .Cells(X, "B").Value End If End If Next End With End Sub Rick "richzip" wrote in message ... I have a large worksheet that I need a modified "IF" formula for. The standard one won't work, and I think a macro is the way to go. ANy suggestions for a macro code to accomplish the following: If A1 contains a text value, leave that text value there and skip to A2 If A1 is blank, look at B1. If B1 contains any of 4 text values AND C1 is 0, then copy B1 to A1. If B1 does not contain one of those text values OR C1=0, then A1 should remain blank. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula in macro causes macro to fail | Excel Programming | |||
Macro Formula for Max value | Excel Worksheet Functions | |||
Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
Formula in macro help | Excel Programming | |||
Sum Formula in Macro | Excel Programming |