Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with calculation
Hi. I need help with a calculation in the macro below.
I have an array of numbers in cells c26:f29. I need to compare each value with the product of numbers contained in column "A" and row "30". For instance, C26 would be compared to A26 * F30. C27 would be compared to A27 * F30. B26 would be compared to A26 * F31 . and so on. The calculation I need help with is in the variable "myKeyValue" below. Can anyone help? Thanks, Mike. -------------------------- Sub CreateOval2() Dim myRange As Range Dim myCell As Range Dim myKeyValue As Variant Dim myOval As Shape Set myRange = Sheet1.Range("c26:f29") For Each myCell In myRange.Cells With myCell myKeyValue = ??? HELP ??? If .Value < myKeyValue Then Set myOval = .Parent.Shapes.AddShape (Type:=msoShapeOval, _ Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height) myOval.Fill.Visible = msoFalse myOval.Line.Weight = 1.5 myOval.Line.ForeColor.SchemeColor = 17 End If End With Next myCell End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with calculation
Do you have a typo in your description? The pattern doesn't make sense. B26
is not even in your original range. -- Vasant "Mike" wrote in message ... Hi. I need help with a calculation in the macro below. I have an array of numbers in cells c26:f29. I need to compare each value with the product of numbers contained in column "A" and row "30". For instance, C26 would be compared to A26 * F30. C27 would be compared to A27 * F30. B26 would be compared to A26 * F31 . and so on. The calculation I need help with is in the variable "myKeyValue" below. Can anyone help? Thanks, Mike. -------------------------- Sub CreateOval2() Dim myRange As Range Dim myCell As Range Dim myKeyValue As Variant Dim myOval As Shape Set myRange = Sheet1.Range("c26:f29") For Each myCell In myRange.Cells With myCell myKeyValue = ??? HELP ??? If .Value < myKeyValue Then Set myOval = .Parent.Shapes.AddShape (Type:=msoShapeOval, _ Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height) myOval.Fill.Visible = msoFalse myOval.Line.Weight = 1.5 myOval.Line.ForeColor.SchemeColor = 17 End If End With Next myCell End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with calculation
Yes, I did have a typo in the description ... actually,
D26 would be compared to would be compared to A26 * F31 ... Sorry about the typo. Would you know how to write the formula? Thanks again, Mike. -----Original Message----- Do you have a typo in your description? The pattern doesn't make sense. B26 is not even in your original range. -- Vasant "Mike" wrote in message ... Hi. I need help with a calculation in the macro below. I have an array of numbers in cells c26:f29. I need to compare each value with the product of numbers contained in column "A" and row "30". For instance, C26 would be compared to A26 * F30. C27 would be compared to A27 * F30. B26 would be compared to A26 * F31 . and so on. The calculation I need help with is in the variable "myKeyValue" below. Can anyone help? Thanks, Mike. -------------------------- Sub CreateOval2() Dim myRange As Range Dim myCell As Range Dim myKeyValue As Variant Dim myOval As Shape Set myRange = Sheet1.Range("c26:f29") For Each myCell In myRange.Cells With myCell myKeyValue = ??? HELP ??? If .Value < myKeyValue Then Set myOval = .Parent.Shapes.AddShape (Type:=msoShapeOval, _ Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height) myOval.Fill.Visible = msoFalse myOval.Line.Weight = 1.5 myOval.Line.ForeColor.SchemeColor = 17 End If End With Next myCell End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with calculation
Try:
myKeyValue = Range("A" & myCell.Row) * Range("F" & (27 + myCell.Column) Of course, subsequent insertions of rows/columns may break this. -- Vasant "Mike." wrote in message ... Yes, I did have a typo in the description ... actually, D26 would be compared to would be compared to A26 * F31 ... Sorry about the typo. Would you know how to write the formula? Thanks again, Mike. -----Original Message----- Do you have a typo in your description? The pattern doesn't make sense. B26 is not even in your original range. -- Vasant "Mike" wrote in message ... Hi. I need help with a calculation in the macro below. I have an array of numbers in cells c26:f29. I need to compare each value with the product of numbers contained in column "A" and row "30". For instance, C26 would be compared to A26 * F30. C27 would be compared to A27 * F30. B26 would be compared to A26 * F31 . and so on. The calculation I need help with is in the variable "myKeyValue" below. Can anyone help? Thanks, Mike. -------------------------- Sub CreateOval2() Dim myRange As Range Dim myCell As Range Dim myKeyValue As Variant Dim myOval As Shape Set myRange = Sheet1.Range("c26:f29") For Each myCell In myRange.Cells With myCell myKeyValue = ??? HELP ??? If .Value < myKeyValue Then Set myOval = .Parent.Shapes.AddShape (Type:=msoShapeOval, _ Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height) myOval.Fill.Visible = msoFalse myOval.Line.Weight = 1.5 myOval.Line.ForeColor.SchemeColor = 17 End If End With Next myCell End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with calculation
Sorry, missing the ending parentheses:
myKeyValue = Range("A" & myCell.Row) * Range("F" & (27 + myCell.Column)) -- Vasant "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Try: myKeyValue = Range("A" & myCell.Row) * Range("F" & (27 + myCell.Column) Of course, subsequent insertions of rows/columns may break this. -- Vasant "Mike." wrote in message ... Yes, I did have a typo in the description ... actually, D26 would be compared to would be compared to A26 * F31 ... Sorry about the typo. Would you know how to write the formula? Thanks again, Mike. -----Original Message----- Do you have a typo in your description? The pattern doesn't make sense. B26 is not even in your original range. -- Vasant "Mike" wrote in message ... Hi. I need help with a calculation in the macro below. I have an array of numbers in cells c26:f29. I need to compare each value with the product of numbers contained in column "A" and row "30". For instance, C26 would be compared to A26 * F30. C27 would be compared to A27 * F30. B26 would be compared to A26 * F31 . and so on. The calculation I need help with is in the variable "myKeyValue" below. Can anyone help? Thanks, Mike. -------------------------- Sub CreateOval2() Dim myRange As Range Dim myCell As Range Dim myKeyValue As Variant Dim myOval As Shape Set myRange = Sheet1.Range("c26:f29") For Each myCell In myRange.Cells With myCell myKeyValue = ??? HELP ??? If .Value < myKeyValue Then Set myOval = .Parent.Shapes.AddShape (Type:=msoShapeOval, _ Top:=.Top, Left:=.Left, Width:=.Width, Height:=.Height) myOval.Fill.Visible = msoFalse myOval.Line.Weight = 1.5 myOval.Line.ForeColor.SchemeColor = 17 End If End With Next myCell End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation using IF | Excel Worksheet Functions | |||
Multi threaded calculation (multi CPU) - impact on calculation spe | Excel Discussion (Misc queries) | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
How do I use a rounded calculation result in another calculation? | Excel Worksheet Functions | |||
range.calculation with UDF not working when calculation is set to automatic | Excel Programming |