Hello.
This is the code so far:

Global GrNom4 As Single
Sub Init()
GrNom4 = InputBox("nnnnnnn&mmmmmmmm XX:")

Sub GetSWDataCom14()
Dim RowPtr As Long, Chan As Long, F1 As Variant, WData As String, i As Integer
RowPtr = ThisWorkbook.Sheets("Sheet1").Cells(3000, 1).End(xlUp).Row + 1
Chan = DDEInitiate("software", "Com14")
F1 = DDERequest(Chan, "Field(1)")
WData = F1(1)
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""
End If
i = RowPtr Mod 31
If i = 0 Then
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = "....................."
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 2).Value = Time
End If
DDETerminate Chan
End Sub

What the code does: i start the macro, insert one value say 10 and the macro introduces it in column A cel by cell unless the received value is below 50% or above 150% of 10. The values are received from balances.

What i want:
1.If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then - this is ok but i needed it to skip a value...say 50. So everything below 50% or above 150% from the original value should not be entered in a cell, except for the "50" value.
2. A column can have a maximum of 3000 values (it can also have 200).... i want to select 30 random values and calculate their average, and then calculate a formula. Something like AAA * 0.603 - this average = XX.

This is not my code. I am not this good.
Any help would be greatly appreciated.
Thank you!

1.If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then - this is ok but i needed it to skip a value...say 50. So everything below 50% or above 150% from the original value should not be entered in a cell, except for the "50" value.

That's easy enough, just add another if statement, so change this:

If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""
End If

To this:

Const ExceptionValue as String = "50"
If WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4 Then
    If WData <> ExceptionValue Then 
        ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""
    End If
End If

Or this:

If (WData < 0.5 * GrNom4 or WData > 1.5 * GrNom4) And WData <> 50 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""

Note, that last one is all on one line, you do not need an end if block if you keep the statement all on one line.


2. A column can have a maximum of 3000 values (it can also have 200).... i want to select 30 random values and calculate their average, and then calculate a formula. Something like AAA * 0.603 - this average = XX.

I would build a function for this, something like below:

Public Function GetAverages(ByVal WhichSheet As Worksheet, ByVal WhichCol As Integer) As Double()
Const NumberOfValues As Integer = 30
Const HasHeaderRow As Boolean = True 'If the first row has text in it, set to true
Dim iRow As Integer, GetRow As Integer
Dim AvNum As Double, LowestNum As Double, HighestNum As Double, iNum As Double
iRow = 0
Do
    iRow = iRow + 1
Loop Until WhichSheet.Cells(iRow + 1, WhichCol).Value = ""
If HasHeaderRow = True Then iRow = iRow - 1

AvNum = 0
LowestNum = 99999
HighestNum = 0

For i = 1 To NumberOfValues
    GetRow = Round(Rnd() * iRow, 0)
    If HasHeaderRow = True Then GetRow = GetRow + 1
    iNum = WhichSheet.Cells(GetRow, WhichCol).Value
    If iNum < LowestNum Then LowestNum = iNum
    If iNum > HighestNum Then HighestNum = iNum
    AvNum = AvNum + iNum
Next
AvNum = AvNum / NumberOfValues

Dim TheData() As Double
ReDim TheData(3)
TheData(0) = AvNum 'Returns average
TheData(1) = NumberOfValues 'Returns number of values used
TheData(2) = LowestNum 'Returns lowest number in range
TheData(3) = HighestNum 'Returns highest number in range

GetAverages = TheData
End Function

You can then just call this function to retrieve your values, you can even add more values to the output of the function to make it more useful. You will probably want to input the values into cells, but to display how it works, you could call it with the below code:

Sub DisplayAverages()
Dim TheSheet As Worksheet, sStr As String
Set TheSheet = ActiveWorkbook.Sheets("Sheet1")
Dim avValues() As Double
avValues = GetAverages(TheSheet, 1)
sStr = "An average of " & avValues(0) & " was found by analysing " & avValues(1)
sStr = sStr & " values, ranging from " & avValues(2) & " to " & avValues(3)
MsgBox sStr
End Sub

In this example you will need to have values in column A of Sheet1.

