Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice!tha
Hi there,
You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice!tha
you can do this with conditional formatting
"Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
thing is i want to be able to do it using vba...so it's cleaner.
"Patrick Molloy" wrote: you can do this with conditional formatting "Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice!tha
Is this homework?
"Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
ha ha ha...no. I am 25! although if i was 18 and at uni, you're right...this
could be a useful site. "JLGWhiz" wrote: Is this homework? "Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
On 22 Iul, 19:47, SAM wrote:
Step 1: set-up 1: If the value ...... For your Step 1 , work with this , and make ajustments according with your needs to workbook and worksheet name .., the lenght of your range where you have Last Price .... Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("TRY.xls").Worksheets("1") With FromWbook Set myRng1 = .Range("B2:B20") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Hope to help |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
On 22 Iul, 20:26, ytayta555 wrote:
Something in your Step 1 is not logical , if the value is < or = , or , or = , in the case the value is equal , you always shall have the color red or green ( not shure ) . Maybe you must put one more condition , such as , if value is equal to the value at time minus 4 cells earlier , then , value to be "x" and color ... how you want . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
my actual data is in cell e11:e71..if the next value on is a continuation of
the series (either +1, or -1) i want it to know to label the 0 as a continuation of the series...? do you have an email? "ytayta555" wrote: On 22 Iul, 20:26, ytayta555 wrote: Something in your Step 1 is not logical , if the value is < or = , or , or = , in the case the value is equal , you always shall have the color red or green ( not shure ) . Maybe you must put one more condition , such as , if value is equal to the value at time minus 4 cells earlier , then , value to be "x" and color ... how you want . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
Is better to explain here in the newsgroup what you need , I'm a beginner too . I use 2003 version , can look only to files saved in xls. format , my email is . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
this should get you started anyway:
Option Explicit Sub update_C() Dim target As Range Dim runtime As Double Dim targettime As Double 'runtime = Now - TimeValue("00:04:00") runtime = DateValue("22/07/2009") + TimeValue("08:51:00") Set target = Range("C2") Do Until target = "" targettime = DateValue(target.Value) + TimeValue(target.Value) If targettime = runtime Then 'green target.Offset(, 2) = 1 Else ' red target.Offset(, 2) = -1 End If Set target = target.Offset(1) Loop End Sub TODO: aggregate the values and add the formatting "Sam" wrote in message ... thing is i want to be able to do it using vba...so it's cleaner. "Patrick Molloy" wrote: you can do this with conditional formatting "Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
See if you can work with this:
Sub sist() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Not sure if I have the comparisons to Now in the right order, but you can change those. "SAM" wrote in message ... my actual data is in cell e11:e71..if the next value on is a continuation of the series (either +1, or -1) i want it to know to label the 0 as a continuation of the series...? do you have an email? "ytayta555" wrote: On 22 Iul, 20:26, ytayta555 wrote: Something in your Step 1 is not logical , if the value is < or = , or , or = , in the case the value is equal , you always shall have the color red or green ( not shure ) . Maybe you must put one more condition , such as , if value is equal to the value at time minus 4 cells earlier , then , value to be "x" and color ... how you want . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
Hi Patrick - i took a look at the code....
but the thing is the time element changes....so it has to look down the range all the time.... "Patrick Molloy" wrote: this should get you started anyway: Option Explicit Sub update_C() Dim target As Range Dim runtime As Double Dim targettime As Double 'runtime = Now - TimeValue("00:04:00") runtime = DateValue("22/07/2009") + TimeValue("08:51:00") Set target = Range("C2") Do Until target = "" targettime = DateValue(target.Value) + TimeValue(target.Value) If targettime = runtime Then 'green target.Offset(, 2) = 1 Else ' red target.Offset(, 2) = -1 End If Set target = target.Offset(1) Loop End Sub TODO: aggregate the values and add the formatting "Sam" wrote in message ... thing is i want to be able to do it using vba...so it's cleaner. "Patrick Molloy" wrote: you can do this with conditional formatting "Sam" wrote in message ... Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. colour: green set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. colour: red Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
I have attached this code to the bottom of the former one so the while thing
looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
The value of x int the code I suggested does accumulate so long as the
condition is true and the value of x is posted in column G on each iteration. If the condition is false or rather if the ElseIf Condition is true, then the value of x becomes zero and that is posted to column G. I just eliminated posting a value of 1 in a column each time as you had illustrated. Maybe I just did not fully understand what you are doing. "Sam" wrote in message ... I have attached this code to the bottom of the former one so the while thing looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
I missed the Next part of the statement. Here is the revised code:
Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If Next End Sub Now it should accumulate all of the differences. "Sam" wrote in message ... I have attached this code to the bottom of the former one so the while thing looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
can i send you an example of the sheet i am making....to your email?
"JLGWhiz" wrote: I missed the Next part of the statement. Here is the revised code: Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If Next End Sub Now it should accumulate all of the differences. "Sam" wrote in message ... I have attached this code to the bottom of the former one so the while thing looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
No, I only try to assist through the news group. But if you can give clear
explanation of what the code does or does not do and what you need it to do, I will do my best to help you. "Sam" wrote in message ... can i send you an example of the sheet i am making....to your email? "JLGWhiz" wrote: I missed the Next part of the statement. Here is the revised code: Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If Next End Sub Now it should accumulate all of the differences. "Sam" wrote in message ... I have attached this code to the bottom of the former one so the while thing looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
i've put a fresh post up......i think it's much clearer and explains the
problems we're having.... "JLGWhiz" wrote: No, I only try to assist through the news group. But if you can give clear explanation of what the code does or does not do and what you need it to do, I will do my best to help you. "Sam" wrote in message ... can i send you an example of the sheet i am making....to your email? "JLGWhiz" wrote: I missed the Next part of the statement. Here is the revised code: Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If Next End Sub Now it should accumulate all of the differences. "Sam" wrote in message ... I have attached this code to the bottom of the former one so the while thing looks like: Sub GIVEATRY() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("1.xls").Worksheets("Sheet1") With FromWbook Set myRng1 = .Range("E11:E71") End With For Each myCell In myRng1.Cells If myCell.Value = myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = 1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With ElseIf myCell.Value <= myCell.Offset(4, 0) Then myCell.Offset(0, 1).Value = -1 myCell.Offset(0, 1).Select With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With Else End If Next myCell End Sub Sub SIST() Dim rng As Range, x As Long, c As Range Set rng = ActiveSheet.Range("E11:E71") For Each c In rng If c.Value <= Now Then x = x + 1 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 10 ElseIf c.Value Now Then x = 0 c.Offset(0, 2) = x c.Offset(0, 2).Interior.ColorIndex = 3 End If End Sub Problem is this is not really doing what i need. Firstly i need the function to sum up the points as it moves down the count (see descrition below) on the methodology. Right now it is only doing step 1....i need to get it to count the number cumulatively (as i describe in step 2) --------------------------- Hi there, You'll have to forgive me because i am a bit of a novice wiith respect to vba but i am trying to learn it...rather painfully. So here's the problem: I have two columns of data: example below: Period Last Price "Column C" "Column D" 22/07/2009 08:54 1.63360 22/07/2009 08:53 1.63370 22/07/2009 08:52 1.63330 22/07/2009 08:51 1.63310 22/07/2009 08:50 1.63300 22/07/2009 08:49 1.63310 22/07/2009 08:48 1.63300 22/07/2009 08:47 1.63290 22/07/2009 08:46 1.63250 22/07/2009 08:45 1.63290 22/07/2009 08:44 1.63230 22/07/2009 08:43 1.63200 In "Column C" i want to print the following rules: Step 1: set-up 1: If the value at time now (in this case: 08:54, value 1.63360) is greater than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = 1. font colour: green background nothing set-up 2: If the value at time now (in this case: 08:54, value 1.63360) is less than or equal to the value at time minus 4 cells earlier (in this case 08:51, value 1.63360) assign value = -1. font colour: red background: nothing Step 2: So we are currently calulating the relative +1's and -1's. I need to cumulatively sum each of these according to the example below: If i have 1,1,1,1,1 the formula needs to sum them as it "counts" (showing 1, 2, 3, 4, 5, respectively). If it is ever interupted (e.g. 1,1,1,1,-1) it just starts the cumulative count again at zero (showing 1,2,3,4,0). So: count would look like: 1 1 1 2 1 3 1 4 1 5 1 6 -1 0 1 1 1 2 when the count get's to +9 the cell should go red and the font black. when the count gets to -9 the cell should go green and the font black. finshed. So what i want is for "Column C" to look like: Period Last Price Column C 22/07/2009 08:54 1.63360 1 (with red font) 22/07/2009 08:53 1.63370 2 (with red font) 22/07/2009 08:52 1.63330 3 (with red font) 22/07/2009 08:51 1.63310 4 (with red font) 22/07/2009 08:50 1.63300 5 (with red font) 22/07/2009 08:49 1.63310 6 (with red font) 22/07/2009 08:48 1.63300 7 (with red font) 22/07/2009 08:47 1.63290 8 (with red font) 22/07/2009 08:46 1.63250 9 (with bold black font, red square) 22/07/2009 08:45 1.63290 1 (with red font) 22/07/2009 08:44 1.63230 -1 (with green font) 22/07/2009 08:43 1.63200 -2 (with green font) basically i am totally stuck....i have tried writing some loops but mine totally fail all the time....it's so so annoying. Sorry to ask for so much help but i have been painfully staring at vba for dummies for the last week and am stuck.... Thank you to anyone who can help me on this... |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
For your Step 2 , as I see , ker_01 brought the solution ,
the second very clever formula , =IF(B2*C10,B2+C1,B2) , : http://groups.google.ro/group/micros...b0c6bddb4c1d8# and , next code , maybe cover really all your needs : Sub GIVEATRYFORSTEPTWO() Dim FromWbook As Worksheet Dim myCell As Range Dim myRng1 As Range Set FromWbook = Workbooks("YYYYY.xls").Worksheets("YYYYY") With FromWbook Set myRng1 = .Range("G11:G71") End With For Each myCell In myRng1.Cells If myCell.Value < 0 Then myCell.Font.ColorIndex = 4 ElseIf myCell.Value 0 Then myCell.Font.ColorIndex = 3 Else End If If myCell.Value = 9 Then myCell.Select Selection.Font.Bold = True Selection.Font.ColorIndex = 1 With Selection.Interior .ColorIndex = 3 .Pattern = xlSolid End With ElseIf myCell.Value = -9 Then myCell.Select Selection.Font.Bold = True Selection.Font.ColorIndex = 1 With Selection.Interior .ColorIndex = 4 .Pattern = xlSolid End With Else End If Next myCell End Sub |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Totally stuck trying to format,loop etc...please help a novice
To make it totaly in VBA , without worksheet
formula , we must be waiting for a Guru ..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming | |||
Totally Stuck...Help Please! | Excel Programming |