Hi,
Originally Posted by
Atlantis764
..The top row will always be the same and in the same position in Database1 and Database2 and all the columns will be identical.
This means that we don’t need two very similar main sections ( I mean we don’t need two lots of Rem 1 and Rem 2 ) since they are doing the same thing. So it matters not what worksheet we do the matching to, Datebase1 or daterbase2, the results of the found column and row would always be the same. But that is not so bad in this case as the coding works very efficiently and large more laid out coding is often better to understand and modify and learn from – from your attempt its clear your picked up well how to do things.
But the point is that we could then use the main Rem 1 and Rem 2 section once as in the first shorter macro I did for you ( https://excelfox.com/forum/showthrea...ll=1#post19860 )
The modification needed would be 2 main changes to the first shorter macro
_ having a bit from the top of the last macro , ( https://excelfox.com/forum/showthrea...ll=1#post19868 ) , so that it runs when either column H or column I is changed,
_ then the output section Rem 3 would need to be tweaked a bit so that it outputted to the correct stuff to the correct worksheet
Here is a solution like that: ( https://excelfox.com/forum/showthrea...ll=1#post19870 )
Originally Posted by
Atlantis764
..Do you think it is possible to help me with this change in the VBA code so that when I delete an amount from one column or another (or the entire row in Database), that amount is also deleted from the related database (hours in Database1 and amount in Database 2)?
_ If you delete a single cells value in either column H or column I, then the existing macros will do what you want already.- That is because you have effectively changed the cells value from what it was to another value of an empty string. That new empty string value will be put in the cell, so to our eyes it will look empty. The end result is what you want.
(In computer VBA language an “empty cell” can be truly Empty or it can have a value that to us as Humans is the same – it can have a value of a zero length string.
So that zero length string value is put in the cell by the existing coding if you remove a value from a cell in either column H or column I, which to our human eyes looks like the cell is empty. Some smarter computer experts might explain that a bit better, but at the end of the day it all amounts to a similar final result as far as we are concerned)
Originally Posted by
Atlantis764
..Do you think it is possible to help me with this change in the VBA code so that when I delete ….. the entire row in Database), that amount is also deleted from the related database (hours in Database1 and amount in Database 2)?
Well, sort of…..
_ You can allow for pretty well any event to cause something to happen. It is just a case of how complicated the final macro will end up. Sometime it’s a better idea if you try to limit what can be done to what your really need. But that is all up to you and how important it is and how much time it saves you. Any code can go wrong or have unexpected bugs in the future, so its always best to think beforehand what you really need – the more longer and more complicated a coding is, the higher likelihood of course of something unexpected occurring later. But it’s down to you to decide on all that.
Nothing is difficult to do. But it is just a lot of simple and tedious coding and becomes inefficient the more you want to do. I am reluctant to go too far helping in this direction as the coding is not difficult, but just takes time. So I start going away from helping you and just start doing your work for you.
I give you one example:
Lets say you want the macro to remove from the related database (hours in Database1 and amount in Database 2) when you remove all values in columns A to I in worksheet Database
This gets very tedious and complicated as you are removing the information you need to figure out what row and column you want to access and remove data from in Database1 and database2 !!!!!!
To start with , towards the top of the Database worksheet code module you need variables to catch that required information before you remove it.
Code:
' These are sometimes called global variable
Dim strSrching As String
Dim DteVee2 As Long
Now we need to make use of another of the event macros that Microsoft already has there and lets us add to. It is the one that runs every time you select something.
So my idea is that, in that event macro, I put coding which will store that information when you select a row in columns A to I, in one go. ( Note I am meaning selecting a range 1 row and 9 columns. If you prefer it to be when you select the entire row, that is just as possible, but different extra coding once again )
Like this
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
If Target.Column = 1 And Target.Columns.Count = 9 And Target.Rows.Count = 1 Then
' fill the global variable in case the values are about to be removed
Let strSrching = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Let DteVee2 = Range("D" & TgRw & "").Value2
Else
End If
End Sub
Then we need this sort of thing to replace our existing coding.
First
_.. remove the worksheet variable assignments, and target row variable assignment further down in the existing coding because you are not allowed to assign variable more than once, and I need to do it in the new bit I will give you below
_.. change the check in the existing coding to exit on multiple cells count to maybe check for multiple row count instead like If Target.Rows.Count <> 1 Then Exit Sub
( Of course you could also arrange that you could delete values in multiple rows, but once again , more and more complicated coding to allow for all that… )
_.. possibly some other things I have not thought about at first thought. There might be some other variable assignments to remove the duplicate of, for example.
Then
This new coding (to replace the existing Worksheet_Change coding)
You will need to add to that a version of the existing coding, modified a bit as I partly explained
Code:
Private Sub worksheet_Change(ByVal Target As Range)
Dim WsD1 As Worksheet: Set WsD1 = ThisWorkbook.Worksheets("Database1")
Dim WsD2 As Worksheet: Set WsD2 = ThisWorkbook.Worksheets("Database2")
If Target.Column = 1 And Target.Columns.Count = 9 And Target.Rows.Count = 1 Then
Dim TgRw As Long: Let TgRw = Target.Row ' the changed target row
' check if all cells are empty
Dim Clm As Long, strTxt As String
For Clm = 1 To 9
Let strTxt = strTxt & Cells.Item(TgRw, Clm).Value
Next Clm
If strTxt = "" Then ' This means all cells are empty (or have a zero length strings in them)
' First we need to get the row and column infomation as before ( buit the info we need is not there anymore as we just removed it!! )
'Dim strSrching As String ' we need to have got this filled before we removed all the info
' Let strSrching = Range("E" & TgRw & "").Value & Range("F" & TgRw & "").Value & Range("G" & TgRw & "").Value
Dim arrDee1() As Variant: Let arrDee1() = WsD1.Evaluate("=A1:A25 & B1:B25 & C1:C25")
Dim MtchRow As Long
Let MtchRow = Application.Match(strSrching, arrDee1(), 0) ' this tries to match the correct row
Dim arrDts() As Variant: Let arrDts() = WsD1.Evaluate("=IF({1},A1:K1)")
'Dim DteVee2 As Long: Let DteVee2 = Range("D" & TgRw & "").Value2 ' we need to have got this filled before
Dim MtchColm As Long
Let MtchColm = Application.Match(DteVee2, arrDts(), 1) ' this tries to match the correct row in Database1 using a 1 as the third argument im match will get the neartest next date match
'now we can "empty" the old data
Let WsD1.Cells.Item(MtchRow, MtchColm).Value = ""
Let WsD2.Cells.Item(MtchRow, MtchColm).Value = ""
Else
End If
Else
'
'
'
'
' ' here goes all the rest, the existing coding, a bit modified as necessary
'
'
'
'
'
End If
End Sub
I think you can see it’s getting complicated and tedious, and takes a while to “develop” but not difficult.
The other thing to bear in mind is that Program “development” as we are doing is extremely inefficient. It is very good for learning purposes, ( and also very good if you are paid to be a program “developer” since you will have plenty of work to be paid for. )
If you want to do the job efficiently it is almost always better to think very hard of what you want to do and be like 99.99% sure of everything you want to do before you start.
Otherwise it takes a lot longer and also the final thing you come up with will be a mess of patched together bits and almost certainly not the best final solution
Alan
Reply With Quote
) in which I entered the data (the fields from the User Form were exactly the dates from the top row of the Database) for each expert at the end of each month.
Bookmarks