HTH, Dai

------------------------------------------
Do or do not, there is no try. |
------------------------------------------

HELLO & THANK YOU!

The first bit works like a charm. I especially liked the one line code. Very clean ... you sir know your stuff.
The second part i didn't get the chance to test, yet. I will do so ASAP.

And i have another question:

How to make "50" appear on the worksheet in the same number cell on column 6 as the last number from column 5.
Basically whenever "50" appears, it's placed next to the last occupied cell on column 5, but on column 6.
I hope i made myself clear, my English is a bit rusty.

Again thank you very much for the help so far.

Have a pleasant day,
scorpi

Not 100% sure I understand what you need, but my guess is that going down column E you have some blank values and/or zeros, you wish to ignore these and only put a 50 by the last real number when a 50 is found, so if going down column E you had the values:
23, 44, 50, 67, 0, 0, 50, 55, ""(blank), 50, 616

You would want 50s to appear in Column F along side the 44, 67 and 55.

If that is correct then the below code will achieve what you want. If it is not, please reply back with another explanation of what you're looking for.

Sub Write50s()
Dim iRo As Integer, NumRows As Integer, LastOcc As Integer
Dim TheSht As Worksheet
Set TheSht = Sheets("Sheet1")
NumRows = TheSht.Cells(3000, 5).End(xlUp).Row
LastOcc = 1
TheSht.Columns(6).ClearContents 'Clears column F before starting
For iRo = 1 To NumRows
    'If Column E has a 50, last occupied cell gets a 50 in Column F
    If TheSht.Cells(iRo, 5).Value = 50 Then TheSht.Cells(LastOcc, 6).Value = 50
    
    'If Column E isn't blank and isn't a zero, becomes last occupied cell
    If TheSht.Cells(iRo, 5).Value <> "" And TheSht.Cells(iRo, 5).Value <> 0 Then LastOcc = iRo
Next
End Sub

HTH, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

I will try to explain better.
As you saw in the code, it eliminates anything above 150% or below 50% (because they are considered aberrations).
All values are with 2 decimals like "31.26". When i launch the macro it asks for the number: let's say 30. The majority of values will be between 29-31 but there is a list of possible values (say from 29.26 - 30.98). Anything below or above is wrong.
This macro is for a balance and a lot of types of products. The weighing process is automatic but supervised by a human operator (in order to decide if the weight is correct).
Due to the fact that we have some 200 products to weigh there is no point in making a code for every one (the products may even change from one day to another). So i want to allow the human operator to decide which value is ok.
And that excepted value will be some type of 50 grams weight (or another value that does not interfere with the product weight).
So if the maximum value is 30.98 and the current product weighs 31.01 than the human operator weighs that excepted value next ; which appears beside the 31.01 value. Same cell number but next column. So now everybody knows that 31.01 is wrong (he/she does not need to know the exact interval only what 50 means). Of course that "50" value (in excel) can be replaced with a text that says "WRONG VALUE" or something.

Am i making any sense?

Thank you very much for trying to help me!

Have a good day!
scorpi

This post has been deleted.

Sorry I haven't replied, do not tend to check over the weekend.

OK, I think I now know what you mean, I was a bit thrown because I thought you were only looking to do this to randomly selected numbers, but if you are looking to do it to the whole data, I'm thinking you would need to use something like the following:

Global GrNom4 As Single

Sub Write50s()
Dim iRo As Integer, NumRows As Integer, MaxValue As Double, MinValue As Double, OrigValue As Double
Dim Msg50 As String
Dim TheSht As Worksheet
Set TheSht = Sheets("Sheet1")
NumRows = TheSht.Cells(3000, 5).End(xlUp).Row
Msg50 = "Err 50: Invalid Value"
GrNom4 = InputBox("nnnnnnn&mmmmmmmm XX:")
MaxValue = 1.5 * GrNom4
MinValue = 0.5 * GrNom4

