UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Processing Spreadsheet Data, Office 2013    
 
   
davideyles
post May 2 2019, 03:12 AM
Post#1



Posts: 211
Joined: 13-April 03
From: Cornwall, UK


Hi All
I am starting to create a spreadsheet to process data exported from our sheep management system.

What I would like to achieve is to be able to forecast what the weight of the animal will be in "x" days from the current weight. The imported data consists of a row/animal with the important data being the most recent weight and the daily weight gain between the previous weighing and current weighing.

I have copied and pasted together bits of code I have Googled and I can roughly achieve the result I want, but the biggest problem is the fact that the data table will have varying numbers of rows depending on the number of animals in the group I need to account for this, and this is where I am getting stuck.

I have put comments in the code to explain what I am doing and if anyone would like to suggest a better way of doing this I would be most grateful.

Cheers

David
Attached File(s)
Attached File  dlwg.zip ( 18.14K )Number of downloads: 2
 
Go to the top of the page
 
PaulBrand
post May 2 2019, 04:12 AM
Post#2



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


I've added to your file, this is a good start...

You'll see my comments

It's best to sort at the end
This post has been edited by PaulBrand: May 2 2019, 04:17 AM
Attached File(s)
Attached File  dlwg.zip ( 18.31K )Number of downloads: 4
 

--------------------
Paul
Go to the top of the page
 
davideyles
post May 2 2019, 07:06 AM
Post#3



Posts: 211
Joined: 13-April 03
From: Cornwall, UK


Hi Paul
Thanks for that works a treat.

I agree sorting at the end, and actually I have changed it to sort on the new calculated value as this is more representative of what I was looking for. I have created a nominal sort range to row 200 which should cover all eventualities, but I was wondering if there is a way of naming a range to sort to when you don't know how many rows you are going to have in the block of data? This would be a tidier way to do the sort as if the number of rows exceeded 200 then some rows from 201 onwards wouldn't be included in the sort but if the range was named and this used to specify the sort range that would solve it.


Once I have had a bit more of a fiddle with formatting etc, I was thinking how to use the spreadsheet/macro the best way. What are your thoughts on getting the new data into the spreadsheet for the macro to be run on? Is the best way to manually copy and paste the data, or if I create the data download spreadsheet from the sheep management system and have a consistent filename and location would there be a tidy way for the macro to import the data from the downloaded spreadsheet and then manipulate the data in the new one?


I've uploaded my new version of the sort


Cheers


David
Attached File(s)
Attached File  dlwg_new_sort.zip ( 20.82K )Number of downloads: 1
 
Go to the top of the page
 
PaulBrand
post May 2 2019, 07:21 AM
Post#4



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


You can apply a name like below:

CODE
ActiveWorkbook.Worksheets("Sheet1").UsedRange.Name = "Test"
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Range("Test").Sort.SortFields.Add2 Key:=Range("l2:l200") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal


I can see you're using the macro recorder, which is fine. There are more efficient ways to do this though.

When I get more time, I'll show you some tips...
This post has been edited by PaulBrand: May 2 2019, 07:25 AM

--------------------
Paul
Go to the top of the page
 
PaulBrand
post May 2 2019, 07:51 AM
Post#5



Posts: 1,704
Joined: 4-September 02
From: Oxford UK


This code is getting smarter by using variables assigned to the workbook and sheet,,,

CODE
    Dim LastRow As Integer
    Dim ws As Worksheet
    Dim wb As Workbook
    Set wb = ThisWorkbook ' better than ActiveWorkbook, especially if your macro grows and another workbook BECOMES active
    Set ws = wb.Sheets("Sheet1")
    
    
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row 'Get the last row number from column A
    Range("L1").Select
    ActiveCell.FormulaR1C1 = InputBox("What time period?") 'Ask for input
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=(R1C12*RC[-1])+RC[-2]"
    Selection.AutoFill Destination:=Range("L2:L" & LastRow), Type:=xlFillDefault
    'Range("L2:L69").Select

'Using variables
With ws
    ws.Activate
    .UsedRange.Name = "Test"
    .Sort.SortFields.Clear
        With ws.Sort
            .SortFields.Add Key:=Range("L2"), Order:=xlAscending '
            .SetRange Range("Test")
            .Header = xlNo
            .Apply
        End With
End With

--------------------
Paul
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 09:03 PM