Posts by chelly

Following worked for anyone else that may need.

Formula broken down

=Index('The column you are looking to return',MATCH('The first cell to match'&'The second Cell to match','Where to find the first cell column data range'&'where to find the second cell column data range',0 for exact match))

=INDEX('Asset Data'!D:D,MATCH('Distribution Log'!$AP3&'Distribution Log'!$P3,'Asset Data'!$G:$G&'Asset Data'!$A:$A,0))

So I would like to Populate Columns on the Distribution Log Tab (From the Asset Data Tab)

C (from C), D (From D), E (From F) L (from I) and P (From H)

However, In the asset data tab where we need to identify the correct entries to pull the date from using both the ISIN (A) and the Register holder (G) as there are multiple holders for the same isin.

So on the distribution tab ISIN (N) and Unitholder (G) need to match the ISIN (A) and Registered holder (G) on the asset data tab and pull through the corresponding information.

I have tried an Index and match but it never works for me. Any help would be really appreciated!!

Quote from S O

Hi Justin,

The "Unresolved Threads" option is showing threads that do not have a "resolved" status as you probably suspected.

The ability to mark a thread as "resolved" is something that we've only had for the last year or so. Historically this didn't exist on OzGrid and so for that reason it's never been a requirement on here. If people do decide to do it then it's really just a bonus.

All the best,

Sam

Display More

Hi Sam, Sorry I was told previously in the thread we cannot make them as resolved?

Quote from royUK

If you have Office 365 and share the workbook there(which is the best way) then you can use AutoSave

I have received the below error unable to share the work book. There is only one user ID that will be using the spreadsheet.

The attached code has been working fine.

I made some amendments to add additional code which caused errors so then deleted a when back to the original code but its not adding the date and time as it should. There are no errors its just as if its not there?

The sheet was protected so thought this could cause the issue? so have unportected the sheet and its still not doing anything.

The sheet is saved as Macro enabled any ideas??

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 4 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 5).Value = Date + Time
 Else
 Cells(Target.Row, 5).ClearContents
 End If
 ElseIf Target.Column = 1 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 2).Value = Date + Time
 Else
 Cells(Target.Row, 2).ClearContents
 End If
 End If
 Application.EnableEvents = True
End Sub

Quote from Mumps

What do you want to trigger the macro to save the workbook?

Below is the table C to G is updated for a new entry of issing a key. A & B is updated when a key is returned.

I need this to save either automatically when updated or on a regular basis

I have tried the code above but it is causing errors and stopped my previous code from working?

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Column = 1 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 2).Value = Date + Time
 Else
 Cells(Target.Row, 2).ClearContents
 End If
 ElseIf Target.Column = 4 Then
 Application.EnableEvents = False
 If Not IsEmpty(Target) Then
 Cells(Target.Row, 5).Value = Date + Time
 Else
 Cells(Target.Row, 5).ClearContents
 End If
 End If
 Application.EnableEvents = True
End Sub

Option Explicit
Dim Counter As Long
Const MAXCHANGES As Long = 10
Private Sub Worksheet_Change(ByVal Target As Range)
Counter = Counter + 1
If Counter > MAXCHANGES Then
 Application.StatusBar = "Saving..."
 Me.Parent.Save
 Counter = 0
 Application.StatusBar = "Done!"
End If
End Sub

Quote from ArdusPetus

Paste this in your worksheet code,

then save the workbook as xlsm.

Code

Option Explicit


Dim Counter As Long
Const MAXCHANGES As Long = 10


Private Sub Worksheet_Change(ByVal Target As Range)
Counter = Counter + 1
If Counter > MAXCHANGES Then
    Application.StatusBar = "Saving..."
    Me.Parent.Save
    Counter = 0
    Application.StatusBar = "Done!"
End If
End Sub

Display More

Hi,

I have created a couple of threads that have been answered and was wondering how to or if I needed to mark them as resolved?

Thanks,

Michelle

HI,

I have a workbook that is in use 24/7 and as much as I drum it into people they do not save the spreadsheet on a regular basis. Every time an entry is input they should save the spreadsheet.

Ive had a look around and im struggling to come up with some VBA code to action this automatically.

Can anyone help?

Yes, it works! Thank you so much !!

I have a spreadsheet I require the VBA to automaticall add the date and Time when a cell is updated and if info deleted remove the stamp.

Using the code below This is working for the first run. So when info is added/removed to column B the date and time is entered/removed in column C

However I need this to run for info added/removed to column G to put the date and time in column F.

Not sure where im going wrong....Any Ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 3).Value = Date + Time
Else
Cells(Target.Row, 3).ClearContents
End If
Application.EnableEvents = True
If Target.Column <> 6 Then Exit Sub
Application.EnableEvents = False
If Not IsEmpty(Target) Then
Cells(Target.Row, 7).Value = Date + Time
Else
Cells(Target.Row, 7).ClearContents
End If
Application.EnableEvents = True
End Sub

I have changed the spreadsheet so I require this when one specific cell is updated and will use conditional formating to highlight any issues.

However I need the date and time added for Two different enties.

using the code below Ive added the code twice using the different columns. It works for the first one but not the second?

Not sure where im going wrong....

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub

Application.EnableEvents = False
If Not IsEmpty(Target) Then

Cells(Target.Row, 3).Value = Date + Time
Else
Cells(Target.Row, 3).ClearContents
End If

Application.EnableEvents = True



If Target.Column <> 6 Then Exit Sub

Application.EnableEvents = False
If Not IsEmpty(Target) Then

Cells(Target.Row, 7).Value = Date + Time
Else
Cells(Target.Row, 7).ClearContents
End If

Application.EnableEvents = True
End Sub

Hi Thank you for your help.

It adds/updates the date if any of the cells are updated rather than only adding the date if all the cells in the range 1-4 are updated.

I think this is the restriction to the change VBA? It seem to be one or another opposed to a group of cells.

Hi Im trying to add code so when a user enters data into Cells A, B, C and D the Date and time is populated in Cell E.

I have a code which actions this request when either of these 4 cells have been changed singularly but was wondering if this could be done if only all 4 Cells have data entered into them and then if deleted from them all the stamp is also deleted.

If anyone able to help?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then

Application.EnableEvents = False

Cells(Target.Row, 5).Value = Date + Time

Application.EnableEvents = True

End If

End Sub

Thanks in advance