Hi bluecat
Put 1.1 in a cell.
Select that cell , right-click, then Copy
Select you 100 cells, rightclick, PasteSpecial
Check Multiply (in the middle section!)
OK
Cheers
Stephen
Hi bluecat
Put 1.1 in a cell.
Select that cell , right-click, then Copy
Select you 100 cells, rightclick, PasteSpecial
Check Multiply (in the middle section!)
OK
Cheers
Stephen
Hi Jeroen
When you say "the first available row", do you mean in the sheet named "Data" ?
Also, do you want the values to be copied, or the formulae (incl lookups, etc) as well?
KiwiSteve
Hi hesham
Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error Resume Next
For Each aCell In Sheet3.Range("E32:E1800")
If aCell.Interior.ColorIndex <> -4142 And aCell.Interior.ColorIndex <> 3 Then
aCell.Interior.ColorIndex = -4142
End If
If aCell.Value <= 2 And aCell.Value <> "" And aCell.Interior.ColorIndex <> 3 Then
aCell.Interior.ColorIndex = 3
MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
MsgBox aCell.Address
End If
Next aCell
End Sub
The first loop changes all cell colours which are not red (ColorIndex =3) to 'No Fill'.
The second loop checks if each aCell is <=2 AND IS NOT BLANK and is not already coloured red. I think that was what your code didn't check.
You can alter these conditions to suit your needs.
Note: You'll see more than one condition is checked by the If. . . And . . . steps
Note: you could also do this using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E32:E1800")) Is Nothing Then
If Target.Interior.ColorIndex <> -4142 And _
Target.Value <= 2 And Target.Value <> "" Then
Target.Interior.ColorIndex = 3
MsgBox """?C????I ??I ???C? C??I??? ???E ?E? ", vbOKOnly, " Warnning"
MsgBox Target.Address
End If
End If
End Sub
This has no code to reset all the cells ColorIndex = -4142. Give it a try.
Cheers
KiwiSteve
Hi Dave
Tried your code, with some mods, and it worked fine. I copied the data to a hidden column on the same sheet to save changing sheets, but otherwise the same.
Many thanks
Stephen B
Hello again
Here's the file
Stephen
Hi nickstoy
Our MailMarshall didn't like me attaching an .xls or a .zip file, so if you post your own email address I'll send it through.
KiwiSteve
Hi there
Attached is a file that predicts for you.
The data oscillates and so a straight linear pridiction is not useful. So I separated each month out for the nine years, and then used a linear and a quadratic LINEST model to predict for each month. You could use others.
The a & b for the linear model are the coefficients of y=ax+b
The a, b & c for the quadratic model are the coefficients for y=ax^2+bx+c
The R^2 is the co-relation, ie how well each equation fits the data. 0.8-0.9 is considered ok, so most of the predictions are ok-ish!
The examples on the right show the LINEST equations that are used. They are array formulae.
The chart has a trendline displayed, with the equation and R^2 value. If you select the chart, you'll see the high-lighted range of data. As you move this to different months, you will (hopefully!) see the values as dispalyed in the table.
Finally, enter a value into the blue cells for the year period ahead, and you will see a prediction for each month in the green cells.
Have fun
KiwiSteve
Hi Ann
Have had a look at Andy's stock chart approach, and it seems to do the job well.
You could also play around with the box and whisker option within stock charts.
Cheers
KiwiSteve
Thanks Dave, much appreciated.
I'll give it a trial and report back. I'll also try to work through the logic.
Cheers
Stephen
Hi Ann
I see one problem - 160/120 has the same decimal value as 120/90, but you wouldn't want those to be shown as the same.
Maybe Chip Pearson has an answer?
He's a charting whiz: http://www.cpearson.com
Cheers
KiwiSteve
Hi all
I'm familiar with VBA, but have never used arrays. From what I've
picked up, I would need an array for this task:
I have up to 1500 data values entered in the range $A$6:$E$305.
They are recordings and are entered in the order they are taken.
How do I read all of these values into an array, sort them into
order, then put them back into the same range so the first 300
values are in column A, the next 300 are in column B, and so on?
Also, would it matter if there was, say, only 1178 entries?
Thank you for any help you can provide.
KiwiSteve
You're welcome.
Hi Sandy
The default 'value' for a combobox is text, not number, so if the linked cell is A1, then you need to make your formula something like this:
=if(VALUE(A1)>5, etc...).
Excel uses VALUE to interpret A1 as a number, if possible, even though it is text. It won't work on "one", however.
Alternatively, if the combobox is on the worksheet you could use a dropdown list from the Data>Validation>List menu. If the valid list contains numbers, then A1 will also be a number.
Cheers
KiwiSteve