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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.