VBA Question

  • Thread starter Thread starter PeCoNe
  • Start date Start date
P

PeCoNe

Hallo,

I use the following code:

' Check AH position
If SumAH > 0 And (IsEmpty(Range("AA10")) Or Factor * SumAH >
Range("AA10")) Then
Range("AA10") = Factor * SumAH
End If
If Range("AA10") > 0 And SumAH < Range("AA10") Then
Beep
End If
' Check KPN position
If SumKPN > 0 And (IsEmpty(Range("AA13")) Or Factor * SumAH >
Range("AA13")) Then
Range("AA13") = Factor * SumKPN
End If
If Range("AA13") > 0 And SumKPN < Range("AA13") Then
Beep
End If
' Check PNL position
If SumPNL > 0 And (IsEmpty(Range("AA14")) Or SumPNL >
Range("AA14")) Then
Range("AA14") = SumPNL
End If
If Range("AA14") > 0 And Range("B14") < Range("AA14") Then
Beep
End If

Now i use 3 symbols.
It can be max 25 symbols and change if i sell or buy a symbol
If i insert or delete a symbol all the addresses behind that must be
changed by hand.
How can i prevent that or is there a better method?

Thanks Peter Maljers
 
Hello,

I would use tables and indices

for i=1 to 25

' Check position in line i
If Sump(i) > 0 And (IsEmpty(Range("AA" & i)) Or _
Factor * Sump(i) > Range("AA" & i)) Then
Range("AA" & i) = Factor * Sump(i)
End If
If Range("AA" & i) > 0 And Sump(i) < Range("AA" & i) Then
Beep
End If
next i
 
Op 2013-05-24 22:05, h2so4 schreef:
Hello,

I would use tables and indices

for i=1 to 25

' Check position in line i
If Sump(i) > 0 And (IsEmpty(Range("AA" & i)) Or _
Factor * Sump(i) > Range("AA" & i)) Then
Range("AA" & i) = Factor * Sump(i)
End If
If Range("AA" & i) > 0 And Sump(i) < Range("AA" & i) Then
Beep
End If
next i
Click to expand...
Thanks for this code.
The situation is a more complex i think.
The rows to take action are not the same distance see below.
I need to take action on the first new symbol so in my case now 6 times.
The sum also is per symbol.

AGN
AGN
AH
AH
AH
KPN
KPN
KPN
KPN
PNL
PNL
RD
RD
RD
RD
SBM
SBM
SBM


Bye Peter
 
Op 2013-05-24 22:05, h2so4 schreef:
Hello,

I would use tables and indices

for i=1 to 25

' Check position in line i
If Sump(i) > 0 And (IsEmpty(Range("AA" & i)) Or _
Factor * Sump(i) > Range("AA" & i)) Then
Range("AA" & i) = Factor * Sump(i)
End If
If Range("AA" & i) > 0 And Sump(i) < Range("AA" & i) Then
Beep
End If
next i


Hello,

I would use tables and indices

for i=1 to 25

' Check position in line i
If Sump(i) > 0 And (IsEmpty(Range("AA" & i)) Or _
Factor * Sump(i) > Range("AA" & i)) Then
Range("AA" & i) = Factor * Sump(i)
End If
If Range("AA" & i) > 0 And Sump(i) < Range("AA" & i) Then
Beep
End If
next i
Click to expand...
Thanks for this code.
The situation is a more complex i think.
The rows to take action are not the same distance see below.
I need to take action on the first new symbol so in my case now 6 times.
The sum also is per symbol.

AGN
AGN
AH
AH
AH
KPN
KPN
KPN
KPN
PNL
PNL
RD
RD
RD
RD
SBM
SBM
SBM


Bye Peter
 
Thanks for this code.
The situation is a more complex i think.
The rows to take action are not the same distance see below.
I need to take action on the first new symbol so in my case now 6 times.
The sum also is per symbol.
Click to expand...
Bye Peter
Click to expand...
Hi Peter,

1) the sum is the sum of a data in which column ?

2) a remark, I guess there is a mistake in your code for KPN, I suppose
the reference to sumAH should be sumKPN.

3) the code could be the following

Sub alert()
Dim i As Integer ' current row
Dim cs As String ' stock name on current row
Dim ps As String ' stock name we are busy with
Dim sump As Double ' sum of ???
Dim frs As Integer ' address of first row with stock name

i = 1
With Worksheets("sheet1")
cs = .Cells(i, 1)

' loop thru all rows
While cs <> ""

sump = 0
frs = i
ps = cs
While ps = cs
' let's sum the ??? for all the rows with the same stock name
' I made the guess that the sum is a sum of a product
' (number of stock * a price) with number in col B and price in col C
' change if necessary
sump = sump + .Range("B" & i) * .Range("C" & i)
i = i + 1
cs = .Cells(i, 1)
Wend
' trigger the alert based on your algorithm
' factor needs to de defined

If sump > 0 And (IsEmpty(Range("AA" & frs)) Or _
Factor * sump > Range("AA" & frs)) Then
Range("AA" & frs) = Factor * sump
End If
If Range("AA" & frs) > 0 And sump < Range("AA" & frs) Then
Beep
End If
Wend
End With
End Sub
 
Op 2013-05-25 17:39, h2so4 schreef:
Hi Peter,

