Macro for IF formula

[richzip]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[richzip]

Thanks for your help Rick! It all worked perfectly.