Log In
Register
Home
January 28, 2020
Offline
Hi all,
Im having this issue on Excel:
- I have data that auto updates, so i need to change columns such that when a certain data passes, the cells in the column cannot change "are fixed and changed to values" instead of formulas.
is there a way i can do this?
October 5, 2010
Offline
Hi,
Please supply your workbook and data and a clearer explanation of what it is exactly you want to happen.
Please supply as much information as possible e.g. :
1. How does your data auto-update?
2. How do you want to 'chnage columns'?
3. What 'certain data passes'?
4. etc
Regards
Phil
January 28, 2020
Offline
Hi,
I cannot attach the full spreadsheet but have attached a snippet.
So i have the pivot table on the left where the "completed" column auto updates through a SQL macro everyday from an external source, that dosent really matter.
For example, i have a formula "I11-C11" to get a value for completed in that certain date.
But what i want is that when that certain date passes, that column "I" automatically change from the formula to a fixed value that wont change.
Trusted Members
December 20, 2019
Offline
Its still not particularly clear as the we cant see the actual workbook, but something like this might work.
You need to create this sub in the Thisworkbook module in the VBA editor. Then when you open the work book it will check if the date in I2 is less than today and if so copy the data to text in the whole of column I
There are a couple of issues you might need to adjust for
- Date formats can be a little painful in VBA, i am in the UK so we use DDMMYYYY which is what i have set mine for.
- I dont know where the date is in your worksheet or if it will change so you might need to adjust I2
- I guess that column I will need to change to J the next day/week/month?
It is possible to get around the above, but i would need to see the workbook to be of more help.
Private Sub Workbook_Open()
Dim R As Range
Set R = Range("i:i")
If Format(Now, "DD/MM/YYYY") > Format(Range("i1"), "DD/MM/YYYY") Then
R = R.Value
End If
End Sub
New Member
January 28, 2021
Offline
Thanks Purfleet,
This formula works great. New to macros here, is there a way to loop this instead of copying this for each column you want to apply this to?
For example, so I wouldn't have to do something like this for each column:
Private Sub Workbook_Open()
Dim R As Range
Set R = Range("i:i")
If Format(Now, "DD/MM/YYYY") > Format(Range("i1"), "DD/MM/YYYY") Then
R = R.Value
End If
Set R = Range("j:j")
If Format(Now, "DD/MM/YYYY") > Format(Range("j1"), "DD/MM/YYYY") Then
R = R.Value
End If
End Sub
Many thanks
UPDATE, used the following formula:
Dim cell As Range
On Error GoTo ErrorHandler
For Each cell In Range("i1:u1")
If cell.Value < Range("i11") Then
cell.Offset(4, 0).Value = cell.Offset(4, 0).Value
End If
Next cell
Exit Sub
ErrorHandler:
Exit Sub
End Sub
1 Guest(s)