1) the sum is the sum of a data in which column ?
Click to expand...
Factor = Range("AA6")
SumAGN = Round(WorksheetFunction.SumIf(Range("F7:F22"), "AGN",
Range("B7:B22")), 2)
SumAH = Round(WorksheetFunction.SumIf(Range("F7:F22"), "AH",
Range("B7:B22")), 2)
SumKPN = Round(WorksheetFunction.SumIf(Range("F7:F22"), "KPN",
Range("B7:B22")), 2)
SumPHNL = Round(Factor * WorksheetFunction.SumIf(Range("F7:F22"), "PNL",
Range("B7:B22")), 2)
SumRD = Round(Factor * WorksheetFunction.SumIf(Range("F7:F22"), "RD",
Range("B7:B22")), 2)
SumSBM = Round(Factor * WorksheetFunction.SumIf(Range("F7:F22"), "SBM",
Range("B7:B22")), 2)

2) a remark, I guess there is a mistake in your code for KPN, I suppose
the reference to sumAH should be sumKPN.
Click to expand...
Thanks i missed that, is corrected.
3) the code could be the following

Sub alert()
Dim i As Integer ' current row
Dim cs As String ' stock name on current row
Dim ps As String ' stock name we are busy with
Dim sump As Double ' sum of ???
Dim frs As Integer ' address of first row with stock name

i = 1
With Worksheets("sheet1")
cs = .Cells(i, 1)

' loop thru all rows
While cs <> ""

sump = 0
frs = i
ps = cs
While ps = cs
' let's sum the ??? for all the rows with the same stock name
' I made the guess that the sum is a sum of a product
' (number of stock * a price) with number in col B and price in col C
' change if necessary
sump = sump + .Range("B" & i) * .Range("C" & i)
i = i + 1
cs = .Cells(i, 1)
Wend
' trigger the alert based on your algorithm
' factor needs to de defined

If sump > 0 And (IsEmpty(Range("AA" & frs)) Or _
Factor * sump > Range("AA" & frs)) Then
Range("AA" & frs) = Factor * sump
End If
If Range("AA" & frs) > 0 And sump < Range("AA" & frs) Then
Beep
End If
Wend
End With
End Sub
Click to expand...
i must study on the rest of the code to understand what happened.
I can try it on monday when the exchange is open.
Again thanks.
Bye Peter
 
PeCoNe submitted this idea :
Op 2013-05-25 17:39, h2so4 schreef:
Click to expand...
hierbij aangepaste code.

Sub alert()
Dim i As Integer ' current row
Dim cs As String ' stock name on current row
Dim ps As String ' stock name we are busy with
Dim sump As Double ' sum of col B for one code
Dim frs As Integer ' address of first row with stock name
dim factor as double
Factor = Range("AA6")
' this macro assumes that all lines with the same code do follow each
' other

i = 7 : 'first line with stock info

With Worksheets("sheet1")
cs = .range("F" & i) : 'stock code is i colum F

' loop thru all rows
While cs <> ""

sump = 0
frs = i
ps = cs
While ps = cs
' lets compute the sum of all the rows with the same code
sump = sump + .Range("B" & i)
i = i + 1
cs = .range("F" & i)
Wend
' sump contains the sum of value of col B for a specific code
sump=round(sump,2)
' trigger the alert based on your algorithm
' factor needs to de defined

If sump > 0 And (IsEmpty(Range("AA" & frs)) Or _
Factor * sump > Range("AA" & frs)) Then
Range("AA" & frs) = Factor * sump
End If
If Range("AA" & frs) > 0 And sump < Range("AA" & frs) Then
Beep
End If
Wend
End With
End Sub
 
Op 2013-05-25 20:18, h2so4 schreef:
PeCoNe submitted this idea :
hierbij aangepaste code.

Sub alert()
Dim i As Integer ' current row
Dim cs As String ' stock name on current row
Dim ps As String ' stock name we are busy with
Dim sump As Double ' sum of col B for one code
Dim frs As Integer ' address of first row with stock name
dim factor as double
Factor = Range("AA6")
' this macro assumes that all lines with the same code do follow each
' other

i = 7 : 'first line with stock info

With Worksheets("sheet1")
cs = .range("F" & i) : 'stock code is i colum F

' loop thru all rows
While cs <> ""

sump = 0
frs = i
ps = cs
While ps = cs
' lets compute the sum of all the rows with the same code
sump = sump + .Range("B" & i)
i = i + 1
cs = .range("F" & i)
Wend
' sump contains the sum of value of col B for a specific code
sump=round(sump,2)
' trigger the alert based on your algorithm
' factor needs to de defined

If sump > 0 And (IsEmpty(Range("AA" & frs)) Or _
Factor * sump > Range("AA" & frs)) Then
Range("AA" & frs) = Factor * sump
End If
If Range("AA" & frs) > 0 And sump < Range("AA" & frs) Then
Beep
End If
Wend
End With
End Sub
Click to expand...
Thanks it works fine now.
Bye Peter Maljers
 
Op 2013-05-29 11:29, PeCoNe schreef:
Op 2013-05-25 20:18, h2so4 schreef:
Thanks it works fine now.
Bye Peter Maljers
Click to expand...
I want if there is a beep that the background red colors and if there is
no beep it must stay white.
How can i do that?
 
After serious thinking PeCoNe wrote :
range("AA" & frs).interior.color=255
else
range("AA" & frs).interior.pattern=xlnone
add the following lines to your code, see above
 
Back
Top