PDA

View Full Version : Create a Loop Until using VBA



Scholar 1234
05-19-2014, 08:11 PM
Hi,
I want to write a code that would loop until a criteria is met. More specifically, here is an example:
The price of a share of CompanyA:CompanyZ is $A-$Z on any given date (Cells A2:Z2), which refreshes daily as does the date in Cell A1 (no help needed with the share price refreshing just with what follows next). Say today is 2014-05-10 (Cell A1) and I want the share price to keep updating in Cells A4:Z4 as well (eg Cell A2=A4), until the date 2014-06-01 is met (Cell A3). That is If the share price for company is $18 on 2014-05-10 and reaches $25 on 2014-06-01, cell A4 will equal $25 and remain at this value (end of loop)
Thanks,
Scholar 1234

Scholar 1234
05-19-2014, 08:35 PM
So basically I want cells A2:Z2 to equal A4:Z4 until cell A1=A3 after which cell A4:Z4 will equal the value of A2:Z4 at the point that A1=A3. Note that A1 and A2:Z2 will continue to change but I want A4:Z4 to stop updating as soon as A1=A3.

Bob Phillips
05-20-2014, 12:13 AM
That is very confused, I think you need to post a sample workbook with a before and after example.

Scholar 1234
05-20-2014, 08:23 AM
Okay I hope this helps:

Cell A1 contains say for example =today()
Cell A2 contains stock that refreshes on my command (no need to help with this)
Cell A3 is fixed and equals a predetermined date ( 2014-06-01)
Cell A4=A2
2014-05-10
$18
2014-06-01
$18

In this next instance notice that Cell A1 is 2014-06-01, which equals A3. At this instance I want A4 to no longer equal A2 (I want the loop to stop):
2014-06-01
$25
2014-06-01
$25

This final scenario shows that Cell A4 remains fixed once the condition A1=A3 is met:

2014-06-25
$28
2014-06-01
$25

Scholar 1234
05-20-2014, 05:50 PM
Is there something I'm missing? I really want to figure this out so please let me know if something is unclear

Kenneth Hobs
05-20-2014, 05:59 PM
Help us help you by post a workbook as requested.

Scholar 1234
05-21-2014, 07:27 AM
This is what I was looking for. Thanks anyway

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
On Error GoTo finish
If Target = Range("A2") Then
If Range("A2").Value < Range("D6").Value Then Range("D8:D77").Value = Range("B8:B77").Value
End If
finish:
Application.ScreenUpdating = True
End Sub

Paul_Hossler
05-21-2014, 07:48 AM
@Scholar1234 --

Glad you got what you were looking for (#7), but it appears to be nothing like the original question in your #1



I want to write a code that would loop until a criteria is met. More specifically, here is an example:
The price of a share of CompanyA:CompanyZ is $A-$Z on any given date (Cells A2:Z2), which refreshes daily as does the date in Cell A1 (no help needed with the share price refreshing just with what follows next). Say today is 2014-05-10 (Cell A1) and I want the share price to keep updating in Cells A4:Z4 as well (eg Cell A2=A4), until the date 2014-06-01 is met (Cell A3). That is If the share price for company is $18 on 2014-05-10 and reaches $25 on 2014-06-01, cell A4 will equal $25 and remain at this value (end of loop)

Scholar 1234
05-21-2014, 08:06 AM
Hi, yeah I did some formatting but the basic idea is still there. In my case cells A2:Z2=B8:B77, A1=A2, A3=D6, and A4:Z4=D8: D77.

Scholar 1234
05-21-2014, 01:05 PM
I have a follow up question, what can I do to subtract two ranges (in bold using previous code)?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
On Error GoTo finish
If Target = Range("A2") Then
If Range("A2").Value < Range("D6").Value Then Range("E8: E77").Value = Range("D8: D77").Value - Range("B8:B77").Value
End If
finish:
Application.ScreenUpdating = True
End Sub

Paul_Hossler
05-21-2014, 01:39 PM
Not tested, but I'd do something like this



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim iRow As Long

If Target.Address <> "$A$2" Then Exit Sub

If Target.Value >= Range("D6").Value Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False ' ***** you really need this

With Target.Parent
For iRow = 8 To 77
.Cells(iRow, 5).Value = .Cells(iRow, 4).Value - .Cells(iRow, 2).Value
Next iRow
End With

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub



To avoid firing the event handler each time any of the rows 8 - 77 change, you should disable .EnableEvents

Bob Phillips
05-22-2014, 06:37 AM
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
On Error GoTo finish
If Target = Range("A2") Then

If Range("A2").Value < Range("D6").Value Then

With Range("E8:E77")

.Formula = "D8-B8"
.Value = .Value
End With
End If
End If
finish:
Application.ScreenUpdating = True
End Sub

p45cal
05-22-2014, 07:55 AM
cross posting:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4a7a5047-ca63-4308-9509-4adeb4cb682b/create-a-loop-until-using-vba?forum=exceldev