TheSht.Columns(6).ClearContents 'Clears column F before starting
For iRo = 1 To NumRows
    'If Column E has a value greater than the maximum allowed...
    If TheSht.Cells(iRo, 5).Value > MaxValue Then TheSht.Cells(iRo, 6).Value = Msg50 & " (too high)"
    
    'If Column E has a value smaller than the minimum allowed...
    If TheSht.Cells(iRo, 5).Value < MinValue Then TheSht.Cells(iRo, 6).Value = Msg50 & " (too low)"
    
Next
End Sub

This is based on columns E and F again, if you wish to put the "50" code in column A, change the 6s to 1s. Hope this really does do what you need, if not, just ask again, maybe cite some more examples, We WILL get there! Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

So this is the macro so far:

Global GrNom4 As Single
Sub Init()
GrNom4 = InputBox("Weight T4:")
On Error Resume Next
Application.Wait Now + TimeValue("00:00:05")
Shell ("C:\Program Files\software\software.exe C:\Program Files\software\PortBoxCom14.xyz")
End Sub

Sub GetSWDataCom14()
Dim RowPtr As Long, Chan As Long, F1 As Variant, WData As String, i As Integer
RowPtr = ThisWorkbook.Sheets("Sheet1").Cells(3000, 1).End(xlUp).Row + 1
Chan = DDEInitiate("WinWedge", "Com14")
F1 = DDERequest(Chan, "Field(1)")
WData = F1(1)
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If (WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4) And WData <> 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""
i = RowPtr Mod 31
If i = 0 Then
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = "=AVERAGE(R[-29]C:R[-1]C)"
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 2).Value = Time
End If
DDETerminate Chan
End Sub

I don't need another input box on column 2. It should be linked to this macro and the values it inputs on column 1.
I'll try to build an example. Let's say the "good" values are from 30.20 up to 30.25.
So in the end it looks like this:
Column A Column B
30.21
30.25
30.22
30.25
30.22
30.21
30.27 23
30.22
30.28 23
30.21
30.22
and so on

But only the human operator decides 30.27 & 30.28 are wrong. He/she sees the values and the next thing he/she weighs is the excepted value (in this case 23). Once 23 has been weighed the macro "knows" to put this value besides the last input value. So the 23 signals bad value. Next value that is not 23 goes in the cell below and so on.
This "placing 23 in the adjacent cell" bit needs, if possible, to be inside the already made macro. Not a new macro.
This bit helps me keep track of how many products not ok. Quality assurance stuff.
PS: i know that on column 2 a time stamp appears... but it shouldn't be a problem because it's linked to that formula on column 1.

Thank you again for taking the time to help me!

OK I think I'm getting a clearer picture now, so the GetSWDataCom14 sub gets called each time something is weighed correct? And WData is the actual weight being pulled from the Com14 program.

So if the weight is below 75% of the initial inputbox value (i.e. GrNom4) or above 175% (or 50 and 150, whatever) then you delete the value completely as if it had never been weighed?

Then if they weigh something that's within these bounds (75% - 175%), but looks too high for the operator, e.g. 30.27, the operator will then weigh a 23 gram weight on the scale as a way of informing the computer that the last value was too high or low, correct?

If yes to all 3 above, then all you need to do is change the following code:

ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If (WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4) And WData <> 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""

To this:

ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Formula = WData
If WData = 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr -1, 2).Value = 23
If WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4 Or WData = 23 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 1).Value = ""

My only worry is that you are overwriting the weight with the average formula every 31 rows, are you sure you want to overwrite it, not put it somewhere else?

Just thinking if you get say a 30.28, on the 31st row, or 62nd etc, then you won't even know about it as it'll just be overwritten with a formula for the averages. You could simply put it on the next line instead so nothing is overwritten?

Also, I'd recommend working out the average in the code itself, then insert the value into the cell rather than a formula, helps speed up Excel if you use less formulae, may not be noticeable on a small workbook, but as it grows you may find it slow down a little.

Anyway I hope I have given what you need, if not, please do ask, that's what I'm here for, to answer the sort of questions I was asking everyone else 5+ years ago.

Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

And it's exactly what i was looking for....thank you!
The weird thing is that some values seem to "escape" the cleansing (that If WData < 0.75 * GrNom4 Or WData > 1.75 * GrNom4). And it did not happen before. And some values, although collected, do not appear in the cells. Since the macro runs for some 10 balances simultaneously i'm thinking it's too much for the PC or Excel to handle. Any clues?
I am now trying the bit with the 30 random values... it's going to take some time to test it...i will try not to bother you unless really necessary.

