Help VBA for EXCEL

2»

Comments

  • 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:

    [code]
    Dim LastRowColA As Integer
    LastRowColA = Range("A3001").End(xlUp).Row
    [/code]

    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:

    [code]
    For i = 1 To NumberOfValues
    GetRow = Round(Rnd() * iRow, 0)
    [/code]

    To:

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

    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:

    [code]
    Range("H4").Formula = GrNo
    Range("H5").Formula = 0.123 * H3
    Range("H6").Formula = GrNo - H5
    [/code]

    Into this (flat values):

    [code]
    Range("H4").Value = GrNo
    Range("H5").Value = 0.123 * Range("H3").Value
    Range("H6").Value = GrNo - Range("H5").Value
    [/code]

    Or this (formulae in the worksheet):

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

    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!
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories