Divide Rest of the Columns using Column A values

[Prabhu]

Hi All,

I have a complex query.

In the excel sheet column A values to be divided with rest of the columns.

Example Cell A1 value to be used to dived rest of the visible rows like B1, C1 D1,etc

Columns and row are not fixed and it is dynamic.

for the ready reference i have attached sample workbook which has sheet1 which has input value and sheet 2 which has result.

Request to help if this can be done VBA code.

Regards,

Prabhu

[Admin]

Hi

try

Option Explicit

Sub kTest()
    
    Dim r   As Long, c  As Long
    Dim ColA, d, Dest As Range
    
    With Worksheets("Sheet1")
        r = .Range("a" & .Rows.Count).End(xlUp).Row
        c = .Cells(1, .Columns.Count).End(xlToLeft).Column
        ColA = .Range("a2:a" & r).Value2
        d = .Range("b2", .Cells(r, c)).Value2
        Set Dest = .Range("b2")
    End With
    
    If IsArray(ColA) Then
        If IsArray(d) Then
            For r = 1 To UBound(ColA, 1)
                For c = 1 To UBound(d, 2)
                    If ColA(r, 1) = 0 Then
                        d(r, c) = "=iferror(" & d(r, c) & "/" & ColA(r, 1) & ",0)"
                    Else
                        d(r, c) = "=" & d(r, c) & "/" & ColA(r, 1)
                    End If
                Next
            Next
            Dest.Resize(UBound(d, 1), UBound(d, 2)).Value2 = d
        End If
    End If
    
End Sub

[Prabhu]

Thanks!

I could not run the macro,below code shows as run time error '1004' Application-defined or object -defined error.


Dest.Resize(UBound(d, 1), UBound(d, 2)).Value2 = d

[Admin]

I have edited the above code.

[snb]

or

Sub M_snb()
   Sheet2.Cells(20, 1).Resize(9, 4) = [index(if(column(A2:D2)=1,sheet1!A2:A10,round(sheet1!A2:D10/sheet1!A2:A10,0)),)]
End Sub

[Prabhu]

Hi Admin,

Still i am getting same error

[Prabhu]

Hi Admin,

Still i am getting same error

request your help to resolve the error.

[snb]

Did you overlook #5 ?

[Prabhu]

Did you overlook #5 ?

Yes,

with the same format of Sheet 1 along with the formulas in cell( instead of value format) like "=149/6" will solve the purpose.

also the row and column are flexible number of row used to change every time.

the solution provide by the admin is working for the 1st line only and then "Dest.Resize(UBound(d, 1), UBound(d, 2)).Value2 = d" code highlighted as error.

if you help to resolve the error also fine for me.

Thanks for the support.