VBA - adding condition to IF

[Luke777]

Hi all,

I'm using the following code on my sheet

Sub FillData4()


Dim ws1 As Worksheet

Set ws1 = Worksheets(1)

    For Each Cell In ws1.Range("C5:C148", Range("C5").End(xlToRight))
        If Cell.Value = "" Then
            Cell.Value = Cell.Offset(-1, 0).Value
        End If
    Next Cell
    
End Sub

The code looks in the range for blank cells. Where it finds a blank cell it then changes the value to match the same as the cell above.

This works perfectly for what I want with one exception. Each column contains the string "END" (could be anywhere in that column). Where "END" is found in that column, I would like it to then move on to the next column and *not* change all the blanks under "END" within the range, to "END".

Hopefully that makes sense

Any ideas on this one?

Thanks!

 

[mikerickson]

Instead of looping, try

With ws1.Range("C5:C148", Range("C5").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = IF(ISNUMBER(MATCH("end",R5C:R[-1]C,0)), """", R[-1]C)"
    .Value = .Value
End With

 

[Luke777]

Instead of looping, try

With ws1.Range("C5:C148", Range("C5").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = IF(ISNUMBER(MATCH("end",R5C:R[-1]C,0)), """", R[-1]C)"
    .Value = .Value
End With

I'm getting a compile error: syntax error when I run that on the formula line

 

[Luke777]

Instead of looping, try

With ws1.Range("C5:C148", Range("C5").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = IF(ISNUMBER(MATCH("end",R5C:R[-1]C,0)), """", R[-1]C)"
    .Value = .Value
End With

Tried changing to

    With ws1.Range("C5:C148", Range("C5").End(xlToRight)).SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 "= IF(ISNUMBER(MATCH(""END"",R5C:R[-1]C,0)), """", R[-1]C)"
    .Value = .Value
    End With

which adds opening marks and doubles for END string but now I get a "compile error: Invalid use of property" - didn't realise HOAs came after you on excel too :D

 

[mikerickson]

We both messed up.
It needs one more = sign

.FormulaR1C1 = "= IF(ISNUMBER(MATCH(""END"",R5C:R[-1]C,0)), """", R[-1]C)"

 

[Luke777]

We both messed up.
It needs one more = sign

.FormulaR1C1 = "= IF(ISNUMBER(MATCH(""END"",R5C:R[-1]C,0)), """", R[-1]C)"

Teamwork lol

Now, bizarrely, its telling me runtime error 1004: "no cells were found" which I'm guessing means I need to add an "on error go to 0" somewhere along the line... only thing is, in the specified range there's tons of blank cells so I don't know why its not finding any of them... could it be because the very first cell in the range isn't blank which would be fixed by "on error"?

 

[mikerickson]

I'm guessing that the cells your eye sees as blank might have formulas that evaluate to "", which SpecialCell won't see as blank.

Try

With ws1
    With Range(.Range("C5:C148"), .Range("C5").End(xlToRight))
        For j = 1 to .Columns.Count
            For i = 1 to .Rows.Count
                If .Cells(i,j).Value = "End" Then
                    Exit For
                ElseIf .Cells(i, j).Value = "" Then
                    Cells(i,j).Value = .Cells(i - 1, j).Value
                End If
            Next i
        Next j
    End With
End With/CODE]

 

[Luke777]

I'm guessing that the cells your eye sees as blank might have formulas that evaluate to "", which SpecialCell won't see as blank.

They *should* be totally blank as any formulas in the range are removed before this code runs (copy the results of any formulas, then clear the range, then paste the values *only* back in). I'll verify by running on a completely fresh, blank sheet.

And I'm a little bit confused. The OP starts by saying that you want to look for the values in column A and move A:D to the sheet named by the column A value.
But towards the end you mention that END in a column should move things to the next column.
Are you creating a sheet for values in column A or for all the cells i the selected range?

I'm very confused by this... I'm not sure as I mentioned moving data to any sheet named by a column value?

 

[mikerickson]

I got this thread confused with another question that I'm working on.
See my edit above (post #7)

 

[Luke777]

I got this thread confused with another question that I'm working on.
See my edit above (post #7)

Had some funky results with that code. I think that's on my poor explanation skills though. I'll show you exactly what I'm after.

This is my starting data

12
	12
		12
			12
		END
END
			END
	END

This would be my desired result

12
12	12
12	12	12
12	12	12	12
12	12	END	12
END	12		12
	12		END
	12
	12
	END

note the '12' value is just a placeholder and could be anything