CODEWITHSUNDEEP
vbaexcel
Dad Walsh
Dad Walsh

Reputation: 15

Excel VBA to Paste Formula to Variable Range

I am attempting to create a VBA code that will paste a formula to Variable Range of both columns and cells. I have the start of a code I thought I could modify, but I have been unsuccessful.

I have a sheet (see image) that has a variable range between A2 & ? I need to paste into the area C3 to the end of rows and columns a formula that will take the value in B and divide it by the number of columns. I thought I had a start but I am failing.

Please assist. "Start" Code Follows

Sub QtyByWks()
    Dim M As Long, N As Long, i As Long, x As Long, j As Long
    M = Sheet10.Cells(1, Columns.count).End(xlToLeft).Column
    N = Sheet10.Cells(Rows.count, "A").End(xlUp).Row
    j = 3
    For x = 1 To M
        For i = 1 To N
            If Cells(i, "B").Value > 0 Then
                Cells(j, "C").Value = Cells(i, "B").Value
                j = j + 2
            End If
        Next i
    Next x
End Sub

Also note, Both Rows and Columns are Variable via an additional VBA [Capture of Worksheet]

Thanks in advance for the assist

[1]: https://i.sstatic.net/xG6YN.png

Upvotes: 1

Views: 1686

Answers (1)

Yoni
Yoni

Reputation: 165

Hard to tell what sort of errors/issues you had with your code since you haven't provided much info. Either way, I'll take a stab at adjusting what you provided to do what I THINK you're trying to do:

Sub QtyByWks()
    Dim M As Long, N As Long, i As Long, x As Long, j As Long
' Changed the formula to check row 2 instead of one, as per your screenshot.
    M = Sheet1.Cells(2, Columns.count).End(xlToLeft).Column
    N = Sheet1.Cells(Rows.count, "A").End(xlUp).Row
    j = 3
'Replaced the x loop with a j loop that increments by 2.  
    For j = 3 To N Step 2
'Had the i loop start from 3 instead of 1
        For i = 3 To M
            If Cells(j, "B").Value > 0 Then
'Divided the "B" value by the number of columns M, which is what it sounds like you were going for in your description.
                Cells(j, i).Value = Cells(j, "B").Value / (M - 2)
            End If
        Next i
    Next j
End Sub

Obviously the code is working on the assumption that the Columns and Rows variables are returning expected values.

Upvotes: 1

Related Questions