Link to home
Start Free TrialLog in
Avatar of joeserrone
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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

I am reading your post again:

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

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) = WS.Range("I" & I) / (WS.Range("I1") * 8)
    End If
Next I
End 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
Avatar of joeserrone
joeserrone

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
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so Much!
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
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
Did it work for you ? any comment ?
gowflow