Divide Rest of the Columns using Column A values

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

Hi

try

Code:

---

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

---

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

I have edited the above code.

or

Code:

---

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

---

Hi Admin,

Still i am getting same error

Quote:

---

Originally Posted by Prabhu

Hi Admin,

Still i am getting same error

---

request your help to resolve the error.

Did you overlook #5 ?

Quote:

---

Originally Posted by snb

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.