Excel Macro Help

[littlejilly]

I have a few questions.

I have 7 products (j = 0 to 6) and I have already declared the ProductPrice for each product.

I also have declared RelativeES(j,0) (an integer)

I need to write a code for TotalCost (j) but only taking in those products where RelativeES(j,0) = 0 or 1.

I then need to caclulate the PriceRatio for each product (j) where RelativeES(j,0) = 0 or 1 where PriceRatio(j) = ProductPrice(j)/TotalCost(all j where RelativeES(j,0) = 0,1))

Thanks

 

[Worf]

Hi

See if the example below is what you want...

Sub Jilly()
Dim RelativeES%(0 To 6, 0 To 6), TotalCost!(0 To 6), _
PriceRatio!(0 To 6), ProductPrice!(0 To 6), j%

' some code here
For j = 0 To 6
    Select Case RelativeES(j, 0)
        Case 0, 1
            TotalCost(j) = Rnd
            PriceRatio(j) = ProductPrice(j) / TotalCost(j)
        Case Else
            ' other code here
    End Select
Next
' more code here
End Sub

 

[littlejilly]

Thanks for your reply.

Also, Is there a way to attach a cell or something?

I have a few combo boxes that show/hide based on the selection of an option button but everytime i open my document the boxes shift

 

[Worf]

Let me see if I understood your question:

- You want the ComboBoxes to appear always at the same position.
- They are not inside a UserForm, rather floating on the sheet.
- Are they Forms or ActiveX ComboBoxes ?

 

[littlejilly]

Yes, that is correct. They should always remain at the same position unless they are to hide based upon selection of an option button (activX)

the comboboxes are also ActivX controls.

 

[Worf]

See if the following technique is useful. You must adapt it to your own code.

Sub Positions()         ' shows information on controls

    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        MsgBox "Name :  " & sh.Name & vbNewLine & "Left :  " & _
        sh.Left & vbNewLine & "Top :  " & sh.Top, vbInformation
    Next
        
End Sub

Sub FixPosition()       ' places control at desired location
    
    With ActiveSheet.Shapes("ComboBox1")
        .Left = 500
        .Top = 80
    End With

End Sub