Take care!
scorpi

How do you mean simultaneously? Separate scales are all feeding into the same workbook at the same time are they?

As you are finding some aberrations (outside the 50-150%) are slipping through and some normal values are being deleted, it would seem likely that this simultaneous running is causing it to delete at the wrong time(or place). What way are you getting these 10 simultaneous balances? (Does the macro pull through 10 values each time, or are you calling the macro 10 times?) Regards, D


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

Yes, separate scales are all feeding their data into the same workbook at the same time.

It's something like: 'Global GrNom1 As Single, GrNom2 As Single, GrNom3 As Single, GrNom4 As Single, etc' and their macro:
Sub GetSWDataCom11()
........................
........................
End Sub

Sub GetSWDataCom12()
........................
........................
End Sub
etc
I have to input the weight for each of the scales manually when i start the macro. Then the macro collects all the input values until it reaches cell 3000 on each column, and then it stops inserting values into the cells but still receives new values. They're just not being written into cells any more.
Are there to many things to do for the macro?

This post has been deleted.

Not necessarily too much to do, but as you are finding the last row each time to populate it, any overlap could cause values to be overwritten incorrectly.

I would create a separate worksheet for each scale, so that GetSWDataCom11 points to sheet "Scale1" etc. You can always compile all values into one sheet if necessary.

Try this, if it does not work, we at least limit the possible causes. Did you change the code so that the average goes on to the next row or perhaps a different column? Once you have tried this, if it fails include your current code in your next post. Regards, D


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

I changed the PC and the network equipment. Seems like most problems are fixed now. The code did not change.
One problem is that sometimes the excepted weight (which is 75g now) tends to be misread: 76 or 74. So i'm working on changing the exception from one value to an interval: can this be done like in excel like "74:76"?.
I don't know why but i cannot find any info on this exception value business. Seems like nobody needed such info.
I also am trying to test the "30 numbers average" macro. I will keep you posted.
And i have been working on creating charts with the obtained values.
This is the code for the charts (done with recording and some F1 help):

' CHART Macro
'

'
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Left = 20
.Width = 4000
.Top = 20
.Height = 320
End With
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns("A:A")
Sheets("Sheet3").Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Left = 50
.Width = 3000
.Top = 50
.Height = 300
End With
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Columns("C:C")
Sheets.Add After:=Sheets(Sheets.Count)
...............................................etc

Works well. But i was wondering: every chart has a legend.. series1 for the first and so on... the first cell on column A,C,E,G,I,K,M, etc contains the product and the name of the operator. Can i insert that into the legend automatically?
Take care!
scorpi

Hi,

1."One problem is that sometimes the excepted weight (which is 75g now) tends to be misread: 76 or 74. So i'm working on changing the exception from one value to an interval: can this be done like in excel like "74:76"?. " - solved.

2."But i was wondering: every chart has a legend.. series1 for the first and so on... the first cell on column A,C,E,G,I,K,M, etc contains the product and the name of the operator. Can i insert that into the legend automatically?" - solved.

I only have 1 more problem: the code for those 30 random values.
"A column can have a maximum of 3000 values (it can also have 200).... i want to select 30 random values from that column and calculate their average (say BBB), and then calculate a formula. Something like AAA * 0.603 - BBB = CCC." - this is what the code is supposed to do.
The code itself is written a few posts up. It does not seem to work and i do not know why. Any help would be appreciated.
Thank you for the help so far.
Take care!
scorpi

OK, I was just about to answer your questions, had some good answers ready too, shame. Well the 30 random values thing was given a while ago, you have changed many things in your workbook since, so perhaps is no longer fit for purpose, may need a little editing, should work though.

What code are you using to call the function? Post it please. Also, try to figure out where it falls over, to do this put in: Debug.Print "Step 1", and step 2 etc. about every three lines (not inside loops) and when you next check the VBE, you will be able to see at which step it stopped.

Without some code to look at I can't tell you why it doesn't work, as it works fine for me. Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

1.The code for the scales:
"Sub GetSWDataCom11()
Dim RowPtr As Long, Chan As Long, F1 As Variant, WData As String, i As Integer
RowPtr = ThisWorkbook.Sheets("Sheet1").Cells(3000, 13).End(xlUp).Row + 1
Chan = DDEInitiate("software", "Com11")
F1 = DDERequest(Chan, "Field(1)")
WData = F1(1)
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 13).Formula = WData
If WData = 75 Or WData = 76 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr - 1, 14).Value = "BAD"
If WData < 0.25 * GrNom1 Or WData > 1.25 * GrNom1 Or WData = 75 Or WData = 76 Then ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 13).Value = ""
i = RowPtr Mod 31
If i = 0 Then
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 13).Formula = "=AVERAGE(R[-29]C:R[-1]C)"
ThisWorkbook.Sheets("Sheet1").Cells(RowPtr, 14).Value = Time
End If
DDETerminate Chan
End Sub"

2.The code for the charts (different macro):
"Sub Grafic()
'
' Grafic Macro
'

'
Sheets("Sheet2").Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart.Parent
.Left = 20
.Width = 4000
.Top = 20
.Height = 320
End With
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A2:A3000")
End Sub"

Q: say i have less than 3000 values. Let's say i have 252 values on column A. How can i make the macro automatically detect how many values i have and only select that range (instead of Range("A2:A3000"))? Something to do with row pointer?

3. the code for those 30 random numbers:
"Public Function GetAverages(ByVal WhichSheet As Worksheet, ByVal WhichCol As Integer) As Double()
Const NumberOfValues As Integer = 30
Const HasHeaderRow As Boolean = True 'If the first row has text in it, set to true
Dim iRow As Integer, GetRow As Integer
Dim AvNum As Double, LowestNum As Double, HighestNum As Double, iNum As Double
iRow = 0
Do
iRow = iRow + 1
Loop Until WhichSheet.Cells(iRow + 1, WhichCol).Value = ""
If HasHeaderRow = True Then iRow = iRow - 1

AvNum = 0
LowestNum = 99999
HighestNum = 0

For i = 1 To NumberOfValues
GetRow = Round(Rnd() * iRow, 0)
If HasHeaderRow = True Then GetRow = GetRow + 1
iNum = WhichSheet.Cells(GetRow, WhichCol).Value
If iNum < LowestNum Then LowestNum = iNum
If iNum > HighestNum Then HighestNum = iNum
AvNum = AvNum + iNum
Next
AvNum = AvNum / NumberOfValues

Dim TheData() As Double
ReDim TheData(3)
TheData(0) = AvNum 'Returns average
TheData(1) = NumberOfValues 'Returns number of values used
TheData(2) = LowestNum 'Returns lowest number in range
TheData(3) = HighestNum 'Returns highest number in range

GetAverages = TheData
End Function"

So i do what the function asks of me (sheet + column) and then in the selected cell "#VALUE!". I think it is because every 31 values the average is being calculated. So if possible i would like to know how to exclude those values.. and if it's not to much trouble: how do i turn this function into a macro?
Thank you for your continuous help with this.

Take care!
scorpi

This post has been deleted.

To answer your first question, assuming you have no gaps in column A, you can find out how many rows there are using the following code:

Dim LastRowColA As Integer
LastRowColA = Range("A3001").End(xlUp).Row

With the function, there's a little misunderstanding here. That function is not a UDF (User Defined Function) that can be called from a cell. This is a normal function that has to be called from another sub or function. If you look at one of the earliest posts, you will see a sub called DisplayAverages, just below the code for the function, this is an example of how you can call the function to return the results.
The function returns an array, containing the different results. Note that the worksheet parameter you pass in has to be an actual Worksheet object, not just the name of the sheet, which is one reason why it will not work when called from a cell.
As for the averages you place in column A, I still would recommend putting them somewhere else, to save confusion and improve data quality, however if you do need to leave them there, you can just edit the function to ignore any of those rows. You could do this by changing the following lines:

For i = 1 To NumberOfValues
    GetRow = Round(Rnd() * iRow, 0)

