Loop through range and output to another sheet (VBA)

[PaulWJ]

I have data that I import into a Workbook. It doesn't have all the information I need so will be combining it with other data from another workbook (Not ideal, but I'm not in control of the other workbooks).

I don't want to amend the import data, so I want to run a macro to look at each cell in the range in one worksheet, and then put an answer in the corresponding cell in another worksheet. I've tried various different ways, but can't quite get it to work. The most recent attempt is using R1C1 reference style, but I'm getting a Method 'Range' of object'_Worksheet' failed error the first time it hits the IF statement.

Is the code way off base, or does it just need tinkered with? Any suggestions would be appreciated.

The range is D6:JC106

Sub MergeRotas()

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("CDemandsData")
Dim ws2 As Worksheet
Set ws2 = ThisWorkbook.Sheets("Work Rota")
Dim r As Integer
Dim c As Integer

For c = 4 To 263
    For r = 6 To 106
    If ws1.Range(c, r).Value = "Holiday" Then
        ws2.Range(c, r).Value = "Leave"
    End If

    Next r
Next c

End Sub

 

[Cubist]

The way you're referencing the address is Cells method. Also it's row first, then column. Try:

Sub MergeRotas()

    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Sheets("CDemandsData")
    Dim ws2 As Worksheet
    Set ws2 = ThisWorkbook.Sheets("Work Rota")
    Dim r As Long
    Dim c As Long

    For c = 4 To 263
        For r = 6 To 106
            If ws1.Cells(r, c).Value = "Holiday" Then
                ws2.Cells(r, c).Value = "Leave"
            End If
        Next r
    Next c

End Sub

 

[dmt32]

Hi
Looping each cell in that manner is generally not recommended as it can prove impossibly slow over a large data set.
However, as yours is not too large I have had a little tinker updating your approach.

See if this update will do what you want.

Sub MergeRotas()
   
    Dim ws(1 To 2)  As Worksheet
    Dim cell        As Range
   
    Set ws(1) = ThisWorkbook.Worksheets("CDemandsData")
    Set ws(2) = ThisWorkbook.Worksheets("Work Rota")
   
    Application.ScreenUpdating = False
    For Each cell In ws(1).Range("D6:JC106")
        If UCase(cell.Value) = "HOLIDAY" Then
            ws(2).Cells(cell.Row, cell.Column).Value = "Leave"
        End If
    Next cell
   
End Sub

Dave

 

[PaulWJ]

Thanks both. I'll give them a whirl. Appreciate the help.