Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
AlinaMicu pe Simpatie.ro
 | Femeie
25 ani
Brasov
cauta Barbat
25 - 39 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
I need to check some names in 2 databases and modify a date for each match.
The big database (fise_aptitudine.xlsm file) contains a list of about 1000 names and the smaller database (unimportant file name) with the correct dates contains about 50 names.
I need to locate each name from the small one into the big one, and update the dates manually (if I could use formulas this would be much easier - I can't because the correct dates in the big db are found in 20 different files).
I can always use CTRL+F, copy/paste the name and find it, then update the date, but it's annoying.
I wanted something easier like a find formula or go to cell reference.
So, in the small database I added the following formula in a column to obtain the row index of the name in the big db:
=CELL("row";INDEX([FISE_APTITUDINE.xlsm]Sheet1!$G$4:$G$1067;MATCH(I4;[FISE_APTITUDINE.xlsm]Sheet1!$G$4:$G$1067;0);1))
and dragged it for all the 50 names, obtaining the rows where I can find the 50 names in the big database.
I can write the cell reference obtained for each name in the big db reference field - eg G408, but why not make it even easier?
So I made the following vba code to go to that row in column G in the big db, by simply selecting the cell with the row index in the small db and clicking a button in QAT or a custom made button in the sheet (insert rectangle - assign macro).
Sub GoToCellReference()
' IMPORTANT: in my case it's always column G (column 7 - C7)
' if you want to define that in a range - the next column
' then activate the next 2 lines and activate the other Application.Goto Reference line
' while deactivating the previously activated one (the one with C7)
'Dim y As Integer
' y = ActiveCell.Offset(0, 1).Value 'the first cell to the right, same row
Dim x As Integer
x = Selection.Value 'or ActiveCell.Value
' both files are open, but I select a cell in 1 file and when clicking the button in QAT
' it activates the file mentioned below and goes to the specified cell reference
Windows("fise_aptitudine.xlsm").Activate
' Application.Goto Reference:="R" & x & "C" & y
Application.Goto Reference:="R" & x & "C7"
End Sub
|
Modificat de TRaP (acum 5 ani)
|
|
| pus acum 5 ani |
|