joeserrone

asked on

# Macro to re-write values in a column

I have a spreadsheet that i will download from a system, the spreadsheet sometimes might have 1 record, other times could be an unknown amount of records. I need to be able to find the range of my data and for each line update the column "K" with a formula similar to:

= I4/($I$1*8)

I am not too sure how to accomplish this in a macro. Attached is a sample of my spreadsheet, the macro I am trying to build would see that there are 6 records that need to be updated, and go in column K and place the formula keeping in mind that I4 is only valid for line 4, for line 5 instead I need it to look in Line 5

Example---Copy.xlsx

= I4/($I$1*8)

I am not too sure how to accomplish this in a macro. Attached is a sample of my spreadsheet, the macro I am trying to build would see that there are 6 records that need to be updated, and go in column K and place the formula keeping in mind that I4 is only valid for line 4, for line 5 instead I need it to look in Line 5

Example---Copy.xlsx

ASKER

Thank you gowflow that is exactly what I was looking for, the only thing is it possible to have that formula in the formula of each cell in column K. Someone other than me might modify the different values and I want to formula to automatically calculate the new values in Column K

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you so Much!

ASKER

If I wanted to Reference a different worksheet, so lets say that the data in cell I1 is in Sheet 2 how would that impact the code below...

Sub BuildColK()

Dim WS As Worksheet

Dim MaxRow As Long, I As Long

Set WS = ActiveSheet

MaxRow = WS.UsedRange.Rows.Count

For I = 4 To MaxRow

If WS.Cells(I, 1) <> "" Then

WS.Range("K" & I).Formula = "=I" & I & "/($I$1* 8)"

End If

Next I

End Sub

Sub BuildColK()

Dim WS As Worksheet

Dim MaxRow As Long, I As Long

Set WS = ActiveSheet

MaxRow = WS.UsedRange.Rows.Count

For I = 4 To MaxRow

If WS.Cells(I, 1) <> "" Then

WS.Range("K" & I).Formula = "=I" & I & "/($I$1* 8)"

End If

Next I

End Sub

heheeh !!!1 It would still work !!!! try it and let me know.

All you would need to do is activate the sheet of your choice and then from within that sheet launch the macro and it will update the active sheet you are in no matter what the name is. For sure, it will always update Col K with the formula impacting Col I as you requested.

gowflow

All you would need to do is activate the sheet of your choice and then from within that sheet launch the macro and it will update the active sheet you are in no matter what the name is. For sure, it will always update Col K with the formula impacting Col I as you requested.

gowflow

Did it work for you ? any comment ?

gowflow

gowflow

I have a spreadsheet that i will download from a system ....

I need to be able to find the range of my data and for each line update the column "K" with a formula similar to: = I4/($I$1*8)

and you need all htis in a macro.

So I guess you have a fix workbook to which you import or attach these new sheets and want to apply this routine in a macro to build the column K based on the formula I4/($I$1*8)

If this is the case then I would suggest that in your main workbook you create a module and in that module you insert the following Sub

Open in new window

and run the Macro each and every time you import a new sheet to this workbook. I have attached your original workbook and inserted this sub just run it and delete previously col K to see the results.

gowflow

Example---Copy.xlsm