View Full Version : Solved: Tweak Macro Not To Eliminate Numbers
Barryj
04-25-2011, 09:18 AM
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
04-25-2011, 11:00 AM
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
04-25-2011, 11:28 AM
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
04-25-2011, 01:55 PM
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
04-26-2011, 06:27 AM
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
04-26-2011, 07:48 AM
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
04-26-2011, 08:22 AM
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
04-26-2011, 09:00 AM
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
04-26-2011, 09:35 AM
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
04-26-2011, 12:01 PM
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
04-26-2011, 12:19 PM
Thanks xld now working, will mark as solved.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.