Excel Macro Help

littlejilly

Board Regular
Joined
Sep 8, 2011
Messages
166
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
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
Hi

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

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

Board Regular
Joined
Sep 8, 2011
Messages
166
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
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

Board Regular
Joined
Sep 8, 2011
Messages
166
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,096
See if the following technique is useful. You must adapt it to your own code.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top