Solved: Tweak Macro Not To Eliminate Numbers

[Barryj]

In the attached workbook I have A/L in column C and when the macro fires if A/L is in column B the it copies it over.

The problem I am having is that there is also numbers in column B, I don't want the numbers to be removed as this code is currently doing, how can I get it to only copy to the cells that have A/L and leave the other cells with numbers alone.

[Bob Phillips]

Private Sub CommandButton2_Click()
Dim Lastrow As Long

With ActiveSheet

With .UsedRange.Rows

Lastrow = .Count + .Cells(1, 1).Row - 1
End With

With .Range("B5").Resize(Lastrow - 4).SpecialCells(xlCellTypeBlanks)

.FormulaR1C1 = "=IF(RC[1]=""A/L"",""A/L"")"
End With
End With
End Sub

[Barryj]

Thanks xld, works fine just one small problem, can it be modified to skip blank cells in the range.

Should have thought about that earlier.

Thanks again.

[Bob Phillips]

Do you mean this?



Private Sub CommandButton2_Click()
Dim Lastrow As Long
Dim rng As Range

With ActiveSheet

With .UsedRange.Rows

Lastrow = .Count + .Cells(1, 1).Row - 1
End With

On Error Resume Next
Set rng = .Range("B5").Resize(Lastrow - 4).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then

With rng
.FormulaR1C1 = "=IF(RC[1]=""A/L"",""A/L"","""")"
End With
End If
End With
End Sub

[Barryj]

Thanks xld, giving this some more thought, I think it would be better if I could steer away form the macro placing formulas down the column.

If data is insertered later it will automatically fill the other column, which I don't want to happen.

So I think a solution without inserting formulas would suit better in the long run if that is not to much of a problem to alter what has already been done.

Thanks again

[Bob Phillips]

Private Sub CommandButton2_Click()
Dim Lastrow As Long
Dim rng As Range

With ActiveSheet

With .UsedRange.Rows

Lastrow = .Count + .Cells(1, 1).Row - 1
End With

On Error Resume Next
Set rng = .Range("B5").Resize(Lastrow - 4).SpecialCells(xlCellTypeBlanks)
On Error Goto 0

If Not rng Is Nothing Then

With rng
.FormulaR1C1 = "=IF(RC[1]=""A/L"",""A/L"","""")"
.Value = .Value
End With
End If
End With
End Sub

[Barryj]

Thanks again xld, Just one last request, can I add mutiplue criteria to the macro, my colleague has thrown up a senerio where two other factors maybe required to be entered. OFF and SICK that will be the last alteration, otherwise it's working as it should.

Thankyou again

[Bob Phillips]

I assume that you mean



Private Sub CommandButton2_Click()
Dim Lastrow As Long
Dim rng As Range

With ActiveSheet

With .UsedRange.Rows

Lastrow = .Count + .Cells(1, 1).Row - 1
End With

On Error Resume Next
Set rng = .Range("B5").Resize(Lastrow - 4).SpecialCells(xlCellTypeBlanks)
On Error Goto 0

If Not rng Is Nothing Then

With rng
.FormulaR1C1 = "=IF(OR(RC[1]=""A/L"",RC[1]=""OFF"",RC[1]=""SICK"",RC[1],"""")"
.Value = .Value
End With
End If
End With
End Sub

[Barryj]

Thanks xld, the macro is stopping at this line
.FormulaR1C1 = "=IF(OR(RC[1]=""A/L"",RC[1]=""OFF"",RC[1]=""SICK"",RC[1],"""")"

runtime error 1004
application defined or object defined error

any thoughts

[Bob Phillips]

Missing bracket



Private Sub CommandButton2_Click()
Dim Lastrow As Long
Dim rng As Range

With ActiveSheet

With .UsedRange.Rows

Lastrow = .Count + .Cells(1, 1).Row - 1
End With

On Error Resume Next
Set rng = .Range("B5").Resize(Lastrow - 4).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then

With rng
.FormulaR1C1 = "=IF(OR(RC[1]=""A/L"",RC[1]=""OFF"",RC[1]=""SICK""),RC[1],"""")"
.Value = .Value
End With
End If
End With
End Sub

[Barryj]

Thanks xld now working, will mark as solved.