VBA Question

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
 
H

h2so4

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
 
P

PeCoNe

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
 
P

PeCoNe

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
 
H

h2so4

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
 
P

PeCoNe

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
 
H

h2so4

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
 
P

PeCoNe

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
 
P

PeCoNe

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?
 
H

h2so4

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Again VBA question 4
Can Text be Treated as Values for use in Calculations with Numbers? 3
VBA set thick borders around non empty cells in range 0
Reference Cell in VLOOKUP 5
Array macro question 4
coding question 1
Trouble flagging conditions in Excel using VB 6
Can Vba workout if a selection is part of a group? 0

Top