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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

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
Back
Top