To:

For i = 1 To NumberOfValues
TryAgain:
    GetRow = Round(Rnd() * iRow, 0)
    If GetRow Mod 31 = 0 Then GoTo TryAgain

HTH, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

"To answer your first question, assuming you have no gaps in column A, you can find out how many rows there are using the following code:
Dim LastRowColA As Integer
LastRowColA = Range("A3001").End(xlUp).Row" - thank you, but there are gaps and many different values in each row. I will fix it somehow.

Far more important (and rather stupid) is this:
"
Public Function GetAverages(ByVal WhichSheet As Worksheet, ByVal WhichCol As Integer) As Double()
Const NumberOfValues As Integer = 30
Const HasHeaderRow As Boolean = True
Dim iRow As Integer, GetRow As Integer
Dim AvNum As Double, LowestNum As Double, HighestNum As Double, iNum As Double
iRow = 0
Do
iRow = iRow + 1
Loop Until WhichSheet.Cells(iRow + 1, WhichCol).Value = ""
If HasHeaderRow = True Then iRow = iRow - 1

AvNum = 0
LowestNum = 99999
HighestNum = 0

For i = 1 To NumberOfValues
TryAgain:
GetRow = Round(Rnd() * iRow, 0)
If GetRow Mod 31 = 0 Then GoTo TryAgain
If HasHeaderRow = True Then GetRow = GetRow + 1
iNum = WhichSheet.Cells(GetRow, WhichCol).Value
If iNum < LowestNum Then LowestNum = iNum
If iNum > HighestNum Then HighestNum = iNum
AvNum = AvNum + iNum
Next
AvNum = AvNum / NumberOfValues

Dim TheData() As Double
ReDim TheData(3)
TheData(0) = AvNum
TheData(1) = NumberOfValues
TheData(2) = LowestNum
TheData(3) = HighestNum

GetAverages = TheData
End Function
Sub DisplayAverages()
Dim TheSheet As Worksheet, GrNo As Single
Set TheSheet = ActiveWorkbook.Sheets("Sheet1")
Dim avValues() As Double
avValues = GetAverages(TheSheet, 7)
Range("H2").Formula = avValues
Range("H3").Formula = Application.StDev(Range("G2:G3000"))
GrNo = InputBox("Total weight")
Range("H4").Formula = GrNo
Range("H5").Formula = 0.123 * H3
Range("H6").Formula = GrNo - H5
End Sub
"

I managed to make it work. Except for the last 2 lines. I wrote the formula like this to break down each step and see what went wrong. I believe i should add a delay between "Range("H4").Formula = GrNo" and the next 2 lines so the macro can write the necessary values into the cells. Is there an easier way?
Thank you very much for all your help. I hope this is the last time i have to bother you.

PS: i can't move the average values. Don't ask why. I did and was told: "Cause you just can't".

Hi scorpion,

Well that code still gives you the last occurrence of any value in that column, which is what you'll need if you're trying to select all the values you have, it just means you'll have blank cells in between, so just ensure your code ignores them (If Cells(i,1).Value = "" Then), if that's what you need to do, just adapt to your code where necessary.

As to the last line, the problem here is you're referring to constants rather than writing a formula, there is no H3 constant, if you want to refer to the range, you must use the range or cells function, if you want to input a formula then put it in quotations. Also, you don't need to use the .formula if you're just inserting or extracting flat values. So, you can change this:

Range("H4").Formula = GrNo
Range("H5").Formula = 0.123 * H3
Range("H6").Formula = GrNo - H5

Into this (flat values):

Range("H4").Value = GrNo
Range("H5").Value = 0.123 * Range("H3").Value
Range("H6").Value = GrNo - Range("H5").Value

Or this (formulae in the worksheet):

Range("H4").Value = GrNo
Range("H5").Formula = "=0.123 * H3"
Range("H6").Formula = "GrNo - H5"

Hope this sets you up to finish it off now, don't be afraid to ask if you are stuck though. Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------

Hi,

Thank you very much for the help. I couldn't have done it without you!
Everything works well so far. It took a little longer to set it up but it is ok now.

Best regards,
scorpi

PS: Happy Easter!