VBA set columns in a row as dynamic range

saku82

New Member
Joined
Oct 22, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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

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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You need to qualify the Cells with the sheet, not just the Range.
Try
VBA Code:
With Sheets("Source")
   Set loopRange = .Range(.Cells(7, 2), .Cells(7, LastColumn))
End With
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,826
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,611
Messages
5,765,414
Members
425,285
Latest member
andypandypoo

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