Guys I need your help with this one, I'm struggling with it for hours now and don't know how to solve it.
loopRange --> I would like to set it dynamically based if row 7 has values (B7: Last Column). The range would be B7:AG7 if all filled with values.
It works with srcRange but when I try
it returns runtime error.
loopRange --> I would like to set it dynamically based if row 7 has values (B7: Last Column). The range would be B7:AG7 if all filled with values.
It works with srcRange but when I try
VBA Code:
Set loopRange = Sheets("Source").Range(Cells(7, 2), Cells(7, LastColumn))
it returns runtime error.
VBA Code:
Sub Test()
Dim cell As Range
Dim cell2 As Range
Dim LastColumn As Long
Dim LastRow As Long
With Sheets("Source")
LastColumn = .Range("B7:B" & Sheets("Source").Columns.Count).End(xlToRight).Column
End With
Dim loopRange As Range
Set loopRange = Sheets("Source").Range("B7:AG7")
With Sheets("Report")
LastRow = .Range("B3:B" & Sheets("Report").rows.Count).End(xlDown).Row
End With
Dim srcRange As Range
Set srcRange = Sheets("Report").Range("B3:B" & LastRow)
For Each cell In loopRange
'If cell.Offset(0, 3).Value = Null Then
cell.Offset(1, 0).Copy
For Each cell2 In srcRange
If cell2.Value = cell.Value Then
cell2.Offset(0, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Next cell2
cell.Offset(2, 0).Copy
For Each cell2 In srcRange
If cell2.Value = cell.Value Then
cell2.Offset(0, 4).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Next cell2
Next cell
End Sub