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(sheet 1!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.