VBA: How do I Format a Custom Range from Dimmed Variables

Ottsel 0
Viewed 39

I'm encountering an issue with my code below. My structure to execute the custom copy and paste is where I hit a road block. Could someone help me with my syntax, please? Still learning, so I haven't figured everything out perfectly yet, so any tips, tricks of examples would be greatly appreciated.

Dim M As Long  '...Prior Mat'l Column
Dim BM As Long '...BID Mat'l Column
Dim i As Long
Dim c As Range '...Range to search in
Dim rng As Range: Set rng = Sheets("BID").Range("B8:L9")
Dim r As Range: Set r = Rows("8:9") '...Precaution step
    
'...Find Prior Mat'l Column
For Each c In Rows("8").Cells
    If c.Value = "Prior" And Cells(9, c.Column).Value = "Lumber" Then
        M = c.Column
    End If
Next c

BM = rng.Find(What:="Mat'l", lookat:=xlPart, MatchCase:=False).Column

For i = 11 To 749 Step 82
    Range(BM & i ":" BM & i + 10).Copy '... where I'm encountering an issue
    Cells(i, M).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
Next i
vba loops excel
1 Answers

The format:

Range(BM.Value & i & ":" & BM.Value & i + 10)

Was what I was looking for, but as they mentioned I would still have some issues, which I found the solution by restructuring it all together, but the explanation helped a ton regardless. Here is what I came up with for the solution:

    For i = 11 To 749 Step 82
    For j = i To i + 9
        If Cells(j, BM).Value > 0 Then
            Cells(j, BM).Copy
            Cells(j, M).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If
    Next j
Next i

Thanks again.

Ottsel0
Related