fill out a column when on a specific value

bulkodave

Board Regular
Joined
Apr 27, 2013
Messages
68
Hello
Thank you for reading my post.

I will try to explain my issue as clearly as i can.

A have a range on a spreadsheet which a macro pastes a value only for another spreadsheet.

This range is (b11:ah57)

in range (d60:ah60) there is numeric values ( between 1 to 7 )

What i need is if the cell value in the range (d60:ah60) is either 1 or 7 then any blank cell in that column in the range B11:ah57 will put W in it, any cell which has a value in it is left alone.

I have made this macro below but will not fill it out, but if i clear all (b11:ah57) it works. I think it might be something on the value only paste even though when the cell is checked it is blank.

any help will be greatly appreciated

Mnay Thanks
Dave

Sub weekends()
Dim cell As Range, raTest As Variant
For Each cell In Range("d60:ah60")
If cell = 7 Or cell = 1 Then
On Error Resume Next
Set raTest = Range(cell.Offset(-49, 0), cell.Offset(-4, 0)).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If IsEmpty(raTest) = False Then
raTest.Value = "W"
End If
End If
Next

End Sub
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,696
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
Sub weekends()


    Dim rng As Range
    
    Application.ScreenUpdating = False
        
    For Each rng In Range("D60:AH60")
        With rng
            If .Value = 1 Or .Value = 7 Then .Cells(11, .Column).Resize(47).SpecialCells(xlCellTypeBlanks) = "W"
        End With
    Next rng
    
    Application.ScreenUpdating = True


End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,839
Members
413,943
Latest member
Dhornsby21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top